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

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

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

В этом разделе

Перед началом работы

Ограничения и условия

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

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

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

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

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

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

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

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

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

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

    • НАЧАТЬ ДИАЛОГОВЫЙ РАЗГОВОР

    • ЗАВЕРШЕНИЕ БЕСЕДЫ

    • ПОЛУЧИТЬ ГРУППУ ДЛЯ БЕСЕД

    • ПЕРЕМЕСТИТЬ БЕСЕДУ

    • ПОЛУЧИТЕ

    • ОТПРАВИТЬ

Безопасность

Разрешения

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

Скалярные функции

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

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

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

Table-Valued Функции

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

В следующем примере создается табличная функция в базе данных AdventureWorks2012. Функция имеет один входной параметр 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  
-- Example invocation  
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
FROM dbo.ufn_FindReports(1);

См. также

Определяемые пользователем функции
CREATE FUNCTION (Transact-SQL)

  • Last updated on