Создание определяемых пользователем функций (ядро СУБД)
Применимо:SQL Server
База данных SQL Azure
Управляемый экземпляр SQL Azure
Служба аналитики Azure Synapse
Аналитическая платформа (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)
В следующем примере в базе данных 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
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
, чтобы оптимизатор запросов не создавал ненужные операторы отката для планов запросов, использующих эти определяемые пользователем функции. Дополнительные сведения о спулах см. в справочнике по логическим и физическим операторам Showplan. Дополнительные сведения о создании функций, привязанных к схеме, см. в соответствующем разделе.
Присоединение к MSTVF в условии FROM
возможно, но может привести к снижению производительности. SQL Server не может использовать все оптимизированные методы для некоторых инструкций, которые могут быть включены в MSTVF, что приводит к неоптимальному плану запросов. Чтобы получить наилучшую производительность, по возможности задавайте соединения не между функциями, а между базовыми таблицами.
MSTVFs имеют фиксированную кардинальность 100, начиная с SQL Server 2014 (12.x), и 1 для более ранних версий SQL Server.
Начиная с SQL Server 2017 (14.x), при оптимизации плана выполнения, который использует MSTVF, можно применять чередованное выполнение, что позволяет использовать фактическую кардинальность вместо указанных выше эвристик.
Дополнительные сведения см. в разделе Последовательное выполнение для функций с табличным значением с несколькими операторами.
ANSI_WARNINGS не учитывается при передаче параметров в хранимой процедуре, пользовательской функции или при объявлении и задании переменных в пакетной инструкции. Например, если объявить переменную как char(3), а затем присвоить ей значение длиннее трех символов, данные будут усечены до заданного размера, и инструкция INSERT
или UPDATE
будет успешной.