Создание определяемых пользователем функций (ядро СУБД)

Применимо:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureСлужба аналитики Azure SynapseАналитическая платформа (PDW)

В этой статье описывается, как создать определяемую пользователем функцию (UDF) в SQL Server с помощью Transact-SQL.

Ограничения

Определяемые пользователем функции нельзя использовать для выполнения действий, изменяющих состояние базы данных.

Определяемые пользователем функции не могут содержать OUTPUT INTO клаузу, имеющую таблицу в качестве цели.

Определяемые пользователем функции не могут возвращать несколько результирующих наборов. Используйте хранимую процедуру, если нужно возвращать несколько результирующих наборов.

Обработка ошибок в функциях, определяемых пользователем, ограниченна. UDF не поддерживает TRY...CATCH, @ERROR или RAISERROR.

Определяемые пользователем функции не могут вызывать хранимую процедуру, но могут вызывать расширенную хранимую процедуру.

Определяемые пользователем функции не могут использовать динамические таблицы SQL или временные таблицы. Табличные переменные разрешены к использованию.

SET операторы не допускаются в функции, определяемой пользователем (например, SET NOCOUNT ON;). Для назначения значений переменной можно использовать SET.

Пункт FOR XML не допускается.

Вложенные пользовательские функции

Определяемые пользователем функции могут быть вложены. То есть одна определяемая пользователем функция может вызывать другую. Уровень вложенности увеличивается, когда начинается выполнение вызванной функции, и уменьшается, когда её выполнение завершается.

Вложенность определяемых пользователем функций не может превышать 32 уровней. Превышение максимального уровня вложенности приводит к сбою всей цепочки вызываемых функций. Каждая ссылка на управляемый код из пользовательской функции Transact-SQL засчитывается как один уровень в пределах 32 уровней вложенности.

Это ограничение не распространяется на методы, вызываемые из управляемого кода.

Инструкции Service Broker

Следующие инструкции компонента Service Broker не могут быть включены в определение определяемой пользователем функции Transact-SQL:

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

Функции с побочными эффектами

Следующие недетерминированные встроенные функции нельзя использовать в Transact-SQL определяемой пользователем функции (UDF).

• NEWID
• NEWSEQUENTIALID
• RAND
• TEXTPTR

Если вы ссылаетесь на одну из этих функций в UDF, вы получите следующую ошибку:

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

Список детерминированных и недетерминированных встроенных системных функций см. в разделе "Детерминированные и недетерминированные функции".

Чтобы обойти эту проблему, можно обернуть функцию с побочными эффектами в представление и вызвать представление из функции.

Разрешения

Требуется разрешение CREATE FUNCTION на базу данных и разрешение ALTER для схемы, в которой создается функция. Если в функции указан определяемый пользователем тип, требуется разрешение EXECUTE на этот тип.

Примеры скалярных функций

Скалярная функция (скалярная UDF)

В следующем примере создается многострочная скалярная функция (скалярная UDF) в базе данных AdventureWorks2022. Функция имеет один входной параметр ProductIDи возвращает одно значение — количество указанного товара на складе.

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

В следующем примере функция ufnGetInventoryStock используется для получения сведений о количестве товаров с идентификаторами ProductModelID от 75 до 80.

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

Дополнительные сведения и примеры скалярных функций см. в статье CREATE FUNCTION.

Примеры табличных функций

Встроенная табличная функция (TVF)

В следующем примере в базе данных AdventureWorks2022 создается встроенная табличная функция (TVF). Функция имеет один входной параметр — идентификатор клиента (магазина) — и возвращает столбцы ProductID, Nameи столбец YTD Total со сведениями о продажах продукта за текущий год.

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

В следующем примере вызывается функция с идентификатором клиента 602.

SELECT *
FROM Sales.ufn_SalesByStore(602);

Функция с табличным значением с несколькими операторами (MSTVF)

В следующем примере в базе данных AdventureWorks2022 создается функция с табличным значением с несколькими операторами (MSTVF). Функция имеет один входной параметр EmployeeID и возвращает список всех сотрудников, которые напрямую или косвенно отчитываются перед заданным сотрудником. Затем функция вызывается с указанием идентификатора сотрудника 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

В следующем примере вызывается функция с идентификатором сотрудника 1.

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

Дополнительные сведения и примеры встроенных табличных функций (встроенные TVFs) и многофакторных табличных значений функций (MSTVFs) см. в статье CREATE FUNCTION.

Лучшие практики

Если определяемая пользователем функция (UDF) не создается с SCHEMABINDING предложением, изменения, внесенные в базовые объекты, могут повлиять на определение функции и вызвать непредвиденные результаты. Рекомендуется реализовать один из следующих методов, чтобы убедиться, что функция не становится устаревшей из-за изменений в его базовых объектах:

• Укажите WITH SCHEMABINDING условие при создании UDF. Это гарантирует, что объекты, на которые ссылается определение функции, нельзя изменять, если функция также не изменяется.

• Выполняйте хранимую процедуру sp_refreshsqlmodule после изменения любого объекта, указанного в определении функции UDF.

При создании UDF, которая не обращается к данным, укажите параметр SCHEMABINDING, чтобы оптимизатор запросов не создавал ненужные операторы отката для планов запросов, использующих эти определяемые пользователем функции. Дополнительные сведения о спулях см. в справочнике по оператору логического и физического шоуплана. Дополнительные сведения о создании функций, привязанных к схеме, см. в соответствующем разделе.

Присоединение к MSTVF в условии FROM возможно, но может привести к снижению производительности. SQL Server не может использовать все оптимизированные методы для некоторых инструкций, которые могут быть включены в MSTVF, что приводит к неоптимальному плану запросов. Чтобы получить наилучшую производительность, по возможности задавайте соединения не между функциями, а между базовыми таблицами.

MSTVF имеют фиксированную оценку кардинальности 100 начиная с версии SQL Server 2014 (12.x) и 1 для более ранних версий SQL Server.

В SQL Server 2017 (14.x) и более поздних версиях оптимизация плана выполнения, использующего MSTVF, может осуществляться с помощью чередующегося выполнения, что позволяет использовать фактическую кардинальность вместо ранее указанных эвристических оценок.

Дополнительные сведения см. в разделе Последовательное выполнение для функций с табличным значением с несколькими операторами.

ANSI_WARNINGS не учитывается при передаче параметров в хранимой процедуре, пользовательской функции или при объявлении и задании переменных в пакетной инструкции. Например, если объявить переменную как char(3), а затем присвоить ей значение длиннее трех символов, данные будут усечены до заданного размера, и инструкция INSERT или UPDATE будет успешной.

Связанный контент

• Определяемые пользователем функции
• СОЗДАТЬ ФУНКЦИЮ (Transact-SQL)
• ALTER FUNCTION (Transact-SQL)
• DROP FUNCTION (Transact-SQL)
• УДАЛИТЬ ФУНКЦИЮ РАЗДЕЛА (Transact-SQL)