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

Статья
12/15/2008

Определяемые пользователем встроенные функции представляют собой подмножество определяемых пользователем функций, возвращающих значения типа 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 для настройки сохраненного результирующего набора в соответствии с нуждами конкретных пользователей. Тем не менее, можно задать индексированное представление, в котором будет храниться полный набор данных, соответствующих представлению, а затем создать встроенную функцию, в которой содержатся параметризованные условия поиска, позволяющие пользователям настраивать результаты. Если определение представления является сложным, то работа по составлению результирующего набора в основном сводится к таким операциям, как построение статистических выражений и соединение таблиц, что приводит к созданию кластеризованного индекса представления. При последующем создании встроенной функции, обращающейся к индексированному представлению, в ней могут быть применены пользовательские параметризованные фильтры, что позволит возвращать конкретные строки данных результирующего набора индексированного представления. Например:

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

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

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

Затем пользователи смогут получить данные о конкретном магазине, указывая его во встроенной функции.
```
SELECT *
FROM fn_QuarterlySalesByStore(14432)
```

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

См. также

Основные понятия

Правила написания пользовательских функций
Возвращающие табличное значение пользовательские функции
Детерминированные и недетерминированные функции
Преобразование хранимых процедур в функции
Создание индексированных представлений

Справка и поддержка

Получение помощи по SQL Server 2005

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