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

Vonatkozik a következőkre:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalitikai Platform System (PDW)SQL adatbázis a Microsoft Fabric-ben

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

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 a TRY...CATCH, @ERROR vagy RAISERROR-t.

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.

Beágyazott, felhasználó által definiált függvények

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.

Service Broker-utasítások

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

Mellékhatás-függvények

A következő nemdeterminisztikus beépített függvények nem használhatók Transact-SQL felhasználó által definiált függvényekben (UDF).

• NEWID
• NEWSEQUENTIALID
• RAND
• TEXTPTR

Ha az alábbi függvények egyikére hivatkozik egy UDF-ben, a következő hibaüzenet jelenik meg:

Msg 443, Level 16, State 1
Invalid use of a side-effecting operator <operator> within a function.

A determinisztikus és nemdeterminista beépített rendszerfüggvények listáját lásd : Determinisztikus és nemdeterminista függvények.

A probléma megkerüléséhez becsomagolhatja a mellékhatás-függvényt egy nézetben, és meghívhatja a nézetet egy függvényen belülről.

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)

A következő példa egy többmondású skaláris függvényt (skaláris UDF) hoz létre az AdventureWorks2025 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 AS INT;
    SELECT @ret = SUM(p.Quantity)
    FROM Production.ProductInventory AS 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)

A következő példa egy sorbeli táblázatértékű függvényt (TVF) hoz létre az AdventureWorks2025 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
          INNER JOIN Sales.SalesOrderDetail AS SD
              ON SD.ProductID = P.ProductID
          INNER JOIN Sales.SalesOrderHeader AS SH
              ON SH.SalesOrderID = SD.SalesOrderID
          INNER 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)

A következő példa egy többállításos táblázatértékű függvényt (MSTVF) hoz létre az AdventureWorks2025 adatbázisban. 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 INT)
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 (
        -- Get the initial list of Employees for Manager n
        SELECT e.BusinessEntityID,
               e.OrganizationNode,
               p.FirstName,
               p.LastName,
               e.JobTitle,
               0
        FROM HumanResources.Employee AS e
             INNER JOIN Person.Person AS 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 AS e
             INNER JOIN EMP_cte
                 ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
             INNER JOIN Person.Person AS 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. Az orsókkal kapcsolatos további információkért lásd a logikai és fizikai showplan operátorra vonatkozó referenciát. 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 az SQL Server 2014 (12.x) esetében rögzített, 100-as számosság-becsléssel rendelkeznek, míg az SQL Server korábbi verziói esetében ez a szám 1.

Az SQL Server 2017 (14.x) és az azt követő verziók esetén az MSTVF-eket alkalmazó végrehajtási terv optimalizálása lehetővé teszi az interleaved végrehajtást, amely a korábban említett heurisztikák helyett a tényleges számosságot használja.

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.

Kapcsolódó tartalom

• felhasználó által definiált függvények
• CREATE függvény (Transact-SQL)
• ALTER FÜGGVÉNY (Transact-SQL)
• DROP függvény (Transact-SQL)
• DROP PARTITION FÜGGVÉNY (Transact-SQL)