Felhasználó által definiált függvények létrehozása (adatbázismotor)

2025-03-05

A következőkre vonatkozik:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Ez a cikk bemutatja, hogyan hozhat létre felhasználó által definiált függvényt (UDF) az SQL Serverben a Transact-SQL használatával.

Korlátozások és korlátozások

A felhasználó által definiált függvények nem használhatók az adatbázis állapotát módosító műveletek végrehajtására.
A felhasználó által definiált függvények nem tartalmazhatnak olyan OUTPUT INTO záradékot, amelynek célja egy tábla.
A felhasználó által definiált függvények nem adhatnak vissza több eredményhalmazt. Ha több eredményhalmazt szeretne visszaadni, használjon tárolt eljárást.
A hibakezelés felhasználó által definiált függvényekben korlátozott. Az UDF nem támogatja TRY...CATCH, @ERROR vagy RAISERROR.
A felhasználó által definiált függvények nem hívhatnak meg tárolt eljárásokat, de kiterjesztett tárolt eljárásokat is meghívhatnak.
A felhasználó által definiált függvények nem használhatják a dinamikus SQL- vagy temp-táblákat. Táblaváltozók engedélyezettek.
SET az utasítások nem engedélyezettek felhasználó által definiált függvényekben (például SET NOCOUNT ON;). A változó értékének hozzárendeléséhez használható a SET.
A FOR XML záradék nem engedélyezett.
A felhasználó által definiált függvények beágyazhatók; vagyis egy felhasználó által definiált függvény meghívhat egy másikat. A beágyazási szint növekszik, amikor a hívott függvény elkezdi a végrehajtást, és csökken, amikor a hívott függvény befejezi a végrehajtást. A felhasználó által definiált függvények legfeljebb 32 szintbe ágyazhatók. Ha túllépi a beágyazás maximális szintjét, a teljes hívófüggvénylánc meghiúsul. A Transact-SQL felhasználó által definiált függvényekből származó felügyelt kódra való hivatkozás egy szintnek számít a 32 szintű beágyazási korláttal szemben. A felügyelt kódból meghívott metódusok nem számítanak bele ebbe a korlátba.
A Transact-SQL felhasználó által definiált függvények definíciójában nem alábbi Service Broker-utasítások szerepelnek:
- BEGIN DIALOG CONVERSATION
- END CONVERSATION
- GET CONVERSATION GROUP
- MOVE CONVERSATION
- RECEIVE
- SEND

Engedélyek

CREATE FUNCTION engedélyt igényel az adatbázisban, és ALTER engedélyt arra a sémára, amelyben a függvény létrejön. Ha a függvény felhasználó által definiált típust ad meg, EXECUTE engedély szükséges a típushoz.

Skaláris függvények példái

Skaláris függvény (skaláris UDF)

Az alábbi példa létrehoz egy többutas skaláris függvényt (skaláris UDF) az AdventureWorks2022 adatbázisban. A függvény egy bemeneti értéket, egy ProductID, és egyetlen adatértéket ad vissza, a készletben megadott termék összesített mennyiségét.

IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL
    DROP FUNCTION ufnGetInventoryStock;
GO
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
RETURNS int
AS
-- Returns the stock level for the product.
BEGIN
    DECLARE @ret int;
    SELECT @ret = SUM(p.Quantity)
    FROM Production.ProductInventory p
    WHERE p.ProductID = @ProductID
        AND p.LocationID = '6';
     IF (@ret IS NULL)
        SET @ret = 0;
    RETURN @ret;
END;

Az alábbi példa a ufnGetInventoryStock függvénnyel adja vissza a 75 és 80 közötti ProductModelID rendelkező termékek aktuális készletmennyiségét.

SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;

További információ és példák a skaláris függvényekre: CREATE FUNCTION.

Példák táblaértékű függvényekre

Beágyazott táblaértékű függvény (TVF)

Az alábbi példa létrehoz egy beágyazott táblaértékű függvényt (TVF) az AdventureWorks2022 adatbázisban. A függvény egy bemeneti paramétert, egy áruház azonosítót használ, és visszaadja a ProductID, a Name, és az év eddigi értékesítéseinek összegét YTD Total az áruháznak eladott minden termékhez.

IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);

Az alábbi példa meghívja a függvényt, és megadja a 602-s ügyfélazonosítót.

SELECT * FROM Sales.ufn_SalesByStore (602);

Többutasításos táblaértékű függvény (MSTVF)

Az alábbi példa egy többutas táblaértékű függvényt (MSTVF) hoz létre az AdventureWorks2022 adatbázisában. A függvény egyetlen bemeneti paramétert vesz fel, egy EmployeeID-t, és visszaadja az összes alkalmazott listáját, akik közvetlenül vagy közvetve a megadott alkalmazottnak jelentenek. Ezután meghívják a függvényt, az alkalmazotti azonosítóként megadva a 109-es számot.

IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE
(
    EmployeeID int primary key NOT NULL,
    FirstName nvarchar(255) NOT NULL,
    LastName nvarchar(255) NOT NULL,
    JobTitle nvarchar(50) NOT NULL,
    RecursionLevel int NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
    AS (
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n
        FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        UNION ALL
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor
        FROM HumanResources.Employee e
            INNER JOIN EMP_cte
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
        )
-- copy the required columns to the result of the function
   INSERT @retFindReports
   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
   FROM EMP_cte
   RETURN
END;
GO

Az alábbi példa meghívja a függvényt, és megadja az 1. alkalmazotti azonosítót.

SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM dbo.ufn_FindReports(1);

További információt és példákat a beágyazott táblázatértékelt függvényekről (beágyazott TVF-ek) és többutas táblaértékű függvényekről (MSTVF-ek) a CREATE FÜGGVÉNYcímű témakörben találhat.

Ajánlott eljárások

Ha a felhasználó által definiált függvény (UDF) nem a SCHEMABINDING záradékkal jön létre, a mögöttes objektumokon végzett módosítások hatással lehetnek a függvény definíciójára, és váratlan eredményeket hozhatnak a meghíváskor. Javasoljuk, hogy az alábbi módszerek egyikét alkalmazza annak biztosítására, hogy a függvény ne váljon elavulttá a mögöttes objektumok változásai miatt:

Adja meg a WITH SCHEMABINDING záradékot az UDF létrehozásakor. Ez biztosítja, hogy a függvénydefinícióban hivatkozott objektumok csak akkor módosíthatók, ha a függvény is módosul.
Hajtsa végre a sp_refreshsqlmodule tárolt eljárást az UDF definíciójában megadott objektumok módosítása után.

Ha olyan UDF-t hoz létre, amely nem fér hozzá az adatokhoz, adja meg a SCHEMABINDING beállítást, amely megakadályozza, hogy a lekérdezésoptimalizáló szükségtelen készletkezelőket hozzon létre az ilyen UDF-eket tartalmazó lekérdezéstervekhez. A tekercsekkel kapcsolatos további információkért lásd a Showplan logikai és fizikai operátorok referenciája. A sémafüggvények létrehozásáról további információt sémafüggvényekcímű témakörben talál.

Az MSTVF-hez való csatlakozás egy FROM záradékban lehetséges, de rossz teljesítményt eredményezhet. Az SQL Server nem tudja használni az összes optimalizált technikát az MSTVF-ben található egyes utasításokon, ami egy optimálisnál rosszabb lekérdezési tervet eredményez. A lehető legjobb teljesítmény elérése érdekében a függvények helyett az alaptáblák közötti illesztéseket használja.

Az MSTVF-ek rögzített számosság-becslése 100 az SQL Server 2014 (12.x) verziótól kezdődően, és 1 az ennél korábbi SQL Server-verziók esetében.

Az SQL Server 2017-től (14.x) kezdődően az MSTVF-eket használó végrehajtási terv optimalizálása alkalmazhat szimultán végrehajtást is, amely a fenti heurisztikák helyett tényleges számosságot alkalmaz.

További információ: Többutas táblaértékű függvények összefűzött végrehajtása .

ANSI_WARNINGS nincs figyelembe véve, ha paramétereket ad át egy tárolt eljárásban, felhasználói függvényben, vagy ha változókat deklarál és beállít egy tételutasításban. Ha például egy változó karakter(3), majd három karakternél nagyobb értékre van beállítva, az adatok csonkolódnak a meghatározott méretre, és a INSERT vagy UPDATE utasítás sikeres lesz.

Megosztás a következőn keresztül: