Операторы 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	Описание
	Успешное завершение
1-99	Предупреждение или информационное сообщение
	Конец файла (списка)
<0	Ошибка. Команда не выполнена

Пример:

WHEN SQLCODE -803

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

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

DO

BEGIN

…

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

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

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

Пример:

BEGIN

RETCODE=0;

WHEN ANY DO

BEGIN

RETCODE=1;

EXIT;

END

END

Создание триггеров

синтаксис команды:

CREATE TRIGGER <имя_триггера> FOR {ТАБЛИЦА | ОБЗОР}

{ACTIVE | INACTIVE}

{BEFORE | AFTER} {DELETE | INSERT | UPDATE}

[POSITION number]

AS

<тело триггера>

Синтаксические конструкции команды CREATE TRIGGER

Параметр	Описание
Имя_триггера	Задает имя триггера
Таблица | обзор	Имя таблицы или обзора, для которх создается триггер
ACTIVE | INACTIVE	Необязательная конструкция. определяет активность триггера. ACTIVE - триггер включен, INACTIVE – триггер отключен.
BEFORE | AFTER	Обязательный. Определяет, когда включается триггер. BEFORE – перед операцией, выполняемой над таблицей, AFTER – после операции, выполняемой над таблицей.
DELETE | INSERT | UPDATE	Указывает, при выполнении какой именно операции, будет включаться триггер.
POSITION number	Задает порядок, в котором будут выполняться триггеры (с одной таблицей и с одними и теми же включениями может быть связано несколько триггеров). Number должен быть целым числом между 0 и 32767 включительно. включение триггеров происходит в порядке возрастания номеров. Если триггеры имеют один и тот же номер, то они будут вклчаться в алфавитном порядке их имен.
тело триггера	Состоит из двух частей: 1) DECLARE VARIABLE <имя переменной> <тип_данных>; - объявляет локальные переменные, используемые только в триггере. 2) непосредственно тело триггера как последовательный набор операций, заключенный в операторные скобки

Рассмотрим примеры.

Пример 1. Триггер для контроля добавления данных в таблицу студент.

CREATE TRIGGER I_1

FOR STUDENT

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

IF (NEW.SURNAME IS NULL) THEN EXCEPTION NO_USER;

IF (NEW.NAME IS NULL) THEN NEW.NAME = "ИМЯ";

END

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

CREATE TRIGGER I_2

FOR STUDENT

ACTIVE BEFORE INSERT POSITION 1

AS

BEGIN

IF (NEW.UNIV_ID >0) THEN

IF (NOT EXISTS (SELECT * FROM UNIVERSITY

WHERE (UNIV_ID = NEW.UNIV_ID))) THEN EXCEPTION NO_UNIV;

END