Лабораторная работа №4. Обработка исключений

 1. Цель работы:

Ознакомиться с понятием исключительной ситуации, рассмотреть процесс обнаружения ошибки при выполнении программ PL/SQL и то, как программы реагируют на ошибки. Продемонстрировать, как описываются исключительные ситуации и как устанавливается соответствие между ними и различными ошибками: стандартными ошибками Oracle и ошибками, определяемыми пользователями. Изучить алгоритмы, посредством которых осуществляется передача исключительных ситуаций между разделами блоков PL/SQL.

 2. Методические указания

Лабораторная работа направлена на ознакомление с понятием исключений языка PL/SQL, а также на изучение основных принципов работы с исключениями.

Требования к результатам выполнения лабораторного практикума:

·       при выполнении задания необходимо сопровождать все реализованные процедуры и функции набором тестовых входных и выходных данных  и описаниями к ним;

·       при внесении изменений в базу данных с помощью языка DML использовать операторы управления транзакциями, в частности оператор COMMIT или ROLLBACK, для их корректного завершения;

·       по завершении выполнения задания составить отчет о проделанной работе.

При составлении и оформлении отчета следует придерживаться рекомендаций, представленных на следующей странице: http://unesco.kemsu.ru/student/rule/rule.html/

   3. Теоретический материал

В любой хорошо написанной программе должны существовать средства обработки, а при возможности и устранения ошибок. В PL/SQL обработка ошибок реализуется с помощью исключительных ситуаций (exception) и обработчиков исключительных ситуаций (exception handler). Исключительные ситуации могут быть связаны с ошибками Oracle или с ошибками, определяемыми пользователем. В этой главе описывается синтаксис исключительных ситуаций и их обработчиков, а также приводятся правила их распространения. В конце главы даются рекомендации по использованию исключительных ситуаций.

Понятие исключительной ситуации

В основе PL/SQL лежит язык программирования Ada, одним из свойств которого является механизм исключительных ситуаций. При использовании этого механизма написанные на PL/SQL программы становятся гораздо надежнее, и во время их выполнения предоставляется возможность обработки как запланированных, так и незапланированных ошибок. Исключительные ситуации в PL/SQL аналогичны также исключительным ситуациям в Java. Например, исключительные ситуации Java порождаются и перехватываются способом, подобным PL/SQL. Однако, в отличие от Java, исключительные ситуации PL/SQL не являются объектами и не имеют методов. Классификация ошибок, возникающих в программах PL/SQL, приведена ниже в таблице 1.

Таблица 1. Типы ошибок PL/SQL

Исключительные ситуации создаются только для обработки ошибок времени выполнения, но не для ошибок компиляции. Ошибки компиляции распознаются системой поддержки PL/SQL, и сообщения о них передаются пользователю. Такие ошибки программа обработать не может, поскольку на этом этапе она еще не выполняется. Ниже приводится блок, в котором возникает ошибка компиляции:

PLS-201: identifier - SSTUDENTS' must be declared

(идентификатор 'SSTUDENTS' должен быть объявлен)

так как в операторе SELECT неверно записан идентификатор students:

DECLARE

v_NumStudents NUMBER;

BEGIN

SELECT COUNT(*)

INTO vJiumStudents

FROM sstudents;

END;

Исключительные ситуации и их обработчики — это метод, при помощи которого программа реагирует на ошибки времени выполнения и устраняет их. В число ошибок выполнения входят ошибки SQL, например:

ORA-0001; unique constraint violated

(нарушение ограничения уникальности)

и процедурные опшбки, например:

ORA-06502: PL/SQL: numeric or value error

(ошибочное число или значение)

В случае возникновения ошибки инициируется (raised) исключительная ситуация. При этом управление программой передается обработчику исключительной ситуации, который является отдельным фрагментом программы. Отделение обработки ошибок от остальной части программы упрощает логическую структуру программы и обеспечивает фиксацию всех ошибок. Для обеспечения обработки ошибок в тех языках программирования, в которых не поддерживается модель исключительных ситуаций (таких, как язык С), программы должны содержать операторы, реализующие эту обработку. Например:

int х = 1, у = 2, z = 3;

f(x)i /* Вызов функции; х передается в качестве аргумента. */

if <ошибка>

handle_error (...);

У = 1 / z;

if <ошибка>

handle_error(...);

z = х + у;

if <ошибка>

handle_error(,..);

Проверка ошибок должна выполняться после каждого оператора программы. Если не внести в программу такую проверку, ошибки не будут обработаны надлежащим образом. Кроме того, операторы, реализующие обработку ошибок, засоряют программу и затрудняют понимание ее логической структуры. Если сравнить программу, приведенную в предыдущем примере, с аналогичной программой, написанной на PL/SQL:

DECLARE

х NUMBER := 1;

у NUMBER := 2;

z NUMBER := 3;

BEGIN

f(x);

У := 1 / z;

z = x + y;

EXCEPTION

WHEN OTHERS THEN

/* Обработчик всех ошибок */

handle_error(…);

END;

Заметьте, что раздел обработки ошибок отделен от логической схемы программы. В этом случае:

Логика программы нагляднее и легче для понимания.

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

Объявление исключительных ситуаций

Исключительные ситуации описываются в разделе объявлений блока, инициируются в выполняемом разделе, а обрабатываются в разделе исключительных ситуаций. Существуют два вида исключительных ситуаций: определяемые пользователем и стандартные (предопределенные).

Исключительные ситуации, определяемые пользователем

Исключительная ситуация, определяемая пользователем, обозначает такую ошибку, которая описывается программистом, причем совсем не обязательно, чтобы эта ошибка была ошибкой Oracle, — она может быть, например, ошибкой данных. Стандартные же исключительные ситуации соответствуют типичным ошибкам SQL и PL/SQL. Исключительные ситуации, определяемые пользователем, описываются в разделе объявлений блока PL/SQL. Как и переменные, исключительные ситуации имеют собственный тип (EXCEPTION) и область действия. Например:

DECLARE

e_TooManyStudents EXCEPTION;

e_TooManyStudents — это идентификатор, который виден во всем блоке. Область действия исключительной ситуации определяется так же, как и область действия переменной или курсора, описанного в том же разделе объявлений.

Стандартные исключительные ситуации

В Oracle существует ряд исключительных ситуаций, которые соответствуют типичным ошибкам Oracle. Как и стандартные типы данных (NUMBER, VARCHAR2 и т.д.), идентификаторы таких исключительных ситуаций описаны в модуле STANDARD. Эти идентификаторы доступны программе, и их не надо описывать в разделе объявлений, в отличие от исключительных ситуаций, определяемых пользователем. Стандартные исключительные ситуации приведены в таблице 2.

Таблица 2. Стандартные исключительные ситуации

Ниже приводится краткое описание некоторых стандартных исключительных ситуаций.

INVALID_CURSOR Эта ошибка возникает при выполнении над курсором запрещенной операции — например, при попытке закрыть уже закрытый курсор. Попытка открыть уже открытый курсор вызывает исключительную ситуацию CURSOR_ALREADY_OPEN.

NO_DATA_FOUND Эта исключительная ситуация устанавливается в двух случаях. Первый случай: оператор SELECT...INTO не возвращает ни одной строки. Если этот оператор возвращает более одной строки, генерируется исключительная ситуация TOO_MANY_ROWS. Второй случай: попытка обращения к элементу индексной таблицы PL/SQL, которому не присвоено значение. Ниже приводится анонимный блок, в котором устанавливается исключительная ситуация NO_DATA_FOUND:

DECLARE

2 TYPE t_NumberTableType IS TABLE OF NUMBER

3 INDEX BY BINARY_INTEGER;

4 v_NumberTable t_NumberTableType;

5 v_TempVar NUMBER;

6 BEGIN

7 v_TernpVar := v_NumberTable(1);

8 END;

*****

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at line 7

INVALID_NUMBER Эта исключительная ситуация устанавливается в SQL-операторах при неудачной попытке преобразования строки символов в число. В процедурных операторах генерируется другая исключительная ситуация — VALUE_ERROR. Например, в следующем операторе возникает INVALID_NUMBER, так как 'X' не является числом:

INSERT INTO students (id, first_name, lastjiame)

VALUES ( ' X ' , 'SCOTT', 'Smith');

STORAGE_ERROR и PROGRAM_ERROR Это внутренние исключительные ситуации. Причиной их возникновения является либо нехватка памяти (STORAGEJERROR), либо внутренняя ошибка PL/SQL (PROGRAM, ERROR). Чаще всего внутренние ошибки вызываются сбоями в работе системы PL/SQL, и о них следует сообщать в службу технической поддержки корпорации Oracle (Oracle Technical Support).

VALUE_ERROR Эта исключительная ситуация устанавливается при появлении арифметической ошибки, ошибки преобразования, усечения или ограничения в процедурном операторе. Если же ошибка возникает в SQL-операторе, то генерируется исключительная ситуация INVALID_NUMBER. Причиной ошибки VALUE_ERROR может быть операция присваивания, оператор SELECT...INTO, параметры RETURNING INTO оператора SQL или параметры подпрограммы. Все эти ситуации являются результатом присвоения значения переменной PL/SQL. Если возникает проблема с этим присваиванием, устанавливается VALUE_ERROR. До OracleS г текстом сообщения об ошибке VALUE_ERROR было "Numeric or Value Error" (ошибочное число или значение). Однако в OracleS г сообщение об ошибке будет указывать причину ошибки. Приведем примеры:

DECLARE

2 v_TempVar VARCHAR2(3);

3 BEGIN

4 v_TempVar := 'ABCD';

5 END;

***

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at line 4

DECLARE

2 v_TempVar NUMBER(2);

3 BEGIN

4 SELECT id

b INTO v_TempVar

6 FROM students

7 WHERE last_name = 'Smith';

8 END;

****

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error: number precision too large

ORA-06512: at line 4

ROWTYPE_MISMATCH Эта исключительная ситуация устанавливается при несоответствии типов базовой курсорной переменной и курсорной переменной PL/SQL. Например, ROWTYPE_MISMATCH возникает при несоответствии фактического и формального типов, которые возвращаются процедурой, использующей в качестве аргумента курсорную переменную.

Инициирование исключительных ситуаций

Когда возникает ошибка, связанная с некоторой исключительной ситуацией, инициируется (устанавливается) эта исключительная ситуация. Исключительные ситуации, определяемые пользователем, устанавливаются явно при помощи оператора RAISE, в то время как стандартные исключительные ситуации (или определенные пользователем исключительные ситуации, связанные с ошибкой Oracle посредством прагмы EXCEPTIONJNIT) инициируются неявно при возникновении соответствующих ошибок Oracle. Если возникает ошибка Oracle, не связанная с исключительной ситуацией, тоже инициируется исключительная ситуация. Ее можно перехватывать с помощью обработчика OTHERS (см. ниже раздел "Обработчик исключительных ситуаций OTHERS"). Стандартные исключительные ситуации при желании можно устанавливать также с помощью оператора RAISE. Усложним пример, приведенный в разделе "Исключительные ситуации, определяемые пользователем":

DECLARE

-- Исключительная ситуация для указания условия ошибки

eJTooManyStudents EXCEPTION;

-- Текущее число студентов, зарегистрированных в HIS-101

v_CurrentStudents NUMBER(3);

-- Максимальное число студентов, допустимое в HIS-101

v_MaxStudents NUMBER(3);

BEGIN

/* Определим текущее число зарегистрированных студентов и

максимальное число студентов. */

SELECT current_students, max_students

INTO v_CurrentStudents, v_MaxStudents

FROM classes

WHERE department = 'HIS' AND course = 101;

/* Сравним полученные значения. */

IF v_CurrentStudents > v_MaxStudents THEN

/* Зарегистрировано слишком много студентов -- установим

исключительную ситуацию. */

RAISE e_TooManyStudents;

ENF IF;

END;

При установлении исключительной ситуации управление программой сразу же передается разделу исключительных ситуаций блока. Если такого раздела нет, исключительная ситуация передается блоку (распространяется на блок), в который входит данный блок. После передачи управления обработчику невозможно вернуться в выполняемый раздел блока. Стандартные ситуации инициируются автоматически при возникновении соответствующей ошибки Oracle. Например, в этом блоке PL/SQL устанавливается исключительная ситуация DUP_VAL_ON_INDEX:

BEGIN

INSERT INTO students (id, first_name, last_name)

VALUES (20000, 'John', 'Smith');

INSERT INTO students (id, first_name, last_name)

VALUES (20000, 'Susan', 'Ryan');

END;

Исключительная ситуация устанавливается вследствие того, что столбец id таблицы students является первичным ключом, и поэтому он должен быть уникальным. При попытке второго оператора INSERT ввести значение 20000 в этот столбец происходит ошибка:

ORA-0001: unique constraint (<constraint name>) violated

(нарушение ограничения уникальности)

Эта ошибка соответствует исключению DUP_VAL_ON_INDEX.

Обработка исключительных ситуаций

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

EXCEPTION

WHEN имя_исключителъной_ситуауии1 THEN

последовательностъ_операторов1;

WHEN имя_исключителъной_ситуации2 THEN

последовательности операторов2;

[ WHEN OTHERS THEN

последователъностъ_операторовЗ; ]

END;

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

DECLARE

-- Исключительная ситуация для указания условия ошибки

e_TooManyStudents EXCEPTION;

-- Текущее число студентов, зарегистрированных в HIS-101

v_CurrentStudents NUMBER(3);

-- Максимально допустимое число студентов в HIS-101

v_MaxStudents NUMBER(3);

BEGIN

/* Определим текущее число зарегистрированных студентов и

максимальное число студентов. */

SELECT current_students, max_students

INTO v_CurrentStudents, v_MaxStudents

FROM classes

WHERE department = 'HIS' AND course = 101;

/* Сравним полученные значения. */

IF v_CurrentStudents > v_MaxStudents THEN

/* Зарегистрировано слишком много студентов -- установим

исключительную ситуацию. */

RAISE e_TooManyStudents;

END IF;

EXCEPTION

WHEN e_TooManyStudents THEN

/* Обработчик, выполняющийся в том случае, если в HIS-101

зарегистрировано слишком много студентов. Введем сообщение,

поясняющее сложившуюся ситуацию. */

INSERT INTO log_table (info)

VALUES ('History 101 has' | |v_CurrentStudents ||

'students: max allowed is' || v_MaxStudents);

END;

Один обработчик может обслуживать несколько исключительных ситуаций, для чего нужно перечислить их имена в условии WHEN, отделив одно от другого ключевым словом OR (или):

EXCEPTION

WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN

INSERT INTO log_table (info)

VALUES ( 'A select error occu red.');

END;

Данная исключительная ситуация может обрабатываться максимум только одним обработчиком в разделе обработки исключений. Если для исключительной ситуации имеется более одного обработчика, компилятор PL/SQL будет инициировать ошибку PLS-483:

DECLARE

2 - Объявим два определенных пользователем исключения

3 e_Exception1 EXCEPTION;

4 e_Exception2 EXCEPTION;

5 BEGIN

6 - Инициируем только исключение 1

7 RAISE e_Exception1;

8 EXCEPTION

9 WHEN e_Exception2 THEN

10 INSERT INTO log_table (info)

11 VALUES ('Handler 1 executed!');

12 WHEN e_Exception1 THEN

13 INSERT INTO log_table (info)

14 VALUES ('Handler 3 executed!');

15 WHEN e_Exception1 OR e_Exception2 THEN

16 INSERT INTO log_table (info)

17 VALUES ('Handler 4 executed!');

18 END;

WHEN e_Exception1 OR e_Exception2 THEN

***

ERROR at line 15:

ORA-06550: line 15, column 3:

PLS-00483: exception 'E_EXCEPTION2' may appear in at most one exception

handler in this block

ORA-06550: line 0, column 0:

PL/SQL: Compilation unit analysis terminated

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

PL/SQL определяет специальный обработчик исключительных ситуаций — WHEN OTHERS. Обработчик OTHERS (другие) выполняется для всех инициированных исключительных ситуаций, которые не обработаны другими предложениями WHEN, определенными в текущем разделе исключений (аналогично базовому классу Exception в Java). Он всегда должен быть последним обработчиком в блоке, чтобы все предыдущие (и более специальные) обработчики были перед этим просмотрены. WHEN OTHERS будет перехватывать все исключения, предварительно определенные и определяемые пользователем. Обработчик OTHERS рекомендуется указывать на самом высоком уровне программы (в самом внешнем блоке) для обеспечения распознавания всех возможных ошибок. Иначе ошибка будет распространяться в вызывающую среду (см. ниже раздел "Распространение исключительных ситуаций"). Это может привести к нежелательным последствиям, таким как откат текущей транзакции.

Добавим в рассматриваемый пример обработчик OTHERS:

DECLARE

-- Исключительная ситуация для указания условия ошибки

e_TooManyStudents EXCEPTION;

-- Текущее число студентов, зарегистрированных в HIS-101

v_CurrentStudents NUMBER(3);

-- Максимально допустимое число студентов в HIS-101

v_MaxStudents NUMBER(3);

BEGIN

/* Определим текущее число зарегистрированных студентов и

максимальное число студентов, */

SELECT current_students, max_students

INTO v_CurrentStudents, v_MaxStudents

FROM classes

WHERE department = 'HIS' AND course = 101;

/* Сравним полученные значения. */

IF v_CurrentStudents > v_MaxStudents THEN

/* Зарегистрировано слишком много студентов -- установим

исключительную ситуацию. */

RAISE e_TooManyStudents;

END IF;

EXCEPTION

WHEN e_TooManyStudents THEN

/* Обработчик, выполняющийся в том случае, если в HIS-101

зарегистрировано слишком много студентов. Введем сообщение,

поясняющее сложившуюся ситуацию. */

INSERT INTO log_table (info)

VALUES ('History 101 has' || v_CurrentStudents ||

'students: max allowed is' || v_MaxStudents);

WHEN OTHERS THEN

/* Обработчик, выполняющийся для всех других ошибок. */

INSERT INTO log_table (info) VALUES ('Another error occurred');

END;

В этом примере обработчик OTHERS регистрирует факт возникновения ошибки, но не определяет ее. Сведения об ошибке, приведшей к установлению исключительной ситуации, обрабатываемой OTHERS, можно получить при помощи предопределенных функций SQLCODE и SQLERRM.

Совет

Не используйте в коде обработчик исключительных ситуаций типа WHEN OTHERS THEN NULL; так каком будет молчаливо перехватывать все неожиданные ошибки, не сообщая о том, что они'произошли. Хороший обработчик OTHERS должен регистрировать ошибку и, возможно, дополнительную информацию для последующего анализа.

SQLCODE и SQLERRM

При использовании обработчика OTHERS бывает полезно знать, какая ошибка Oracle установила исключительную ситуацию. Можно регистрировать не только факт возникновения ошибки, но и ее тип, если это необходимо для выполнения конкретных действий. В PL/SQL такие сведения получают при помощи двух встроенных функций: SQLCODE и SQLERRM. SQLCODE возвращает код текущей ошибки, a SQLERRM — текст сообщения об ошибке. Для исключений, определяемых пользователем, SQLCODE возвращает 1, a SQLERRM возвращает "User-defined Exception" (определенное пользователем исключение).

Внимание

Функция DBMS_UTILITY.FORMAT_ERROR_STACKTavKe возвращает код текущей ошибки и может быть использована в дополнение к функции SQLERRM (см. приложение А).

Ниже приводится полный текст блока PL/SQL, в котором используется обработчик исключительных ситуаций OTHERS.

DECLARE

-- Исключительная ситуация для указания условия ошибки

e_TooManyStudents EXCEPTION;

-- Текущее число студентов, зарегистрированных в HIS-101

v_CurrentStudents NUMBER(3);

-- Максимально допустимое число студентов в HIS-101

v_MaxStudents NUMBER(3);

-- Переменная для хранения кода ошибки

v_ErrorCode NUMBER;

-- Переменная для хранения текста сообщения об ошибке

v_ErrorText VARCHAR2(200);

BEGIN

/* Определим текущее число зарегистрированных студентов и

максимально допустимое число студентов. */

SELECT current_students, max_students

INTO v_CurrentStudents, v_MaxStudents

FROM classes

WHERE department = 'HIS' AND course = 101;

/* Сравним полученные значения. */

IF v_CurrentStudents > v MaxStudents THEN

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

/* Зарегистрировано слишком много студентов -- установим

исключительную ситуацию. */

RAISE e_TooManyStudents;

END IF;

EXCEPTION

WHEN e_TooManyStudents THEN

/* Обработчик, выполняющийся в случае, если в HIS-101

зарегистрировано слишком много студентов. Введем сообщение,

поясняющее сложившуюся ситуацию. */

INSERT INTO log_table (info)

VALUES ('History 101 has' | v_CurrentStudents ||

'students: max allowed is' || v_MaxStudents);

WHEN OTHERS THEN

/* Обработчик, выполняющийся для всех других ошибок */

v_ErrorCode := SQLCODE;

--Обратите внимание на использование SUBSTR

v_ErrorText := SUBSTR(SQLERRM, 1, 200);

INSERT INTO log_table (code, message, info) VALUES

(v_ErrorCode, v_ErrorText, 'Oracle error occurred');

END;

Максимальная длина сообщения об ошибке Oracle составляет 512 символов. В примере переменная v_ErrorText ограничена 200 символами (для соответствия полю code таблицы logjtable). Если текст сообщения об ошибке превышает 200 символов, операция Q v_ErrorText := SQLERRM; сама вызывает стандартную исключительную ситуацию VALUE_ERROR. Во избежание этого используется встроенная функция SUBSTR, обеспечивающая присваивание переменной v_ErrorText не более чем 200 символов.

Значения функций SQLCODE и SQLERRM сначала присваиваются локальным переменным, и только потом эти переменные указываются в SQL-операторе. Данные функции являются процедурными, поэтому их нельзя использовать непосредственно в SQL-операторе.

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

ORA-0000: normal, successful completion

Если в SQLERRM передается положительное значение, отличное от +100, выдается сообщение

non-ORACLE Exception

SQLERRM(IOO) возвращает

ORA-1403: no data found

При вызове в обработчике функция SQLCODE возвращает отрицательное число, обозначающее ошибку Oracle. Единственным исключением является ошибка "ORA-1403: no data found", когда SQLCODE возвращает +100.

Если SQLERRM без аргументов вызывается из выполняемого раздела блока, она всегда возвращает сообщение

ORA-0000: normal, successful completion

A SQLCODE возвращает 0. Все эти ситуации показаны в следующем примере:

BEGIN

2 DBMS_OUTPUT.PUT_LINECSQLERRM(0): ' || SQLERRM(O));

3 DBMS_OUTPUT.PUT_LINECSQLERRM(100): ' | SQLERRM(IOO));

4 DBMS_OUTPUT.PUT_LINE('SQLERRM(10): ' | | SQLERRM(IO));

5 DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);

6 DBMS_OUTPUT.PUT_LINECSQLERRM(-1): ' | SQLERRM(-1));

7 DBMS_OUTPUT.PUT_LINE(1SQLERRM(-54): ' || SQLERRM(-54));

8 END

SQLERRM(O): ORA-0000: normal, successful completion

SQLERRM(IOO): ORA-01403:'no data found

SQLERRM(IO): -10: non-ORACLE exception

SQLERRM: ORA-0000: normal, successful completion

SQLERRM(-I): ORA-00001: unique constraint (.) violated

SQLERRM(-54): ORA-00054: resource busy and acquire with NOWAIT specified

PL/SQL procedure successfully completed.

Использование функции RAISE_APPLICATION JERROR

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

RAISE_APPLICATION_ERROR( номер_ошибки, сообщение_об_ошибке,

[сохранение_ошибки]);

где номер_ошибки - это параметр, лежащий в диапазоне от -20000 до -20999, сообщеиие_об_огимбке — текст, соответствующий данной ошибке, а сохранение_ошибки — логическое значение. Длина параметра сообщение_об_ошибке не должна превышать 512 символов. Логический параметр сохранение_ошибки необязателен. Если он установлен в TRUE, новая ошибка пополнит список ранее произошедших ошибок (при наличии этого списка), в случае же FALSE новая ошибка заместит текущий список ошибок. Для примера рассмотрим процедуру, которая перед регистрацией нового студента проверяет, имеется ли для него свободное место в учебной группе.

/* Регистрирует студента, указанного параметром p_StudentID, в группе, идентифицируемой параметрами p_Department и p_Course. */

CREATE OR REPLACE PROCEDURE Register (

p_StudentID IN students. id%TYPE,

p_Department IN classes.department%TYPE,

p_Course IN classes.course%TYPE$ AS

v_CurrentStudents classes. current_students%TYPE;

v_MaxStudents classes. max_students%TYPE;

v_NumCredlts classes.num_credits%TYPE;

v_Count NUMBER;

BEGIN

/* Определим текущее число зарегистрированных студентов и

максимально допустимое число студентов в группе. */

BEGIN

SELECT current_students, max_students, num_credits

INTO v_CurrentStudents, v_MaxStudents, v_NumCredits

FROM classes

WHERE course = p_Course

AND department = p_Department;

/* Выясним, есть ли в группе свободное место для нового студента. */

IF v_CurrentStudents + 1 > v_MaxStudents THEN

RAISE_APPLICATION_ERROR(-20000, 'Can' 't add more students to ' | I

p_Department || ' ' || p_Course);

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

/* Указанная группа не существует */

RAISE_APPLICATION_ERROR(-20001, p_Department || ' ' II

p_Course || ' doesrT't exist');

END;

/* Проверим, что студент еще не зарегистрирован */

SELECT COUNT(*)

INTO v_Count

FROM registered_students

WHERE student.id = p_StudentID

AND department = p_Department

AND course = p_Course;

IF v_count = 1 THEN

RAISE APPLICATION ERROR (-20002,

'Student ' || p_StudentID 1 1 ' is already registered for ' ||

p_Departraent I I ' ' II p_Course);

END IF;

/* В группе есть место, и студент еще в нее не записан. Обновим

необходимые таблицы. */

INSERT INTO registered_students (student_id, department, course)

VALUES (p_StudentID, p_Department, p_Course);

UPDATE students

SET current_credits = current_credits + v_NumCredits

WHERE ID = p_StudentID;

UPDATE classes

SET current_students = current_students + 1

WHERE course = p_Course

AND department = p_Department;

END Register;

В процедуре Register функция RAISE_APPLICATION_ERROR используется в трех случаях. Сначала процедура определяет текущее число студентов, зарегистрированных в группе, при помощи первого оператора SELECT...INTO. Если этот оператор возвращает NO_DATA_FOUND, то управление программой передается обработчику исключительной ситуации, и RAISE_APPLICATION_ERROR применяется для уведомления пользователя о том, что группа не существует. Если же группа существует, процедура проверяет, имеется ли свободное место для нового студента. Если свободного места нет, вновь применяется функция RAISE_APPLICATION_ERROR, чтобы сообщить пользователю об отсутствии свободного места. И, наконец, если свободное место есть, проверяется, не зарегистрирован ли студент в группе, это делается с помощью второго оператора SELECT ... INTO. Если все три проверки проходят успешно, студент вносится в список группы путем обновления таблиц registered_students, students и classes. Следующий сеанс SQL*Plus демонстрирует поведение процедуры Register и инициируемые ошибки. Предполагается, что таблицы находятся в своем исходном состоянии (созданы сценарием tables, sql).

-- Примеры ошибок ORA-2001 и ORA-2002

>exec Register(10000, ' C S ' , 999);

BEGIN Register(10000, ' O S ' , 999); END;

ERROR at line 1:

ORA-20001: CS 999 doesn't exist

ORA-06512: at "DEMO.REGISTER", line 28

ORA-06512: at line 1

>exec Register(10000, 'CS', 102);

BEGIN Register(10000, 'CS', 102); END;

ERROR at line 1:

ORA-20002: Student 10000 is already registered for CS 102

ORA-06512: at "DEMO. REGISTER", line 40

ORA-06512: at line 1

> -- Зарегистрируем двух студентов в MUS 410, что породит ORA-2003

> exec Register(10002, 'MUS', 410);

PL/SQL procedure successfully completed.

> exec Register(10005, 'MUS', 410);

BEGIN Register(10005, 'MUS', 410); END;

ERROR at line 1:

ORA-20000: Can't add more students to MUS 410

ORA-06512: at "DEMO. REGISTER", line 23

ORA-06512: at line 1

Сравним эти выходные данные со следующим примером анонимного блока, который порождает исключение NO_DATA_FOUND:

> BEGIN

2 RAISE NO_DATA_FOUND;

3 END;

BEGIN

***

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at line 2

Формат выходных данных одинаков — номер ошибки Oracle и связанный с ней текст. Отметим, что в обоих случаях выдается сообщение ORA-6512, указывающее строку, которая вызвала ошибку. Поэтому RAISE_APPLICATION_ERROR может использоваться для возврата условий ошибки пользователю в виде, согласованном с другими ошибками Oracle. Это очень полезно, так как не требуется никакой специальной обработки определенных пользователем ошибок по сравнению со стандартными ошибками.

Распространение исключительных ситуаций

Исключительные ситуации могут возникать в разделе объявлений, в выполняемом разделе или в разделе исключительных ситуаций блока PL/SQL. Выше было показано, что происходит в том случае, когда исключительная ситуация инициируется в выполняемом разделе блока и при этом имеется соответствующий обработчик. Но что произойдет, если обработчик отсутствует или исключительная ситуация возникает в другом разделе? Для ответа на этот вопрос следует рассмотреть процесс, называемый передачей (распространением) исключительных ситуаций (exception propagation).

Исключительные ситуации, возникающие в выполняемом разделе

Если исключительная ситуация возникает в выполняемом разделе блока PL/SQL, то для определения обработчика, который должен быть вызван, используется следующий алгоритм:

1. Если в текущем блоке имеется обработчик данной исключительной

ситуации, он выполняется, блок успешно завершается и управление

программой передается вышестоящему блоку.

2. Если обработчик отсутствует, исключительная ситуация передается

в вышестоящий блок и инициируется там. После этого в вышестоя-

щем блоке выполняется шаг 1. Если вышестоящего блока не сущест-

вует, исключительная ситуация будет передана вызывающей среде,

такой как SQL*Plus.

Прежде всего дадим определение вышестоящему, или охватывающему, блоку (enclosing block). Блок может быть частью другого блока, т.е. внешний блок может включать в свой состав внутренний блок. Рассмотрим пример:

DECLARE

-- Начало внешнего блока.

BEGIN

DECLARE

-- Начало внутреннего блока 1, который встроен во внешний блок.

BEGIN

END;

BEGIN

-- Начало внутреннего блока 2, который также встроен во

-- внешний блок. Обратите внимание, что в этом блоке

-- отсутствует раздел объявлений.

END;

-- Конец внешнего блока.

END;

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

BEGIN

-- Начало внешнего блока.

-- Вызов процедуры. Внешний блок охватывает эту процедуру.

(...);

EXCEPTION

WHEN OTHERS THEN

-- Любое исключение, порожденное F, будет перехватываться здесь.

END;

Если процедура F порождает исключительную ситуацию, которая не обрабатывается, то эта исключительная ситуация передается во внешнийблок, охватывающий данную процедуру.

Область действия исключительной ситуации

Область действия исключительной ситуации аналогична области действия переменной. Если исключительная ситуация, определенная пользователем, будет передана из блока и окажется вне области своегодействия, ссылаться на нее по имени станет невозможно. Рассмотрим пример:

> BEGIN

2 DECLARE

3 eJJserDefinedException EXCEPTION;

4 BEGIN

5 RAISE e_UserDefinedException;

6 END;

7 EXCEPTION

8 /* e_UserDefinedException вне области своего действия;

9 это исключение можно обработать только при помощи OTHERS. */

10 WHEN OTHERS THEN

11 /* Инициируем исключение повторно, оно будет

12 передано вызывающей среде. */

13 RAISE;

14 END;

BEGIN

***

ERROR at line 1:

ORA-06510: PL/SQL: unhandled user-defined exception

ORA-06512: at line 13

Если сообщение об ошибке, определяемой пользователем, нужно передать из блока, рекомендуется описывать исключительную ситуацию в модуле так, чтобы она была видима вне этого блока, или следует воспользоваться функцией RAISE_APPLICATION_ERROR (см. ниже). Если создать модуль Globals и описать в нем e_UserDefinedException, то эта исключительная ситуация будет видима и во внешнем блоке. Например:

CREATE OR REPLACE PACKAGE Globals AS

/* В этом модуле содержатся глобальные объявления.

К объявленным здесь объектам можно обращаться из всех других блоков

или процедур с помощью квалифицированных ссылок. Заметьте, что у этого

модуля нет тела. */

/* Исключительная ситуация, определяемая пользователем */

eJJserDefinedException EXCEPTION;

END Globals;

С учетом созданного модуля Globals можно переписать рассмотренный выше фрагмент программы следующим образом:

BEGIN

BEGIN

RAISE Globals.eJJserDefinedException;

END;

EXCEPTION

/* Поскольку e_UserDefinedException видима здесь, можно

обработать ее явным образом. */

WHEN Globals.eJJserDefinedException THEN

/* Повторно инициируем исключение, которое будет

передано вызывающей среде. */

RAISE;

END;

Помимо исключительных ситуаций, в модуле Globals можно объявить общие таблицы, переменные и типы PL/SQL.

Отслеживание всех исключительных ситуаций

Не следует допускать возникновения в программах исключительных ситуаций, которые не обрабатываются. Можно воспользоваться обработчиком OTHERS, создав его на самом верхнем уровне программы. Например, если этот обработчик будет регистрировать факт и время возникновения каждой ошибки, то ни одна ошибка не будет оставлена без внимания:

DECLARE

vJrrrorNumber NUMBER; - Переменная для хранения кода ошибки

vJErrorText VARCHAR2(200); - Переменная для хранения текста

-- сообщения об ошибке.

BEGIN

/* Обычная обработка информации */

EXCEPTION

WHEN OTHERS THEN

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

блок был успешно завершен. ｫ/

vJ^rrorNumber := SQLCODE;

vJ^rrorText := SUBSTR(SQLERRM, 1, 200);

INSERT INTO log_table (code, message, info) VALUES

(vJErrorNumber, vJErrorText, 'Oracle error occurred at' 1 1

TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MIiSS1));

END;

4. Порядок выполнения работы

Изучить предлагаемый теоретический материал.

Выполнить следующие задания:

1. Создайте процедуру, которая бы обновляла номера регионов для служащих отделов.

  а) Напишите обработчик исключений, который будет выдавать сообщение о том, что указанный регион не существует

  б) Напишите обработчик исключений, который бы выдавал пользователю сообщение о том, что для указанного региона уже есть отдел с таким названием

  в) Напишите обработчик исключений, который выдавал бы пользователю сообщение о том, что указанный номер отдела не существует (используйте атрибут SQL%NOTFOUND  и возбудите исключение вручную).

2. Напишите процедуру для вывода на фамилии и названия отдела для служащих, чья заработная плата лежит в диапазоне плюс-минус 100$ от введенного значения.

           а) Напишите обработчик исключений, который будет выдавать сообщение о том, что служащих с такой зарплатой нет.

           б) Напишите обработчик исключений, который будет выдавать сообщение о том, что служащих с такой зарплатой несколько. Сообщение должно указывать, сколько сотрудников попадает в этот диапазон зарплат.

5. Содержание отчета

В отчете следует указать:

1. Цель работы

2. Введение

3. Программно-аппаратные средства, используемые при выполнении работы.

4. Основную часть (описание самой работы), выполненную согласно требованиям к результатам выполнения лабораторного практикума.

5. Заключение (выводы)

6. Список используемой литературы

Литература:

1. Баженова И.Ю. Oracle 8/8i. Уроки программирования. - М.: "ДИАЛОГ-МИФИ",2000.

2. Дейт К. Дж. Введение в системы баз данных. 7-е изд. - М.-СПб-К:"Диалектика", 2001.

3. Скотт Урман. "ORACLE 8. Программирование на языке PL/SQL".
   (М.: "Лори", 1999, 608 стр.); [оригинал: Scott Urman "Oracle8 PL/SQL Programming" Osborne/McGraw-Hill, 1997]

4. В.А.Кравчук, Ю.П.Бегус, В.А.Габзовский. "Введение в Oracle PL/SQL".
   (Киев, Издательство "ДиаСофт", 1998, - 400 с.)