Oбpaбoткa oшибoк в PL/SQL
Нельзя создать приложение, которое будет безошибочно работать в любых ситуациях: возможны аппаратные сбои, невыявленные ошибки приложения и ошибки из-за некорректных действий пользователей приложения (клиентов). Если при этом программная ошибка произошла в блоке PL/SQL, вложенном в другой блок, а тот, в свою очередь, вложен в третий блок и т.д., то она может дойти до клиентского приложения. Чтобы устранить возможную отмену большого объема ранее выполненных операций и трафик из-за возвращаемых клиенту ошибок, чтобы посылать клиенту точные сообщения о причине ошибки и способе ее устранения (если она все же дошла до клиента), разработчики приложения должны предусматривать возможные программные ошибки и создавать процедуры, адекватно реагирующие на них.
В PL/SQL предусмотрен механизмы перехвата и обработки ошибок, возникающих при выполнении программы. Эти механизмы называются исключительными ситуациями.
Когда программа обнаруживает заданное условие ошибки, то вызывается соответствующая исключительная ситуация. Обработки исключительных ситуаций в программе производится в разделе EXCEPTION.
При обнаружении исключительной ситуации, обработка основного тела программы останавливается и управление передается соответствующему обработчику исключительной ситуации, который определяет дальнейшие действия.
В PL/SQL используются следующие типы исключительных ситуаций:
· встроенные исключительные ситуации;
· исключительные ситуации, определяемые пользователем;
· обработчик OTHERS.
Встроенные исключительные ситуации
Oracle включает четырнадцать встроенных исключительных ситуаций, соответствующих типовым ошибкам, приведенным в следующей таблице:
Исключительная ситуация ORACLE Описание
---------------------- --------- ---------------------------------------------
CURSOR_ALREADY_OPEN ORA-06511 Попытка открытия уже открытого курсора
DUP_VAL_ON_INDEX ORA-00001 Попытка вставить дубликат значения для
уникального индекса
INVALID_CURSOR ORA-01001 Попытка выполнения запрещенной операции с кур-
сором (например, закрытие неоткрытого курсора)
INVALID_NUMBER ORA-01722 Отказ преобразования строки символов в число
LOGIN_DENIED ORA-01017 Неправильное имя пользователь/пароль
NO_DATA_FOUND ORA-01403 Предложение SELECT...INTO возвращает ноль строк
NOT_LOGGED_ON ORA-01012 Нет подключения к Oracle7
PROGRAM_ERROR ORA-06501 Внутренняя ошибка PL/SQL
STORAGE_ERROR ORA-06500 Пакет PL/SQL вышел из пределов памяти или если
память разрушена
TIMEOUT_ON_RESOURCE ORA-00051 Истекло время ожидания ресурса Oracle7
TOO_MANY_ROWS ORA-01422 Предложение SELECT...INTO возвращает более
одной строки
TRANSACTION_BACKED_OUT ORA-00061 Удаленный сервер отменил транзакцию
VALUE_ERROR ORA-06502 Арифметическая ошибка, ошибка преобразования,
усечения или ограничения
ZERO_DIVIDE ORA-01476 Попытка деления на ноль
Если в раздел EXCEPTION программы (блока) включена фраза
WHEN имя_исключения THEN
текст_обработчика_исключения;
с именем какого-либо встроенного исключения и возникла соответствующая ошибка, то вместо прекращения исполнения программы и выдачи типового сообщения об ошибке, будет исполняться созданный пользователем текст обработчика исключения.
Такой обработчик может, например, выяснить ситуацию, при которой произошло деление на ноль, и выдать правдоподобный результат операции деления или прервать исполнение программы и дать сообщение об изменении каких-либо данных.
В последнем случае это может быть не типовое сообщение «Вы пытаетесь делить на ноль», а любое подготовленное пользователем сообщение, например, инструкцию длиной до 2048 символов.
Для выдачи сообщения об ошибке, обеспечения возврата в среду, из которой вызывалась текущая программа (блок) и отмены всех действий, выполненных в текущей транзакции, целесообразно использовать процедуру RAISE_APPLICATION_ERROR(errnum,errtext); где errnum – отрицательное целое число в диапазоне -20000.. -20999 и errtext – символьная строка длиной до 2048 символов.
В приведенном ниже триггере «shtins» использованы два типа встроенных исключительных ситуаций: NO_DATA_FOUND и TOO_MANY_ROWS.
DROP TRIGGER shtins;
CREATE TRIGGER shtins
BEFORE INSERT ON SHTAT
FOR EACH ROW
DECLARE
nach DATE;
kon DATE;
str NUMBER;
minraz NUMBER;
maxraz NUMBER;
nach_kon EXCEPTION;
err_str EXCEPTION;
nach_nach EXCEPTION;
err_razr EXCEPTION;
err_razr_pr EXCEPTION;
err_stavka EXCEPTION;
BEGIN
SELECT min_razr,max_razr INTO minraz,maxraz FROM dolgnosti
WHERE dolgn =:new.dolgn;
IF:new.razr NOT BETWEEN minraz AND maxraz THEN RAISE err_razr; END IF;
IF:new.razr_proc NOT BETWEEN 50 AND 100 THEN RAISE err_razr_pr; END IF;
IF:new.stavka NOT BETWEEN 0.25 AND 100 THEN RAISE err_stavka; END IF;
IF:new.nachalo >:new.konec THEN RAISE nach_kon; END IF;
SELECT MAX(stroka) INTO str FROM shtat;
IF:new.stroka <> str+1 THEN RAISE err_str; END IF;
<> -- метка блока, в котором производится поиск строк с
-- параметрами, аналогичными вводимым значениям
BEGIN
SELECT nachalo,konec INTO nach,kon FROM shtat
WHERE OTDEL =:new.otdel AND DOLGN =:new.dolgn AND RAZR =:new.razr
AND RAZR_PROC =:new.razr_proc AND KONEC =
(SELECT MAX(konec) FROM shtat
WHERE OTDEL =:new.otdel AND DOLGN =:new.dolgn
AND RAZR =:new.razr AND RAZR_PROC =:new.razr_proc);
IF:new.nachalo <= nach THEN RAISE nach_nach; END IF;
IF:new.nachalo <= kon THEN
UPDATE SHTAT SET konec = (:new.nachalo - 1)
WHERE OTDEL =:new.otdel AND DOLGN =:new.dolgn AND RAZR =:new.razr
AND RAZR_PROC =:new.RAZR_PROC AND konec = kon;
END IF;
EXCEPTION -- начало обработчика исключений блока find_strings
WHEN NO_DATA_FOUND THEN NULL; -- вызывается, если SELECT блока find_strings
-- не возвращает ни одной строки.
END find_strings;
EXCEPTION -- начало обработчика исключений основной программы
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20003,'Должности '||:new.dolgn||' не существует!');
WHEN err_razr THEN
RAISE_APPLICATION_ERROR(-20004,'Значение разряда не попадает в "вилку" разрядов');
WHEN err_razr_pr THEN
RAISE_APPLICATION_ERROR(-20005,'Разрядный процент должен находиться в пределах 50-100');
WHEN err_stavka THEN
RAISE_APPLICATION_ERROR(-20006,'Число ставок должно находиться в пределах 0.25-100');
WHEN nach_nach THEN
RAISE_APPLICATION_ERROR(-20007,'Дата начала должна быть больше '||to_char(nach));
WHEN TOO_MANY_ROWS THEN
RAISE_APPLICATION_ERROR(-20008,'Много строк; обратитесь к АБД.');
WHEN nach_kon THEN
RAISE_APPLICATION_ERROR(-20009,'Дата начала не может быть больше даты конца');
WHEN err_str THEN
RAISE_APPLICATION_ERROR(-20010,'Неправильный номер вводимой строки');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20011,'Какая-то другая ошибка');
END shtins;
Так как в большом приложении могут часто повторяться встроенные или пользовательские исключительные ситуации, то целесообразно создать в базе данных таблицу (например, USERERR) с уникальными номерами (error_number) и текстами (error_text) исключений. Это позволит избежать определения лишних сообщений об ошибках и сделать их согласованными во всем приложении.
При использовании такой таблицы и процедуры RAISE_APPLICATION_ERROR надо описать в в разделе DECLARE блока две переменных (например, errnum типа NUMBER и errtext типа VARCHAR2) и использовать в обработчике исключений конструкцию:
WHEN TOO_MANY_ROWS THEN
SELECT error_number,error_text INTO errnum,errtext FROM usererr
WHERE error_number = 20008;
RAISE_APPLICATION_ERROR(errnum,errtext);
или
WHEN TOO_MANY_ROWS THEN
SELECT error_number,error_text INTO errnum,errtext FROM usererr
WHERE errtext LIKE 'Много строк; обр%';
RAISE_APPLICATION_ERROR(errnum,errtext);