- •Тема 4.3. Триггеры и хранимые процедуры
- •1. Триггеры и их назначение
- •2. Хранимые процедуры и их назначение
- •3. Sql для процедур и триггеров InterBase
- •3.1 Оператор присваивания
- •3.2 Оператор объявления переменных declare
- •3.3 Оператор генерации исключения
- •3.4 Оператор вызова процедуры
- •3.5 Оператор ветвления
- •3.6 Оператор цикла
- •3.7 Оператор цикла while
- •3.8 Оператор генерации события post_event
- •3.9 Операторы sql insert, update, select, delete
- •3.10 Оператор возврата значений выходных параметров suspend
- •3.11 Оператор прерывания процедуры exit
- •3.12 Оператор обработки ошибок when
- •4. Команды создания, удаления и модифиации триггеров
3.9 Операторы sql insert, update, select, delete
Внутри хранимых процедур и триггеров могут использоваться стандартные команды SQL: INSERT, UPDATE, SELECT, DELETE. Единственной особенностью этих команд внутри процедур и триггеров является то, что в них могут использоваться в качестве параметров локальные переменные процедур. Для того чтобы отличать локальные переменные от столбцов таблиц в командах INSERT, UPDATE, SELECT, DELETE, имена локальных переменных предваряются символом ":".
Кроме того, для помещения результатов выборки в локальные переменные к команде SELECT добавляется опция INTO.
3.10 Оператор возврата значений выходных параметров suspend
Оператор SUSPEND предназначен для использования в хранимых процедурах (в триггерах SUSPEND неприменим), причем только в процедурах выбора, хотя синтаксически допустим и в выполнимых процедурах.
Оператор SUSPEND приостанавливает выполнение процедуры выбора, возвращает управление вызвавшей программе и возобновляет работу со следующей команды, когда выполнена очередная команда FETCH. SUSPEND возвращает вызвавшей программе результаты работы процедуры в выходных параметрах.
SUSPEND не должен использоваться в выполнимых процедурах, так как команды, следующие за ним, никогда не будут выполнены. В выполнимых процедурах следует использовать оператор явного выхода EXIT.
В процедуре выбора команда SUSPEND возвращает текущие значения выходных параметров вызвавшей программе и продолжает выполнение. Если какой-либо выходной параметр не получил значение явно, то его содержимое непредсказуемо, что может привести к ошибкам, поэтому процедура должна обязательно присваивать значения всем выходным параметрам перед выполнением SUSPEND.
3.11 Оператор прерывания процедуры exit
И в процедурах выбора и в выполнимых процедурах оператор EXIT передает управление на конец процедуры (завершающий END).
Действие, выполняемое по достижении конца процедуры, зависит от ее типа:
В процедуре выбора конечная команда END возвращает управление вызвавшему ее приложению и устанавливает SQLCODE в 100; это указывает, что список найденных ею строк закончен.
В выполнимой процедуре конечная команда END возвращает управление вызвавшему ее приложению с установкой значений выходных параметров, если они есть.
3.12 Оператор обработки ошибок when
Оператор WHEN ... DO обеспечивает обработку возникших ошибок. Оператор применяется в хранимых процедурах и триггерах.
Синтаксис:
WHEN {<список_ошибок> / ANY}
DO <операторы>
< список_ошибок> ::=
{EXCEPTION имя_исключения / SQLCODE номер / GDSCODE код_ошибки}
Оператор WHEN должен быть последним в блоке BEGIN...END. С его помощью процедуры и триггеры могут обрабатывать ошибки трех типов:
Исключения, инициированные оператором EXCEPTION в данной процедуре или процедурах, прямо или косвенно вызванных данной, а также исключения, инициированные в триггерах, вызванных в результате действий этих процедур.
SQL-ошибки, идентифицирующиеся SQLCODE
Ошибки, идентифицирующиеся кодами ошибок InterBase.
Конструкция ANY позволяет выполнять операторы обработки при возникновении любых ошибок перечисленных типов.
Сводка синтаксиса оператора WHEN приведена в таблице.
Параметр |
Описание |
EXCEPTION имя_исключения |
Имя исключения (описанного в базе) |
SQLCODE номер |
Код ошибки - SQLCODE |
GDSCODE код_ошибки |
Код ошибки InterBase |
ANY |
Обеспечивает вызов обработчика для любых перехватываемых ошибок |
<операторы> |
Простой оператор или блок, осуществляющий обработку ошибок |
3.12.1 Обработка исключений
Вместо завершения работы при возникновении исключения процедура может обработать и возможно исправить ситуацию, приведшую к исключению. При возникновении исключения выполняются следующие действия:
Прекращается выполнение блока BEGIN ... END, содержащего исключение, и отменяются действия, выполненные в блоке.
Если блок содержит оператор WHEN, управление передается в WHEN, в противном случае аналогичные действия производятся в блоке, содержащем данный. Подобные действия производятся до тех пор, пока либо не будет найден соответствующий WHEN, либо не будет достигнут уровень процедуры. В последнем случае процедура будет завершена, а исключение не будет обработано.
Выполняются действия, определенные оператором (блоком), заданным в конструкции WHEN (если он обнаружен).
Управление возвращается оператору (блоку) программы, следующему за оператором WHEN.
Если исключение обработано с помощью WHEN, то соответствующее сообщение об ошибке не выдается.
3.12.2 Обработка ошибок SQL
Процедуры могут также обрабатывать ошибки SQL по кодам, возвращенным в SQLCODE. После выполнения каждой команды SQL формируется код ее завершения - SQLCODE, отражающий успешность выполнения, или код ошибки. SQLCODE может также содержать код предупреждения, типа того, что перечень строк в выборке по циклу FOR SELECT исчерпан.
Коды завершения команд SQL
SQLCODE |
Описание |
0 |
Успешное завершение |
1-99 |
Предупреждение или информационное сообщение |
100 |
Конец файла (списка) |
<0 |
Ошибка. Команда не выполнена |
Пример
WHEN SQLCODE -803
/*Попытка добавить строку со значением первичного ключа,
которое уже есть в таблице */
DO
BEGIN
…
3.12.3 Обработка ошибок InterBase
Процедуры могут обрабатывать ошибки InterBase по кодам, возвращенным в GDSCODE. Например, если команда в процедуре пытается модифицировать строку, уже модифицированную другой, еще не завершенной, транзакцией, то в этом случае процедура могла бы получать код ошибки InterBase, isc_lock_conflict. При повторении попытки ее модификации, другая транзакция может выполнить откат, сняв, таким образом, блокировку, что позволит успешно завершить команду. Используя инструкцию WHEN GDSCODE, процедура может обрабатывать ошибки конфликта блокировки и повторять его операцию.
В качестве примера рассмотрим фрагмент хранимой процедуры с возвращаемым параметром RETCODE, который устанавливается в 0 при нормальном выполнении процедуры и в 1 при возникновении каких либо ошибок
Пример:
BEGIN
RETCODE=0;
WHEN ANY DO
BEGIN
RETCODE=1;
EXIT;
END
END