Бөлісу құралы:


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

Применимо: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure azure Synapse Analytics Analytics Platform System (PDW)

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

ограничения

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

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

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

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

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

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

  • SET операторы не допускаются в определяемой пользователем функции.

  • Предложение FOR XML не допускается.

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

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

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

Разрешения

Требуется разрешение 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 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;

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

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

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

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

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

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

В следующем примере функция вызывается с идентификатором 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 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

В следующем примере функция вызывается с идентификатором сотрудника 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 параметр, чтобы предотвратить создание оптимизатора запросов ненужных операторов spool для планов запросов, использующих эти определяемые пользователем функции. Дополнительные сведения об очередях см. в справочнике по логическим и физическим операторам Showplan. Дополнительные сведения о создании функций, привязанных к схеме, см. в соответствующем разделе.

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

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

Начиная с SQL Server 2017 (14.x), оптимизируя план выполнения, использующий MSTVFs, может использовать чередованное выполнение, что приводит к использованию фактического кратности вместо приведенной выше эвристики.

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

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