Главная » Microsoft SQL Server, Базы данных » Табличные функции с множеством инструкций

0

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

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

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

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

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

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

RETURNS @имя_та блицы TABLE (столбцы)

AS

BEGIN

Программный код заполнения табличной переменной RETURN END

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

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

2.              В теле функции две инструкции INSERT заполняют переменную @Price.

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

Функция f PriceAvg возвращает все цены из таблицы Price и среднее значение цены для каждого товара:

USE OBXKites go

CREATE FUNCTION fPriceAvg()

RETURNS @Price TABLE (Code CHAR(10),

EffectiveDate DATETIME,

Price MONEY)

AS

BEGIN

INSERT @Price (Code, EffectiveDate, Price)

SELECT Code, EffectiveDate, Price FROM Product JOIN Price

ON Price.ProductID = Product.ProductID INSERT @Price (Code, EffectiveDate, Price)

SELECT Code, Null, Avg(Price)

FROM Product JOIN Price

ON Price.ProductID = Product.ProductID GROUP BY Code

RETURN

END

Вызов функции

Для выполнения функции к ней производится обращение в предложении FROM инструкции SELECT:

SELECT *

FROM dbo.fPriceAvg()

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

Code EffectiveDate                                 Price

1001 2001-05-01 00:00:00.000 14.9500 1001 2002-06-01 00:00:00.000 15.9500

1001       2002-07-20 00:00:00.000 17.9500

Резюме

Пользовательские функции расширяют возможности объектов SQL Server и обеспечивают максимальную гибкость выражений и инструкций SELECT, в то же время принося в жертву переносимость приложения.

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

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

Реализация триггеров

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

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

Разработка триггеров предполагает знакомство с рядом вопросов, связанных с SQL Server, в том числе с порядком выполнения транзакций, блокировками, языком Т-SQL и хранимыми процедурами. Это является необходимым условием создания действенных триггеров. Триггеры состоят из нескольких уникальных элементов и требуют тщательного планирования. В то же время они обеспечивают гарантированное выполнение сложных правил бизнес-логики и проверки данных.

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

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

По теме:

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