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 i 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.

• instrukcje SET nie są dozwolone w funkcji zdefiniowanej przez użytkownika.

• Klauzula FOR XML nie jest dozwolona.

• 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.

• 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

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

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

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

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 o rolkach, zobacz Showplan Logical and Physical Operators Reference (Dokumentacja operatorów logicznych i fizycznych programu 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.

MSTVF-y mają stałą prognozę kardynalności wynoszącą 100, począwszy od SQL Server 2014 (12.x), i 1 dla wcześniejszych wersji SQL Server.

Począwszy od SQL Server 2017 (14.x), optymalizacja planu wykonywania korzystającego z MSTVF może używać wykonywania naprzemiennego, co skutkuje użyciem rzeczywistej kardynalności zamiast powyższych 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 FUNCTION (Transact-SQL)
• DROP PARTITION FUNCTION (Transact-SQL)