• Статья

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

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

В следующем примере возвращаются названия магазинов и городов заданного региона.

USE AdventureWorks;
GO
CREATE VIEW CustomersByRegion
AS
SELECT DISTINCT S.Name AS Store, A.City
FROM Sales.Store AS S
    JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
    JOIN Person.Address AS A ON A.AddressID = CA.AddressID
    JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
WHERE SP.Name = N'Washington';
GO

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

USE AdventureWorks;
GO
IF OBJECT_ID(N'Sales.ufn_CustomerNamesInRegion', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_CustomerNamesInRegion;
GO
CREATE FUNCTION Sales.ufn_CustomerNamesInRegion
                 ( @Region nvarchar(50) )
RETURNS table
AS
RETURN (
        SELECT DISTINCT S.Name AS Store, A.City
        FROM Sales.Store AS S
        JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
        JOIN Person.Address AS A ON A.AddressID = CA.AddressID
        JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
        WHERE SP.Name = @Region
       );
GO
-- Example of calling the function for a specific region
SELECT *
FROM Sales.ufn_CustomerNamesInRegion(N'Washington');
GO

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

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

  • Предложение RETURNS содержит только ключевое слово table. Нет необходимости задавать формат возвращаемой переменной, поскольку он определяется форматом результирующего набора инструкции SELECT в предложении RETURN.

  • Нет инструкции function_body, разделенной ключевыми словами BEGIN и END.

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

  • Возвращающая табличное значение функция работает только с константами или аргументами @local_variable.

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

Встроенные функции также могут быть использованы для улучшения работы индексированных представлений. Индексированное представление не может использовать параметры в условиях поиска предложения WHERE для настройки сохраненного результирующего набора в соответствии с нуждами конкретных пользователей. Тем не менее, можно задать индексированное представление, в котором будет храниться полный набор данных, соответствующих представлению, а затем создать встроенную функцию, в которой содержатся параметризованные условия поиска, позволяющие пользователям настраивать результаты. Если определение представления является сложным, то работа по составлению результирующего набора в основном сводится к таким операциям, как построение статистических выражений и соединение таблиц, что приводит к созданию кластеризованного индекса представления. При последующем создании встроенной функции, обращающейся к индексированному представлению, в ней могут быть применены пользовательские параметризованные фильтры, что позволит возвращать конкретные строки данных результирующего набора индексированного представления. Например:

  1. Можно задать представление vw_QuarterlySales, которое собирает все данные по продажам в результирующий набор, содержащий все сводные сведения по квартальным продажам всех магазинов.

  2. Чтобы материализовать результирующий набор, содержащий сводные данные, необходимо создать кластеризованный индекс представления vw_QuarterlySales.

  3. Для фильтрации сводных данных необходимо создать встроенную функцию.

    CREATE FUNCTION dbo.ufn_QuarterlySalesByStore
     ( @StoreID int )
RETURNS table
AS
RETURN (
        SELECT *
        FROM SalesDB.dbo.vw_QuarterlySales
        WHERE StoreID = @StoreID
       )

  4. Затем пользователи смогут получить данные о конкретном магазине, указывая его во встроенной функции.

    SELECT *
FROM fn_QuarterlySalesByStore(14432)

Основной задачей запросов, созданных на шаге 4, является статистическая обработка продаж по кварталам. Указанная задача выполняется на шаге 2. В каждой отдельной инструкции SELECT шага 4 для фильтрации сводных данных используется функция fn_QuarterlySalesByStore.