Зона действия и распространение

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

 

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

 

(1) внешняя программа прекращает работу

(2) произойдет откат незакрытых транзакций

 

Обработанная исключительная ситуация по цепочке «наверх» не передается. Если «транзитную» ситуацию нужно «поймать» (чтобы выдать на экран осмысленное сообщение), ее потребуется активизировать снова с помощью RAISE.

 

Пример:

 

DECLARE

PROCEDURE delete_dept (indept IN NUMBER)

IS

           still_have_employees EXCEPTION;

           PRAGMA EXCEPTION_INIT (still_have_employees, -2292);

BEGIN

           DELETE FROM dept WHERE deptno = indept;

EXCEPTION

           WHEN still_have_employees THEN

                              DBMS_OUTPUT.PUT_LINE ('Please delete employees in dept ' || indept || ' first');

              RAISE;

END;

BEGIN

COMMIT;

delete_dept (40);

BEGIN delete_dept (30); EXCEPTION WHEN OTHERS THEN NULL; END;

delete_dept (20);

delete_dept (10);

END;

/

 

Упражнение. Выполнить пример выше. Посмотреть список отделов. Убрать из текста удаление отделов 20 и 10 и выполнить пример снова. Посмотреть список отделов. Выполнить ROLLBACK. Посмотреть список отделов и объяснить результат.

 

 

Использование функций SQLCODE и SQLERRM

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

 

CREATE TABLE err_test (

 name VARCHAR2 (5)

  CONSTRAINT big_first_letter CHECK (SUBSTR (name, 1, 1) BETWEEN 'A' AND 'Z'),

 quantity NUMBER

  CONSTRAINT no_small_numbers CHECK (quantity > 1024));

 

DECLARE oper VARCHAR2 (100):= 'ONE';

BEGIN

INSERT INTO err_test VALUES ('Athen', 2000);

 

BEGIN

INSERT INTO err_test VALUES ('Athen', 200);

EXCEPTION WHEN OTHERS THEN oper:= oper || ', TWO';

END;

 

BEGIN

INSERT INTO err_test VALUES ('athen', 2000);

EXCEPTION WHEN OTHERS THEN oper:= oper || ', THREE';

END;

 

BEGIN

INSERT INTO err_test VALUES ('Athena', 2000);

EXCEPTION WHEN OTHERS THEN oper:= oper || ', FOUR'; RAISE;

END;

 

EXCEPTION WHEN OTHERS THEN

IF SQLCODE = -2290 AND SQLERRM LIKE '%NO_SMALL_NUMBERS%'

THEN

   DBMS_OUTPUT.PUT_LINE('Quantity is too small while ' || oper);

ELSIF SQLCODE = -2290 AND SQLERRM LIKE '%BIG_FIRST_LETTER%'

     THEN

        DBMS_OUTPUT.PUT_LINE

         ('Name should begin with capital letter while ' || oper);

ELSE

                           DBMS_OUTPUT.PUT_LINE ('Exception not hooked, '

                           || ' SQLCODE = ' || TO_CHAR (SQLCODE));

                           DBMS_OUTPUT.PUT_LINE (SQLERRM);

           DBMS_OUTPUT.PUT_LINE ('Generated by ' || oper);

END IF;

END;

/

 

Упражнение. Прогнать пример.  Снять скобки BEGIN... END последовательно с операций вставки THREE и TWO и прогнать пример два раза. Переписать пример по-другому, воспользовавшись именами ситуаций, прибегнув к PRAGMA EXCEPTION_INIT.

 

Для того, чтобы сформировать стек цепочки сообщений исключительных ситуаций, можно воспользоваться процедурой FORMAT_ERROR_STACK системного пакета DBMS_UTILITY. Прочитать содержимое стека можно с помощью процедуры FORMAT_CALL_STACK из того же пакета.

 

 

Хранимые процедуры и функции

Именованными единицами программного кода в PL/SQL, допускающими хранение в базе, могут быть:

 

n процедуры

n функции

n триггерные процедуры («триггеры»)

n пакеты

n типы объектов

 

Первые две категории под общим названием «подпрограммы» рассматриваются в этом разделе.

 

 

Общий синтаксис

Синтаксис создания процедуры в PL/SQL в версии Oracle 8.1:

 

CREATE [OR REPLACE] PROCEDURE имя _процедуры

           [(список _параметров)]

           [AUTHID {CURRENT_USER | DEFINER}]

{IS | AS}

           [ раздел объявлений ]

BEGIN

           раздел кода

[EXCEPTION

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

END [ имя_процедуры ];

 

 

Пример вызова процедуры в блоке PL/SQL:

 

apply_discounts(new_company_id, 0.15); -- скидка 15%

 

Пример самостоятельного вызова процедуры в SQL*Plus:

 

EXEC apply_discounts(new_company_id, 0.15)

 

Синтаксис создания функции в PL/SQL версии Oracle 8.1:

 

CREATE [OR REPLACE] FUNCTION имя _функции

           [(список_параметров)]

           RETURN тип_результата

           [AUTHID {CURRENT_USER | DEFINER}]

           [DETERMINISTIC]

           [PARALLEL_ENABLE]

           [PIPELINED]

           [AGGREGATE]

{IS | AS}

           [ раздел объявлений ]

BEGIN

           раздел кода, включающий предложение RETURN

[EXCEPTION

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

END [ имя_функции ];

 

(В заголовке функции могут указываться кроме указанных другие, более специальные конструкции, о которых см. в документации).

 

Функции на PL/SQL могут использоваться как элемент выражений в SQL и PL/SQL. Примеры:

 

n присваивание:

sales99:= tot_sales(1999, ‘C’);

n выставление значения по умолчанию:

DECLARE

Sales99 NUMBER DEFAULT tot_sales(1999, ‘C’);

BEGIN

…

n в выражении:

IF tot_sales(1999, ‘C’) > 10000

THEN

…

n в предложении SQL:

SELECT first_name, surname FROM sellers

WHERE tot_sales(1999, ‘C’) > 10000;

n в качестве аргумента в списке параметров:

apply_discount(company_id, max_discount(SYSDATE));

 

Указания компилятору в предложениях создания подпрограмм и функций см. ниже.

 

 

Параметры

список_параметров выше – перечисление параметров через запятую. Синтаксис объявления параметра в списке:

 

имя_параметра [ режим_использования ] [NOCOPY] тип_ параметра [{:= | DEFAULT} выражение ]

 

 

Тип параметра

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

 

Следующие определения формальных параметров недопустимы:

 

name                 VARCHAR (20)

id                        NUMBER (5, 2)

moment            TIMESTAMP (4)

timeint              INTERVAL YEAR (2) TO MONTH

 

Следующие определения формальных параметров возможны:

 

id                        PLS_INTEGER

en                       emp.ename%TYPE

emp                   emp%ROWTYPE

name                 VARCHAR

id                        NUMBER

tm                      TIMESTAMP

tint                     INTERVAL YEAR TO MONTH

timeint              YMINTERVAL_UNCONSTRAINED

 

Фактическая длина для обработки в подпрограмме, если это необходимо, определиться при ее вызове.