Главная » Microsoft SQL Server, Базы данных » Внедренные табличные функции

0

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

Создание внедренной табличной функции

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

CREATE FUNCTION имя_функции (параметры)

RETURNS Table AS

RETURN (инструкция      SELECT)

Следующая внедренная табличная функция является функциональным эквивалентом представления vE vent View, созданного нами в главе 14.

USE СНА2 go

CREATE FUNCTION fEventList ()

RETURNS Table AS

RETURN(

SELECT dbo.CustomerType.Name AS Customer,

dbo.Customer.LastName, dbo.Customer.FirstName, dbo.Customer.Nickname,

dbo.Event_mm_Customer.ConfirmDate, dbo.Event.Code, dbo.Event.DateBegin, dbo.Tour.Name AS Tour, dbo.BaseCamp.Name, dbo.Event.Comment FROM dbo.Tour

INNER JOIN dbo.Event

ON dbo.Tour.TourlD = dbo.Event.TourlD INNER JOIN dbo.Event_mm_Customer

ON dbo.Event.EventID = dbo.Event_mm_Customer.EventID INNER JOIN dbo.Customer

ON dbo.Event_mm_Customer.CustomerlD = dbo.Customer.CustomerlD LEFT OUTER JOIN dbo.CustomerType ON dbo.Customer.CustomerTypeID

= dbo.CustomerType.CustomerTypeID INNER JOIN dbo.BaseCamp

ON dbo.Tour.BaseCampID = dbo.BaseCamp.BaseCampID)

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

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

SELECT LastName, Code, DateBegin FROM dbo.fEventList()

Результат выполнения инструкции (сокращенный):

LastName                        Code           DateBegin

Anderson                        01-003        2001-03-16 00:00:00.000

Brown  01-003               2001-03-16 00:00:00.000

Frank    01-003               2001-03-16 00:00:00.000

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

Сравнивая внедренные табличные функции с другими объектами SQL Server, можно сказать, что по производительности они стоят на одной ступеньке с хранимыми процедурами и на 5-10% быстрее представлений.

Использование параметров

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

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

USE OBXKites go

CREATE VIEW vPricelist AS

SELECT, Price.Price FROM dbo.Price

JOIN dbo.Product P

ON Price.ProductID = P.ProductID WHERE EffectiveDate =

(SELECT MAX(EffectiveDate)

FROM dbo.Price

WHERE ProductID = P.ProductID

AND Eff ectiveDate <= GetDateO)

Для ограничения этого списка ценой одного товара в вызывающую инструкцию добавляется предложение WHERE:

SELECT *

FROM vPriceList WHERE = *1001′

Будет получен следующий результат:

Code    Price

1001    14.9500

Из представления vPriceList SQL Server создает новую инструкцию SELECT, затем вызывает ограничение WHERE внешней инструкции и на их основании генерирует план выполнения запроса.

В противоположность этому функция позволяет передавать ограничение в качестве параметра в уже скомпилированную инструкцию SELECT:

CREATE FUNCTION dbo.fPriceList (

@Code CHAR(10) = Null, ©PriceDate DateTime)

RETURNS Table AS

RETURN(

SELECT Code, Price.Price FROM dbo.Price

JOIN dbo.Product P

ON Price.ProductID = P.ProductID WHERE EffectiveDate =

(SELECT MAX(EffectiveDate)

FROM dbo.Price

WHERE ProductID = P.ProductID

AND EffectiveDate <= @PriceDate)

AND (Code = @Code OR @Code IS NULL)

)

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

SELECT * FROM dbo.fPriceList(DEFAULT, ‘2/20/2002′)

Будет получен следующий результат:

Code    Price

1047    6.9500

1049    12.9500

Если же в качестве первого параметра передается код товара, то скомпилированная инструкция SELECT в функции вернет только одну строку, соответствующую заданному товару: SELECT * FROM dbo.fPriceList(‘1001′, ‘2/20/2002′)

Будет получен следующий результат:

Code    Price

1001    14.9500

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

Во внедренных табличных функциях может использоваться введенная в SQL Server 2005 команда APPLY; при этом используются различные значения параметров для разных строк, обрабатываемых главным запросом. Однажды, работая с версией SQL Server 2000, мне пришлось потратить немало времени, чтобы обойти это ограничение, так что мне не остается ничего иного, кроме как поблагодарить разработчиков SQL Server 2005.

Существуют две формы команды APPLY; наиболее распространенной из них является CROSS APPLY. Это имя может несколько ввести в заблуждение, поскольку она работает скорее как внутреннее (INNER JOIN), а не перекрестное объединение (CROSS JOIN). Команда CROSS APPLY объединяет данные из основного запроса с табличным набором данных из пользовательской функции. Если функция не возвращает данных, то строка из основного запроса также не возвращается, как в следующем примере:

USE СНА2 go

CREATE FUNCTION fEventList2 (@CustomerID INT)

RETURNS Table AS

RETURN (

SELECT dbo.CustomerType.Name AS Customer,

dbo.Customer.LastName, dbo.Customer.FirstName, dbo.Customer.Nickname,

dbo.Event_mm_Customer.ConfirmDate, dbo.Event.Code, dbo.Event.DateBegin, dbo.Tour.Name AS Tour, dbo.BaseCamp.Name, dbo.Event.Comment FROM dbo.Tour

INNER JOIN dbo.Event

ON dbo.Tour.TourlD = dbo.Event.TourlD INNER JOIN dbo.Event mm Customer

ON dbo.Event.EventID = dbo.Event_mm_Customer.EventID INNER JOIN dbo.Customer

ON dbo.Event_mm_Customer.CustomerlD = dbo.Customer.CustomerlD LEFT OUTER JOIN dbo.CustomerType ON dbo.Customer.CustomerTypeID

= dbo.CustomerType.CustomerTypeID INNER JOIN dbo.BaseCamp

ON dbo.Tour.BaseCampID = dbo.BaseCamp.BaseCampID WHERE Customer.CustomerlD = @CustomerID

)

SELECT C.LastName, Code, DateBegin, Tour FROM Customer С

CROSS APPLY fEventList2(C.CustomerlD)

ORDER BY C.LastName

Будет получен следующий результат:

LastName Code DateBegin                                           Tour

Anderson     01-003 2001-03-16 00:00:00.000 Amazon Trek

Anderson     01-006 2001-07-03 00:00:00.000 Bahamas Dive

Anderson    01-016 2001-11-16 00:00:00.000 Outer Banks Lighthouses

Andrews     01-015 2001-11-05 00:00:00.000 Amazon Trek

Andrews     01-012 2001-09-14 00:00:00.000 Gauley River Rafting

Andrews     01-014 2001-10-03 00:00:00.000 Outer Banks Lighthouses

Bettys          01-013 2001-09-15 00:00:00.000 Gauley River Rafting

Bettys          01-015 2001-11-05 00:00:00.000 Amazon Trek

Команда OUTER APPLY работает почти так же, как левое внешнее объединение. При таком использовании строки главного запроса включаются в результирующий набор данных, независимо от того, вернула ли внедренная табличная функция какие-либо данные.

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии