Sql для процедур и триггеров InterBase

SQL для процедур и триггеров в InterBase представляет собой законченный язык программирования для манипулирования данными.

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

• контекстные переменные допустимы только в триггерах;

• входные и выходные параметры, а также инструкции SUSPEND и EXIT, которые возвращают значения, которые возвращают значения, применимы только в хранимых процедурах.

Оператор присваивания

Формат:

Переменная = выражение;

Оператор объявления переменных DECLARE

Оператор DECLARE применяется в хранимых процедурах и триггерах и размещается в начале их тела непосредственно перед скобкой BEGIN, за которой размещаются все исполнимые операторы. Все используемые переменные должны быть объявлены. Оператор объявления завершается ";". Одним оператором может быть объявлена только одна переменная, и объявления имеют смысл только внутри хранимой процедуры или триггера.

Формат:

DECLARE VARIABLE <переменная> <тип данных>;

Оператор генерации исключения

Генерирует сообщение об ошибке (исключение). Применяется в хранимых процедурах и триггерах.

Формат:

EXCEPTION имя_исключения;

Имя_исключения – имя генерируемого исключения. Исключение с данным именем должно быть предварительно создано в базе командой CREATE EXCEPTION.

Оператор вызова процедуры

Оператор осуществляет вызов хранимой процедуры. Применяется в хранимых процедурах и триггерах.

Формат:

EXECUTE PROCEDURE <название> [<список параметров>]

[RETURNING_VALUES <список параметров>];

<название> – имя вызываемой процедуры. Сама процедура должна быть пред­варительно создана в базе командой CREATE PROCEDURE.

<список параметров>, заданный после имени процедуры – это список входных параметров процедуры (если процедура не требует параметров, то может отсутствовать). Отдельные параметры могут быть константами или переменными. Перед именем пере­менной ставится ":", кроме случаев использования контекстных переменных NEW и OLD. <список параметров>, заданный после RETURNING_VALUES – список возвращаемых процедурой значений (если процедура возвращает значения). Перед именем возвращаемых значении ставится ":". В триггерах и процедурах использование вызовов других процедур аналогично использованию подпрограмм в традиционных алгоритмических языках.

Замечание! При вызове из прикладных программ на базовых языках синтаксис вызова отличается от приведенных.

Оператор ветвления

Оператор ветвления IF ... THEN ... ELSE обеспечивает выполнение того или иного действия в зависимости от истинности проверяемого условия.

Формат:

If (<условие>) THEN <выражение1>

[ELSE <выражение 2>];

Если условие выполнено (значение TRUE), то выполняется оператор, следующий за конструкцией THEN, иначе выполняется оператор, следующий за конструкцией ELSE, если она присутствует.

Оператор цикла

Цикл FOR обеспечивает выполнение заданного оператора для каж­дой строки команды SELECT. Цикл FOR может использоваться в храни­мых процедурах и триггерах.

Формат:

FOR <выражение_SELECT> DO <операторы>;

FOR SELECT – инструкция цикла, которая отыскивает строку, указанную в <выражение_ select> и для каждой строки исполняет оператор или блок операторов, указанный после ключевого слова DO.

Конструкция <выражение_ select> представляет собой обычную команду SELECT, к которой добавлена обязательная здесь опция INTO и которая должна быть последней опцией команды SELECT.

В опции INTO перечисляются имена локальных переменных или па­раметров процедуры, которым присваиваются соответствующие значения выбранных командой SELECT столбцов. Имена переменных должны предваряться символом ":".

Пример.

FOR SELECT surname, name FROM student

INTO :fam, :s_name

DO

IF(fam>P1) THEN

IF(fam<P2) THEN SUSPEND;

Здесь оператор SUSPEND обеспечивает передачу вызывающему приложению данных, удовлетворяющих дополнительному условию.

Оператор цикла WHILE

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

Формат:

WHILE ( <условие>) DO <операторы>;

WHILE ... DO – оператор выполнения цикла, который повторяет оператор или блок операторов, указанных после DO, пока условие <условие> истинно. Условие проверяется в начале каждого цикла.

Оператор генерации события POST_EVENT

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

Сама обработка событий в InterBase строится по следующей схеме.

1. Приложение выдает команду

EVENT INIT список_событий (имя_событияl [,имя_события2 ...]);

По этой команде создается список событий, со­держащий имена событий имя_событияl , имя_события2 ...

2. То же или другое приложение выдает команду

EVENT WAIT список_событий;

По этой команде приложение приостанавливается и ожидает наступления одного из событий в списке событий.

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

Внутри триггеров и хранимых процедур реализуется только коман­да POST_EVENT.

Формат:

POST_EVENT <имя_события>;

Параметр <имя_события> может быть либо символьным литералом в кавычках, либо строковой переменной.

Замечание! Имена переменных в хранимых процедурах не должны предваряться символом ":" нигде, кроме как в командах SELECT, INSERT, UPDATE, DELETE, что позволяет отличать их от имен столбцов.

При выполнении процедуры команда POST_EVENT сообщает диспетчеру событий о наступлении события. Диспетчер событий следит за приложениями, ждущими событий, и извещает об их наступлении.

Операторы SQL INSERT, UPDATE, SELECT, DELETE

Внутри хранимых процедур и триггеров могут использоваться стандартные команды SQL: INSERT, UPDATE, SELECT, DELETE. Единственной особенностью этих команд внутри процедур и триггеров является то, что в них могут использоваться в качестве параметров локальные пе­ременные процедур. Для того чтобы отличать локальные переменные от столбцов таблиц в командах INSERT, UPDATE, SELECT, DELETE, имена локальных переменных предваряются символом ":".

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

Оператор возврата значений выходных параметров SUSPEND

Оператор SUSPEND предназначен для использования в хранимых процедурах (в триггерах SUSPEND неприменим), причем только в процедурах выбора, хотя синтаксически допустим и в выполнимых процедурах.

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

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

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

Оператор прерывания процедуры EXIT

И в процедурах выбора и в выполнимых процедурах оператор EXIT передает управление на конец процедуры (завершающий END).

Действие, выполняемое по достижении конца процедуры, зависит от ее типа:

• в процедуре выбора конечная команда END возвращает управление вызвавшему ее приложению и устанавливает SQLCODE в 100; это указывает, что список найденных ею строк закончен.

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

Оператор обработки ошибок WHEN

Оператор WHEN ... DO обеспечивает обработку возникших ошибок. Оператор применяется в хранимых процедурах и триггерах.

Формат:

WHEN {<список_ошибок> / ANY}

DO <операторы>

< список_ошибок> ::=

{EXCEPTION имя_исключения / SQLCODE номер / GDSCODE код_ошибки};

Оператор WHEN должен быть последним в блоке BEGIN...END. С его помощью процедуры и триггеры могут обрабатывать ошибки трех типов:

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

• SQL-ошибки, идентифицирующиеся SQLCODE;

• ошибки, идентифицирующиеся кодами ошибок InterBase.

Конструкция ANY позволяет выполнять операторы обработки при возникновении любых ошибок перечисленных типов.

Сводка синтаксиса оператора WHEN приведена в таблице.

Параметр	Описание
EXCEPTION имя_исключения	Имя исключения (описанного в базе)
SQLCODE номер	Код ошибки - SQLCODE
GDSCODE код_ошибки	Код ошибки InterBase
ANY	Обеспечивает вызов обработчика для любых перехватываемых ошибок
<операторы>	Простой оператор или блок, осуществляющий обработку ошибок

Обработка исключений

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

1. прекращается выполнение блока BEGIN ... END, содержащего исключение, и отменяются действия, выполненные в блоке;

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

3. выполняются действия, определенные оператором (блоком), заданным в конструкции WHEN (если он обнаружен).

Управление возвращается оператору (блоку) программы, следующе­му за оператором WHEN.

Если исключение обработано с помощью WHEN, то соответствую­щее сообщение об ошибке не выдается.

Обработка ошибок SQL

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

Коды завершения команд SQL

SQLCODE	Описание
0	Успешное завершение
1-99	Предупреждение или информационное сообщение
100	Конец файла (списка)
<0	Ошибка. Команда не выполнена

Пример:

WHEN SQLCODE -803

/*Попытка добавить строку со значением первичного ключа,

которое уже есть в таблице */

DO

BEGIN

…

Обработка ошибок InterBase

Процедуры могут обрабатывать ошибки InterBase по кодам, возвра­щенным в GDSCODE. Например, если команда в процедуре пытается модифицировать строку, уже модифицированную другой, еще не завер­шенной, транзакцией, то в этом случае процедура могла бы получать код ошибки InterBase, isc_lock_conflict. При повторении попытки ее моди­фикации, другая транзакция может выполнить откат, сняв, таким образом, блокировку, что позволит успешно завершить команду. Используя инст­рукцию WHEN GDSCODE, процедура может обрабатывать ошибки кон­фликта блокировки и повторять его операцию.

В качестве примера рассмотрим фрагмент хранимой процедуры с воз­вращаемым параметром RETCODE, который устанавливается в 0 при нор­мальном выполнении процедуры и в 1 при возникновении каких либо ошибок

Пример:

BEGIN

RETCODE=0;

WHEN ANY DO

BEGIN

RETCODE=1;

EXIT;

END

END