8. Сервер баз данных ms sql Server. Принципы написания и отладки хранимых процедур и триггеров.[3. С.259-270, 18 – темы 7, 8, 9].

Триггеры

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

В MS SQL SERVER 2000 появился новый вид триггера — INSTEAD OF триггер. Его принципиальное отличие от обычных (AFTER) триггеров состоит в том, что он выполняется не после выполнения операции вставки/изменения/удаления, а вместо нее.

Простейший оператор создания триггера имеет вид:

CREATE TRIGGER < имя _ триггера >

ON < имя _ таблицы >

FOR [DELETE] [,] [INSERT] [,] [UPDATE]

AS

SQL- операторы (тело триггера)

В конкретных СУБД этот оператор может существенным образом отличаться. Для создания триггеров в MS SQL Server 2000 используется специальная команда:

CREATE TRIGGER имя _ триггера

ON таблица

[WITH ENCRYPTION]

{

{FOR | AFTER | INSTEAD OF }{[DELETE] [,] [INSERT] [,] [UPDATE] }

[WITH APPEND]

[NOT FOR REPLICATION]

AS

{ IF UPDATE (столбец _i) 

[ {AND | OR} UPDATE (столбец _j)]

[ ... n] 

| IF (COLUMNS_UPDATED() { побитовый _ оператор } битовая _ маска)

{оператор_сравнения} битовая_маска_столбца [... n ]

}

инструкции_SQL [ ... n ]

}

}

В параметре FOR задается тип триггера AFTER (после выполнения операции модификации) и INSTEAD OF (вместо выполнения операции модификации) и одна или несколько операций модификации, которые запускают данный триггер.

Опция WITH APPEND необходима, только если установленный уровень совместимости не превышает 65 и используется для создания дополнительных триггеров.

Параметр WITH ENCRIPTING имеет тот же смысл, что и для хранимых процедур, он скрывает исходный текст тела триггера.

NOT FOR REPLICATION показывает, что триггер не активизируется при модификации таблицы в процессе репликации.

IF UPDATE (столбец) — для операций добавления и обновления данных можно определить дополнительные условия на конкретный столбец таблицы; при указании нескольких столбцов они разделяются логическими операторами.

IF (COLUMNS_UDATED()) — выше было показано, как можно с помощью конструкции If update (столбец) определять, какие столбцы затрагиваются изменениями. 

Для отслеживания вносимых изменений в MS SQL Server используются специальные системные таблицы inserted и deleted. Таблица inserted содержит добавленные строки, а таблица deleted — удаленные. Нетрудно догадаться, что при выполнении операции изменения, будет использована и таблица inserted, и таблица deleted. 

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

SELECT *

FROM deleted

Нельзя создавать INSTEAD OF UPDATE и DELETE триггеры на таблицы, имеющие внешние ключи с установленными опциями каскадного изменения или удаления соответственно.

В общем случае не существует ограничения на количество триггеров. Стандартных триггеров типа AFTER для каждой операции модификации может быть разработано несколько. Все триггеры анализируются последовательно в порядке их создания. В отличие от After-триггеров существует ограничение на триггеры Instead Of. На каждую операцию модификации может быть создан только один триггер

Триггеры можно встраивать друг в друга, допускается 32 уровней вложенности. Если операции вложенного триггера нежелательны, SQL Server можно сконфигурировать так, чтобы отключить их.

Вложенные триггеры могут привести к рекурсии. Рекурсия бывает двух видов: прямая и косвенная. Прямая рекурсия получается в случае, если срабатывание триггера приводит к изменениям, которые вновь вызывают его же. Косвенная рекурсия получается, когда срабатывание триггера приводит к изменениям, которые приводят к срабатыванию другого триггера, что в свою очередь приводит к изменениям, вызывающим срабатывание первого триггера. Конечно же, цепочка может состоять не только из двух, но и из большего числа триггеров. Прямую рекурсию можно отключить (и включить) с помощью опции БД recursive triggers. Отключить (и включить) косвенную рекурсию, равно как и вложенность триггеров вообще, можно с помощью серверной опции nested_triggers. Эта опция определяет возможность вложенности триггеров не для одной конкретной БД, а для всего сервера.

Хранимые процедцры

С точки зрения приложений, работающих с БД, хранимые процедуры (Stored Procedure) — это подпрограммы, которые выполняются на сервере. По отношению к БД это объекты, которые создаются и хранятся в БД. Они могут быть вызваны из клиентских приложений. При этом одна процедура может быть использована в любом количестве клиентских приложений, что позволяет существенно сэкономить трудозатраты на создание прикладного программного обеспечения и эффективно применять стратегию повторного использования кода. Так же, как и любые процедуры в стандартных языках программирования, хранимые процедуры могут иметь входные и выходные параметры или не иметь их вовсе.

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

Хранимые процедуры пишутся на специальном встроенном языке программирования, они могут включать любые операторы SQL, а также включают некоторый набор операторов, управляющих ходом выполнения программ, которые во многом схожи с подобными операторами процедурно ориентированных языков программирования. В коммерческих СУБД для написания текстов хранимых процедур используются собственные языки программирования. Так, в СУБД Oracle для этого используется язык PL /SQL, а в MS SQL Server и System11 фирмы Sybase используется язык Transact SQL. В последних версиях Oracle объявлено использование языка Java для написания хранимых процедур.

Хранимые процедуры являются объектами БД. Каждая хранимая процедура компилируется при первом выполнении, в процессе компиляции строится оптимальный план выполнения процедуры. Описание процедуры совместно с планом ее выполнения хранится в системных таблицах БД. 

Хранимая процедура — это набор команд, хранимый на сервере и выполняемый как единое целое.

Для создания хранимой процедуры применяется оператор SQL CREATE PROCEDURE.

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

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

В MS SQL Server хранимая процедура создается оператором:

CREATE PROC[EDURE] <имя_процедуры> [;<версия>]

[{@параметр1 тип_данных}

[VARYING] [= <значение_по_умолчанию>] [ OUTPUT ]]

[,.параметр N ...]

[ WITH

{ RECOMPILE

| ENCRYPTION

| RECOMPILE, ENCRYPTION }]

[ FOR REPLICATION ]

AS

Тело процедуры

Здесь необязательное ключевое слово VARYING определяет, что в качестве выходного параметра используется результирующий набор — только для типа cursor

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

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

Ключевое слово ENCRYPTION определяет режим, при котором исходный текст хранимой процедуры не сохраняется в БД. Такой режим применяется для того, чтобы сохранить авторское право на интеллектуальную продукцию, которой и являются хранимые процедуры. Часто такой режим применяется, когда вы ставите готовую базу заказчику и не хотите, чтобы исходные тексты разработанных вами хранимых процедур были бы доступны администратору БД, работающему у заказчика. Однако надо помнить, что если вы захотите отредактировать текст хранимой процедуры сами, то вы его тоже не сможете извлечь из БД, его надо будет хранить отдельно, в некотором текстовом файле — и это не самое плохое. Но вот в случае восстановления БД после серьезной аварии для перекомпиляции потребуются первоначальные исходные тексты всех хранимых процедур. Поэтому защита вещь хорошая, но она усложняет сопровождение и модификацию хранимых процедур.

FOR REPLICATION — ключевые слова, показывающие, что эта процедура создается только для репликации.

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

Поскольку хранимая процедура является полноценным компонентом базы данных, то, как вы уже поняли, создать новую процедуру можно только для текущей базы данных. После создания в системе хранимой процедуры SQL Server компилирует ее и проверяет выполняемые внутри нее операторы. При обнаружении синтаксических ошибок сервер не позволит запустить созданную процедуру на решение. Если вы создаете хранимую процедуру в специальном редакторе внутри Enterprise Manager, то этот редактор не позволит вам сохранить текст процедуры пока в нем есть синтаксические ошибки.

Хранимая процедура может быть вызвана несколькими способами. Простейший способ — это использование оператора:

EXEC <имя процедуры> <значение_входного_параметра1>...

<имя_переменной_для_выходного параметра1>...

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

Однако если нам необходимо получить например, число экземпляров книги «Oracle8. Энциклопедия пользователя», которая имеет ISBN 966-7393-08-09, то текст вызова ранее созданной хранимой процедуры может быть следующим:

/*определили две переменные

@Ntek — количество экземпляров данной книги в наличие в библиотеке

@ISBN — международный шифр книги */

declare @ Ntek int

DECLARE @ ISBN VARCHAR (14) 

/* Присвоим значение переменной @ISBN */

Select @ ISBN = ’966-7393-08-09’

/* Присвоим переменной @Ntek результаты выполнения хранимой процедуры

COUNT_EX */

EXEC @Ntek = COUNT_EX @IS