Деление на ноль oracle обработка исключений. Особенности обработки ошибок сервера базы данных Oracle. Группы ошибок Oracle

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

Исключения

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

Часть блока, предназначенная для обработки исключений, – это идеальное место для выдачи информативных сообщений об ошибках и выполнения очистки (cleanup), позволяющей избавиться от всего, что могло бы в дальнейшем вызвать путаницу или проблемы. Если исключение было возбуждено в ходе выполнения процедуры вставляющей строки в таблицу, то типичная процедура очистки может включать в себя оператор ROLLBACK.

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

Системные исключения

Вы уже знакомы с исключением ZERO_DIVIDE, предопределенным в PL/SQL. Существует довольно много других системных исключений, которые распознаются и возбуждаются PL/SQL или Oracle. В таблице 1 приведен более полный список системных исключений.

В PL/SQL можно выдавать пользователям информацию об ошибке двумя способами. Первый способ – использовать команду SQLCODE, которая возвращает код ошибки. Этот код представляет собой отрицательное число, обычно равное номеру ошибки ORA, которая выводится при завершении приложения, если исключение осталось необработанным. Второй способ – возвращать текстовое сообщение, описывающее ошибку. Неудивительно, что соответствующая команда называется SQLERRM. В обработчике исключения можно использовать как SQLCODE, так и SQLERRM. Замечание: не у всех системных исключений есть имена.

Таблица 1 . Системные исключения

Теперь вернемся к самому первому примеру этой главы и используем в нем SQLCODE и SQLERRM. Ниже приведен исходный код примера и результаты его запуска (рис.1).

Num_a NUMBER:= 6;

Num_b NUMBER;

Num_b:= 0;

Num_a:= Num_a / Num_b;

Num_b:= 7;

dbms_output.put_line(" Value of Num_b "|| Num_b);

EXCEPTION

WHEN ZERO_DIVIDE THEN

err_num NUMBER:= SQLCODE;

err_msg VARCHAR2(512) := SQLERRM;

dbms_output.put_line("ORA Error Number " || err_num);

dbms_output.put_line("ORA Error message " || err_msg);

dbms_output.put_line("Value of Num_a " || Num_a);

dbms_output.put_line("Value of Num_b " || Num_b);

SQL> set serveroutput on

SQL> DECLARE

2 num_a NUMBER:= 6;

3 num_b NUMBER;

4 BEGIN

5 num_b:= 0;

6 num_a:= num_a / num_b;

7 num_b:= 7;

8 dbms_output.put_line(" Value of num_b "|| num_b);

9 EXCEPTION

10 WHEN ZERO_DIVIDE

11 THEN

13 err_num NUMBER:= SQLCODE;

14 err_msg VARCHAR2(512) := SQLERRM;

15 BEGIN

16 dbms_output.put_line("ORA Error Number "|| err_num);

17 dbms_output.put_line("ORA Error message " || err_msg);

18 dbms_output.put_line("Value of num_a " || num_a);

19 dbms_output.put_line("Value of num_b " || num_b);

20 END;

21 END;

ORA Error Number -1476

ORA Error Message ORA-01476: divisor is equal to zero

Value of num_а 6

Value of num_b 0

PL/SQL procedure successfully completed.

Рис. 1. Использование SQLCODE и SQLERRM при обработке системных исключений

Рассмотрим конструкцию по отлову исключений(ошибок) в Oracle

when others then - отлавливает все ошибки, которые могут возникнуть, если до нее не отловили ранее

set serveroutput on;

declare
a INTEGER:= 1;
b INTEGER:= 0;
c CHAR(1) := "c";
begin
--Не вызовет ошибки
DBMS_OUTPUT.PUT_LINE(a / a);
--Вызовет деление на 0
--DBMS_OUTPUT.PUT_LINE(a / b);
--Вызовет все остальные ошибки
--DBMS_OUTPUT.PUT_LINE(a / c);
exception
when zero_divide then
DBMS_OUTPUT.PUT_LINE("zero_divide!");
when others then
DBMS_OUTPUT.PUT_LINE("when others then!");
end;

Перевели табличку с описанием ошибок: http://oracleplsql.ru/named-system-exceptions.html

/*
DUP_VAL_ON_INDEX Вы пытались выполнить операторы insert или update поля, изменение значения которого нарушит ограничение уникальности поля.
TIMEOUT_ON_RESOURCE Возбуждается при возникновении таймаута, когда ORACLE ожидает ресурса.
TRANSACTION_BACKED_OUT Откат удаленной части транзакции.
INVALID_CURSOR Вы пытаетесь сослаться на курсор, который еще не существует. Это могло произойти потому, что вы выполняете выборку (fetch) курсора, который был закрыт (close) или не был открыт (open).
NOT_LOGGED_ON Вы пытаетесь выполнить вызов в Oracle, не подключившись к Oracle.
LOGIN_DENIED Вы пытаетесь войти в Oracle с неверными имя пользователя / пароль.
NO_DATA_FOUND Вы пробовали один из следующих вариантов:
1. Вы выполнили SELECT INTO и запрос не вернул ни одной строки.
2. Вы ссылаетесь на неинициализированную строку в таблице.
3. Вы читаете после конца файла пакета UTL_FILE.
TOO_MANY_ROWS Вы пытались выполнить SELECT INTO и запрос вернул более одной строки.
ZERO_DIVIDE Вы пытались поделить число на ноль.
INVALID_NUMBER Вы пытаетесь выполнить оператор SQL который пытается преобразовать строку в число.
STORAGE_ERROR Вы исчерпали доступную память или память повреждена.
PROGRAM_ERROR Это общее сообщение Обратитесь в службу поддержки Oracle, возбуждается по причине обнаружения внутренней ошибки.
VALUE_ERROR Вы пытались выполнить операцию и была ошибка преобразования, усечения, или ограничения числовых или символьных данных.
CURSOR_ALREADY_OPEN Вы попытались открыть курсор, который уже открыт.
*/

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

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

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

Если в исполняемой секции блока возбуждается исключение, управление передается секции обработки исключений (секции EXCEPTION). В том случае, если исключение будет успешно обработано, блок PL/SQL завершится без ошибок. Если же обработчика для этого исключения нет, выполнение блока PL/SQL прекратится в аварийном порядке.

Имеется три типа исключений:

Перехват исключений

Перехват исключений осуществляется в секции обработки исключений блока PL/SQL.

EXCEPTION

WHEN исключение_1 [ OR исключение_2 ...] THEN

операторы ;

WHEN исключение_3 [ OR исключение_4 ...] THEN

операторы ;

исключение – имя предопределенного исключения или исключения, описанного в декларативной секции

WHEN OTHERS – определяет действия по обработке всех исключений, обработка для которых не задана явно

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

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

Перехват предопределенных исключений сервера Oracle

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

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

v_sal emp.sal%TYPE;

SELECT sal INTO v_sal FROM emp WHERE LOWER(job)=LOWER(:v_job);

DBMS_OUTPUT.put_line("Salary of " || :v_job || " is " || TO_CHAR(v_sal));

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.put_line(:v_job || " is not a title of employees");

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.put_line(:v_job || " is a title of many employees");

WHEN OTHERS THEN

DBMS_OUTPUT.put_line("Other error occurred");

Теперь рассмотрим различные варианты, присваивая разные значения хост-переменной v_ job .

SQL> VARIABLE v_job VARCHAR2(20)

SQL> EXECUTE:v_job:= "President"

SQL> @d:\users\except

Salary of President is 5000

PL/SQL procedure successfully completed.

SQL> EXECUTE:v_job:= "Clerk"

PL/SQL procedure successfully completed.

SQL> @d:\users\except

Clerk is a title of many employees

PL/SQL procedure successfully completed.

SQL> EXECUTE:v_job:= "Engineer"

PL/SQL procedure successfully completed.

SQL> @d:\users\except

Engineer is not a title of employees

PL/SQL procedure successfully completed.

Перехват неопределенных исключений сервера Oracle

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

Это учебное пособие объясняет, как использовать встроенные исключительные ситуации в Oracle/PLSQL c синтаксисом и примерами.

Описание

Встроенные исключительные ситуации это исключительные ситуации, которые имеют определенные имена в PL/SQL. Они определены в стандартном пакете в PL/SQL и не могут быть определены программистом.
Oracle имеет стандартный набор встроенных исключительных ситуаций:

Синтаксис

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

Синтаксис для процедуры

CREATE PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
BEGIN
executable_sectionEXCEPTION
WHEN exception_name1 THEN

WHEN exception_name2 THEN

WHEN exception_name_n THEN

WHEN OTHERS THEN

END ;

Синтаксис для функции

CREATE FUNCTION function_name
[ (parameter [,parameter]) ]
RETURN return_datatype
IS | AS
BEGIN
executable_sectionEXCEPTION
WHEN exception_name1 THEN

WHEN exception_name2 THEN

WHEN exception_name_n THEN

WHEN OTHERS THEN

END ;

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

Oracle PL/SQL

CREATE OR REPLACE PROCEDURE add_new_supplier (supplier_id_in IN NUMBER, supplier_name_in IN VARCHAR2) IS BEGIN INSERT INTO suppliers (supplier_id, supplier_name) VALUES (supplier_id_in, supplier_name_in); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN raise_application_error (-20001,"You have tried to insert a duplicate supplier_id."); WHEN OTHERS THEN raise_application_error (-20002,"An error has occurred inserting a supplier."); END;

Exception Handling in PL/SQL

In any procedural language, it is critical to remember that the programs are a complete and separate entity from the database. Hence, whenever the program requests rows from the database, the programmer must make sure that the request completed successfully.

In relational databases, the database will always pass a special variable called SQLCODE back to any calling program. The SQLCODE returned from reach call from the program to the database is translated by Oracle into a named Boolean variable (See table below).

For example, if the database returns a SQLCODE=100, the PL/SQL variable NO_DATA_FOUND will be set to TRUE.

Without exception, all PL/SQL programs should be made to abort whenever an unexpected SQLCODE is returned by the Oracle database.

This can have a disastrous effect on the database, especially when the PL/SQL loads data into tables based upon false premises. To prevent this tragedy, Oracle provides a WHEN OTHERS variable, which is set to TRUE if any unexpected SQLCODE is returned from the Oracle database.

For example, consider the following code:

DECLARE
err_num NUMBER;
err_msg VARCHAR2(100);
BEGIN
...
EXCEPTION
...
WHEN OTHERS THEN
err_num:= SQLCODE;
err_msg:= SUBSTR(SQLERRM, 1, 100);
INSERT INTO errors VALUES (err_num, err_msg);
END;

Here we see that our exception handling has an EXCEPTIONS area testing WHEN OTHERS. If the WHEN OTHERS Boolean variable is TRUE, the PL/SQL code captures the SQLCODE and the associated error message (SQLERRM), and stores these values into a special Oracle errors table.

Oracle Exception Handling

Developers often flag error conditions and handle them using Oracle exception handling and the use of IF-THEN logic.

Oracle exception handling u sing IF-THEN logic to flag errors

The above example illustrates Oracle exception handling using the boolean variable bAidAmountOk to keep track of a condition throughout the processing of each student record.

This use of Oracle exception handling has an impact on performance. Oracle exception handling uses multiple instructions to test for the error condition. Each Oracle exception handling instruction requires CPU cycles to complete. A much better approach involves the use of Oracle exception handling to avoid wasting CPU cycles, as seen below:

Using Oracle exception handlers to improve performance.

In this example of Oracle exception handling, the xAID_AMOUNT_OK exception is explicitly raised inside the loop. This allows execution to skip the instructions that occur after the student"s GPA is checked, cutting down on the cpu used in the Oracle exception handling.

Oracle exception handling is highly performance efficient. When an Oracle exception is raised, all subsequent instructions within the block are bypassed so the exception can be handled by an Oracle exception handler. Oracle exception handling can be utilized to significantly boost performance.