Табличные функции с множеством инструкций

Внедренные табличные функции

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

Внедренная табличная функция не имеет в своем теле блока BEGIN... END — вместо этого возвращается результирующий набор данных инструкции SELECT в виде таблицы с заданным именем:

CREATE FUNCTION имя_функции (параметры)
RETURNS Table AS
RETURN (инструкция_SELECT)

Следующая внедренная табличная функция является функциональным эквивалентом представления v_Customer созданного в лаб. занятии №6.

CREATE FUNCTION fCustomers ()

RETURNS TABLE

AS

RETURN

(

SELECT Customer.IdCust, Customer.FName, Customer.LName, City.CityName

FROM Customer INNER JOIN

City ON Customer.IdCity = City.IdCity

)

Для извлечения данных с помощью функции fCustomers вызовите ее в предложении FROM инструкции SELECT:

SELECT *

FROM dbo.fCustomers()

ORDER BY LName, FName

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

CREATE FUNCTION [dbo].[fCustomersForCity] (@IdCity int = NULL)

RETURNS TABLE

AS

RETURN

(

SELECT IdCust, FName, LName

FROM Customer

WHERE IdCity = @IdCity OR @IdCity IS NULL

)

Если функция вызывается с параметром по умолчанию, то возвращается список всех клиентов:

SELECT *

FROM dbo.fCustomersForCity(DEFAULT)

Если же в качестве параметра передается уникальный идентификатор города, то скомпилированная инструкция SELECT в функции вернет только клиентов из города с заданным кодом:

SELECT *

FROM dbo.fCustomersForCity(1)

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

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

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

CREATE FUNCTION имя_функиии (входные_параметры)
RETURNS @имя_таблицы TABLE (столбцы)
AS
BEGIN
Программный код заполнения табличной переменной
RETURN
END

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

1. В начале инструкции CREATE FUNCTION создается табличная переменная.

2. В теле функции с помощью инструкций INSERT заполняют переменную.

3. После выполнения функции значение табличной переменной передается во внешнюю процедуру как результат функции.

Запишем предыдущую функцию в виде многооператорной функции.

CREATE FUNCTION [dbo].[fCustomersByCity2]

(

@IdCity int = NULL

)

RETURNS

@Result TABLE

(

IdCust int,

FName nvarchar(20),

LName nvarchar(20)

)

AS

BEGIN

IF (@IdCity IS NULL)

INSERT @Result

SELECT IdCust, FName, LName

FROM Customer

ELSE

INSERT @Result

SELECT IdCust, FName, LName

FROM Customer

WHERE IdCity = @IdCity

RETURN

END

Задание для самостоятельной работы: Создайте пользовательские функции, возвращающие:

· Список всех товаров, которые не были ни разу заказаны

· Список всех заказов за заданный период времени.

Дата добавления: 2014-12-08