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

Достоинства пользовательских функций перечислены ниже.

■ С их помощью можно внедрить в запросы сложную логику.

■ Создавая новые функции, можно проектировать сложные выражения.

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

■ Они обладают достоинствами хранимых процедур, так как могут быть скомпилированы и оптимизированы таким же способом.

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

■ Скалярные, возвращающие одно значение.

■ Внедренные табличные, аналогичные представлениям.

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

Скалярными называют те функции, которые возвращают одно значение. Эти функции могут принимать множество параметров, выполнять вычисления, но в результате выдают одно значение. Эти функции могут использоваться в любых выражениях, даже участвующих в ограничениях проверки. Скалярные функции должны быть детерминированными. Это значит, что для одних и тех же входных параметров они должны генерировать одно и то же выходное значение. В скалярных пользовательских функциях не допускаются операции обновления базы данных, но в то же время они могут работать с локальными временными таблицами. Они не могут возвращать данные BLOB (двоичные большие объекты) таких типов, как text, image и

ntext, равно как табличные переменные и курсоры.

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

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

RETURNS тип_данных

AS

BEGIN

текст_функции

RETURN выражение

END

В списке входных параметров должны быть указаны типы данных и, в случае

необходимости, значения по умолчанию, аналогично хранимым процедурам.

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

Все три типа пользовательских функций могут быть созданы с использованием преимуществ связывания со схемой. Представления могут связываться со схемой, но эта функция недоступна для хранимых процедур. Связывание со схемой делает невозможным изменение и удаление всех объектов, от которых зависит функция. Если связанная со схемой функция обращается к таблице ТаЫеА, то к последней могут быть добавлены столбцы; в то же время не может быть изменен или удален ни один из существующих ее столбцов, равно как и вся таблица.

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

CREATE FUNCTION FunctionName (Input Parameters)

RETURNS DataType

WITH SCHEMA BINDING

AS

BEGIN;

инструкции;

RETURNS выражение;

END;

 

Внедренные табличные функции. Создание, вызов внедренной табличной функции. Использование параметров. Коррелированные пользовательские функции.

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

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

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

RETURNS Table

AS

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

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

FROM инструкции SELECT:

SELECT LastName, Code, DateBegin

FROM dbo.fEventList()

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

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

Во внедренных табличных функциях может использоваться введенная в SQL Server 2005 команда APPLY. Существуют две формы команды APPLY; наиболее распространенной из них является CROSS APPLY. Команда CROSS APPLY объединяет данные из основного запроса с табличным набором данных из

пользовательской функции. Если функция не возвращает данных, то строка из основного запроса также не возвращается.

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

 

 

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

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

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

CREATE FUNCTION имя_функции (входные_параметры) RETURNS @ имя_таблицы TABLE (столбцы)

AS

BEGIN

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

RETURN

END

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

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

личная переменная @Price.

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

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

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

 

Реализация триггеров. Основы триггеров. Порядок выполнения транзакций. Создание триггеров. Триггеры After. Триггеры Instead of. Ограничения триггеров. Отключение триггеров. Создание списка триггеров. Триггеры и безопасность.

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

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

Любая транзакция проходит несколько проверок и программных кодов в следующем порядке:

1. Проверка Identity Insert

2. Ограничение допустимости пустых значений

3. Проверка типа данных

4. Выполнение триггера INSTEAD OF. Если такой триггер сущ-ет, то выполнение инструкции DML останавливается в этой точке. Такие триггеры не могут быть рекурсивными.

5. Ограничение первичного ключа.

6. Ограничение проверки

7. Ограничение внешнего ключа

8. Выполнение инструкции DML и обновление журнала транзакций

9. Выполнение триггера AFTER

10. Подтверждение транзакций

11. Запись в файл данных

Триггеры создаются и модифицируются с помощью стандартных команд языка DDL CREATE, ALTER и DROP следующим образом:

AFTER CREATE TRIGGER имя_триггера ON имя_таблицы

AFTER Insert, Update Delete

AS

Программный_ код_ триггера

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

Триггеры INSEAD OF заменяют транзакцию (т.е. выполняются вместо нее). Эти триггеры особенно полезны, когда заранее известно, что инструкция DML, запустившая триггер, почти наверняка будет отменена, а вместо нее должна быть реализована некоторая логика. Ограничения триггеров. Следующие инструкции недопустимы в триггерах:

1. CREATE, ALTER и DROP.(создание, изменение и удаление таблицы)

2. RECONFIGURE (изменение конфигурации

3. RESTORE (изменение БД)

4. DISK RESIZE (изменение размеров дискового пространства)

5. DISK INIT(инициализация диска)

Отключение триггеров. Для временного отключениятриггеров исп-ся инстр-я DDL ALTER TABLE с параметром DISABLE TRIGGER

ALTER TABLE имя_таблицы DISABLE TRIGGER имя_триггера

ALTER TABLE имя_таблицы ENABLE TRIGGER имя_триггера

Создание списка триггеров. Для этого используется следующий запрос:

Триггеры и безопасность. Только пользователи, принадлежащие фиксированной северной роли sysadmin или фиксированным ролям dbowner или ddladmin имеют разрешение на создание, изменение, включение или отключение триггеров. Программный код в триггере выполняется, предполагая разрешение владельца таблицы, к которой прикреплен триггер.

 

 

26. Взаимодействие триггеров. Организация триггеров. Вложенные триггеры. Рекурсивные триггеры. Триггеры Instead of и After. Множество триггеров After.

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

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

1. проверка данных

2. поддержка сложных правил бизнес-логики.

3. ведение журнала аудита

4. изменение даты

5. обеспечение сложной защиты данных

Вложенные триггеры – термин указывает на то, могут ли триггеры, вызываемые инструкциями DML, вызывать другие триггеры. Например, если параметр сервера Nested Triggerrs включен и некоторый триггер обновляет таблицу А, а эта таблица сама имеет триггер, то и он тоже будет вызван.

Рекурсивные триггеры являются уникальным типом вложенного типа After. Если триггер выполняет инструкцию DML, которая снова вызывает его самого, он называется рекурсивным. Если параметр БД Recursive_Triggers отключен, то рекурсивная операция триггеров становится невозможной. Триггер считается рекурсивным, если он напрямую инициирует самого себя. Если в триггере выполняется хранимая процедура, которая обновляет таблицу триггера, то такой рекурсивный вызов является непрямым и на него не распространяется действие параметра БД Recursive_Triggers.

Включаются с помощью инструкции ALTER DTATBASE

ALTER DTATBASE имя_базы_данных SET RECURSIVE TRIGGERS OFF| ON

Триггеры INSТEAD OF и AFTER. Если таблица для одного и того же события имеет триггеры INSТEAD OF и AFTER, то возможна следующая последовательность операций:

1. Инструкция DML инициирует транзацию.

2. Вместо инструкции DML выполняется триггерINSТEAD OF

3. Если триггер INSТEAD OF выполняет инструкцию DML для того же события таблицы, то процесс продолжается

4. Выполняется триггер AFTER.

Множество триггеров AFTER. Если некоторое событие таблицы имеет несколько триггеров AFTER, то все они будут выполнены. Каждый из триггеров имеет возможность откатить транзакцию => будет отменена вся работа, выполненная изначальной инструкцией и всеми уже выполненными триггерами.