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

Статья
09/27/2024

Применимо: 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)

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

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

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

Рекомендации

Обратная связь

Дополнительные ресурсы

Поделиться через

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

ограничения

Разрешения

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

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

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

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

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

Рекомендации

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

Обратная связь

Дополнительные ресурсы