Создание функций, определяемых пользователем

Завершено

Определяемые пользователем функции (UDF) похожи на хранимые процедуры, которые хранятся отдельно от таблиц в базе данных. Эти функции принимают параметры, выполняют действие, а затем возвращают результат действия в виде одного (скалярного) значения или результирующий набор (табличное значение). Затем можно использовать функцию вместо таблицы при написании инструкции SELECT. Определяемые пользователем функции предназначены для выполнения вычислений и использования этого результата в другой инструкции. В то время как хранимые процедуры могут инкапсулировать функцию и инструкцию, а также изменять данные в базе данных.

Мы рассмотрим три типа определяемых пользователем функций. Дополнительные сведения о различных функциях см. в справочной документации по T-SQL.

Функции с табличным значением типа inline

Встроенные табличные функции (TVF) — это простейшая функция, созданная на основе инструкции SELECT, и это предпочтительный выбор для производительности.

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

CREATE FUNCTION SalesLT.ProductsListPrice(@cost money)  
RETURNS TABLE  
AS  
RETURN  
    SELECT ProductID, Name, ListPrice  
    FROM SalesLT.Product  
    WHERE ListPrice > @cost; 

Когда функция с табличным значением запускается со значением параметра, то будут возвращены все продукты с ценой единицы больше, чем это значение.

Следующий код использует табличную функцию вместо таблицы.

SELECT Name, ListPrice  
FROM SalesLT.ProductsListPrice(500);

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

В отличие от встроенного TVF, многооператорная функция с табличным значением (MSTVF) может содержать несколько операторов и имеет разные требования к синтаксису.

Обратите внимание, что в следующем коде мы используем begin/END в дополнение к RETURN:

CREATE FUNCTION Sales.mstvf_OrderStatus ()
RETURNS
@Results TABLE
     ( CustomerID int, OrderDate datetime )
AS
BEGIN
     INSERT INTO @Results
     SELECT SC.CustomerID, OrderDate
     FROM Sales.Customer AS SC
     INNER JOIN Sales.SalesOrderHeader AS SOH
        ON SC.CustomerID = SOH.CustomerID
     WHERE Status >= 5
 RETURN;
END;

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

SELECT *
FROM Sales.mstvf_OrderStatus();

Вопросы, связанные с производительностью

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

В SQL Server версии 2017 и более поздних версиях корпорация Майкрософт представила функции интеллектуальной обработки запросов для повышения производительности MSTVF. Дополнительные сведения о функциях интеллектуальной обработки запросов см. в справочной документации по T-SQL.

Скалярные пользовательские функции

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

Ниже приведен пример получения каталожной цены для конкретного продукта в определенный день.

CREATE FUNCTION dbo.ufn_GetProductListPrice
(@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 
BEGIN
    DECLARE @ListPrice money;
        SELECT @ListPrice = plph.[ListPrice]
        FROM [Production].[Product] p 
        INNER JOIN [Production].[ProductListPriceHistory] plph 
        ON p.[ProductID] = plph.[ProductID] 
            AND p.[ProductID] = @ProductID 
            AND StartDate = @OrderDate
    RETURN @ListPrice;
END;
GO

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

    SELECT dbo.ufn_GetProductListPrice (707, '2011-05-31')

Привязка функции к объектам, на которые ссылается ссылка

SCHEMABINDING опционален при создании функции. При указании SCHEMABINDING функция привязывается к объектам, на которые ссылается ссылка, а затем объекты нельзя изменять, не изменяя функцию. Перед изменением объекта функция должна быть изменена или удалена, чтобы удалить зависимости.

SCHEMABINDING удаляется при возникновении любого из следующих действий:

• Функция удаляется
• Функция изменяется с помощью инструкции ALTER без указания SCHEMABINDING