Tworzenie funkcji zdefiniowanych przez użytkownika (aparat bazy danych)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

W tym artykule opisano sposób tworzenia funkcji zdefiniowanej przez użytkownika (UDF) w programie SQL Server przy użyciu języka Transact-SQL.

Ograniczenia

Funkcji zdefiniowanych przez użytkownika nie można używać do wykonywania akcji modyfikujących stan bazy danych.

Funkcje zdefiniowane przez użytkownika nie mogą zawierać klauzuli OUTPUT INTO, która ma tabelę jako element docelowy.

Funkcje zdefiniowane przez użytkownika nie mogą zwracać wielu zestawów wyników. Jeśli chcesz zwrócić wiele zestawów wyników, użyj procedury składowanej.

Obsługa błędów jest ograniczona w funkcji zdefiniowanej przez użytkownika. Funkcja UDF nie obsługuje TRY...CATCH, @ERROR ani RAISERROR.

Funkcje zdefiniowane przez użytkownika nie mogą wywoływać procedury składowanej, ale mogą wywoływać rozszerzoną procedurę składowaną.

Funkcje zdefiniowane przez użytkownika nie mogą używać dynamicznych tabel SQL ani tabel tymczasowych. Zmienne tabeli są dozwolone.

SET instrukcje nie są dozwolone w funkcji zdefiniowanej przez użytkownika (na przykład SET NOCOUNT ON;). Przypisanie wartości zmiennej może używać elementu SET.

Klauzula FOR XML nie jest dozwolona.

Zagnieżdżone funkcje zdefiniowane przez użytkownika

Funkcje zdefiniowane przez użytkownika można zagnieżdżać. Oznacza to, że jedna funkcja zdefiniowana przez użytkownika może wywołać inną. Poziom zagnieżdżania jest zwiększany, gdy wywołana funkcja rozpoczyna swoje wykonanie, i zmniejszany po jego zakończeniu.

Funkcje zdefiniowane przez użytkownika można zagnieżdżać maksymalnie na 32 poziomach. Przekroczenie maksymalnych poziomów zagnieżdżania powoduje niepowodzenie całego łańcucha funkcji wywołujących. Każde odwołanie do kodu zarządzanego z funkcji zdefiniowanej przez użytkownika Transact-SQL jest liczone jako jeden poziom względem limitu zagnieżdżenia na poziomie 32.

Metody wywoływane z poziomu kodu zarządzanego nie są liczone względem tego limitu.

Polecenia Service Broker

Następujące instrukcje Service Broker nie mogą być uwzględnione w definicji funkcji zdefiniowanej przez użytkownika Transact-SQL:

• BEGIN DIALOG CONVERSATION
• END CONVERSATION
• GET CONVERSATION GROUP
• MOVE CONVERSATION
• RECEIVE
• SEND

Funkcje z efektami ubocznymi

Następujące nieokreślone wbudowane funkcje nie mogą być używane w funkcji zdefiniowanej przez użytkownika (UDF) Transact-SQL.

• NEWID
• NEWSEQUENTIALID
• RAND
• TEXTPTR

Jeśli odwołujesz się do jednej z tych funkcji wewnątrz UDF, wystąpi następujący błąd:

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

Aby uzyskać listę deterministycznych i niedeterministycznych wbudowanych funkcji systemowych, zobacz Funkcje deterministyczne i niedeterministyczne.

Aby obejść ten problem, możesz opakować funkcję powodującą efekty uboczne w widoku i wywołać ten widok z poziomu funkcji.

Uprawnienia

Wymaga CREATE FUNCTION uprawnienia w bazie danych i ALTER uprawnienia do schematu, w którym jest tworzona funkcja. Jeśli funkcja określa typ zdefiniowany przez użytkownika, wymaga uprawnienia EXECUTE do tego typu.

Przykłady funkcji skalarnych

Funkcja skalarna (skalarna funkcja UDF)

Poniższy przykład tworzy wieloinstrukcyjną funkcję skalarną (scalar UDF) w bazie danych AdventureWorks2022. Funkcja przyjmuje jedną wartość wejściową, ProductIDi zwraca pojedynczą wartość danych, zagregowaną ilość określonego produktu w spisie.

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

W poniższym przykładzie użyto funkcji ufnGetInventoryStock, aby zwrócić bieżącą ilość zapasów dla produktów, które mają ProductModelID z zakresu od 75 do 80.

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

Aby uzyskać więcej informacji i przykłady funkcji skalarnych, zobacz CREATE FUNCTION.

Przykłady funkcji o wartości tabelarnej

Funkcja wbudowanych wartości tabeli (TVF)

W poniższym przykładzie w bazie danych AdventureWorks2022 zostanie utworzona funkcja w tabeli wbudowanej (TVF). Funkcja przyjmuje jeden parametr wejściowy, identyfikator klienta (sklep) i zwraca kolumny ProductID, Namei agregację sprzedaży od roku do daty jako YTD Total dla każdego produktu sprzedawanego w sklepie.

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
);

Poniższy przykład wywołuje funkcję i określa identyfikator klienta 602.

SELECT *
FROM Sales.ufn_SalesByStore(602);

Funkcja tabelaryczna z wieloma instrukcjami (MSTVF)

W poniższym przykładzie w bazie danych AdventureWorks2022 zostanie utworzona funkcja z wieloma instrukcjami o wartości tabeli (MSTVF). Funkcja przyjmuje pojedynczy parametr wejściowy, EmployeeID, i zwraca listę wszystkich pracowników, którzy podlegają określonemu pracownikowi bezpośrednio lub pośrednio. Następnie wywoływana jest funkcja określająca identyfikator pracownika 109.

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

Poniższy przykład wywołuje funkcję i określa identyfikator pracownika 1.

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

Aby uzyskać więcej informacji i przykłady funkcji tabel wbudowanych (inline TVF) i funkcji tabelarycznych z wieloma instrukcjami (MSTVF), zobacz CREATE FUNCTION.

Najlepsze rozwiązania

Jeśli funkcja zdefiniowana przez użytkownika (UDF) nie jest tworzona za pomocą klauzuli SCHEMABINDING, zmiany wprowadzone w obiektach bazowych mogą mieć wpływ na definicję funkcji i spowodować nieoczekiwane wyniki po wywołaniu. Zalecamy zaimplementowanie jednej z następujących metod, aby upewnić się, że funkcja nie staje się nieaktualna z powodu zmian w jego obiektach bazowych:

• Określ klauzulę WITH SCHEMABINDING podczas tworzenia UDF. Gwarantuje to, że obiekty, do których odwołuje się definicja funkcji, nie mogą być modyfikowane, chyba że funkcja zostanie również zmodyfikowana.

• Wykonaj procedurę składowaną sp_refreshsqlmodule po zmodyfikowaniu dowolnego obiektu określonego w definicji funkcji zdefiniowanej przez użytkownika (UDF).

W przypadku tworzenia funkcji zdefiniowanej przez użytkownika, która nie uzyskuje dostępu do danych, określ opcję SCHEMABINDING, aby uniemożliwić optymalizatorowi zapytań generowanie niepotrzebnych operatorów buforowania dla planów zapytań obejmujących te funkcje zdefiniowane przez użytkownika. Aby uzyskać więcej informacji na temat spuli, zobacz Odniesienie do operatora logicznego i fizycznego Showplan. Aby uzyskać więcej informacji na temat tworzenia funkcji powiązanej schematem, zobacz funkcje powiązane ze schematem.

Dołączenie do MSTVF w klauzuli FROM jest możliwe, ale może skutkować niską wydajnością. Program SQL Server nie może użyć wszystkich zoptymalizowanych technik w niektórych instrukcjach, które można uwzględnić w programie MSTVF, co powoduje nieoptymalny plan zapytania. Aby uzyskać najlepszą możliwą wydajność, jeśli to możliwe, użyj sprzężeń między tabelami podstawowymi zamiast funkcji.

MSTVFs mają stałą prognozę liczebności 100, poczynając od SQL Server 2014 (12.x), i 1 dla wcześniejszych wersji SQL Server.

** W programie SQL Server 2017 (14.x) i nowszych wersjach optymalizowanie planu wykonania wykorzystującego MSTVF może używać wykonania przeplatanego, co powoduje użycie rzeczywistej kardynalności zamiast wspomnianych wcześniej heurystyk.

Aby uzyskać więcej informacji, zobacz Przeplataną realizację dla funkcji tabelarycznych z wieloma instrukcjami.

ANSI_WARNINGS nie jest uwzględniana podczas przekazywania parametrów w procedurze składowanej, funkcji zdefiniowanej przez użytkownika lub podczas deklarowania i ustawiania zmiennych w instrukcji wsadowej. Jeśli na przykład zmienna jest zdefiniowana jako char(3), a następnie ustawiona na wartość większą niż trzy znaki, dane są obcinane do zdefiniowanego rozmiaru, a instrukcja INSERT lub UPDATE powiedzie się.

Powiązana zawartość

• funkcje zdefiniowane przez użytkownika
• CREATE FUNCTION (Transact-SQL)
• ALTER FUNCTION (Transact-SQL)
• DROP, FUNKCJA (Transact-SQL)
• DROP PARTITION FUNCTION (Transact-SQL)