Иван Задворьев - Язык PL/SQL

1

2

4

В SQL*Plus возникновение ошибок не влияет на статус транзакции, она остается активной и ее можно будет зафиксировать или отменить. Это же верно и для Quest SQL Navigator. Для других программ, выполняющих транзакции в Oracle, действия при возникновении ошибок могут отличаться. Например, для прикладного программного обеспечения (каких-нибудь бухгалтерских программ с GUI на C#, скриптов обсчета данных в базе на Python) программисты часто согласно требованиям бизнес-логики предусматривают отмену транзакции при возникновении первой же ошибки выполнения предложения SQL в ходе транзакции.

Точки сохранения для вызовов PL/SQL

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

Рассмотрим две ситуации

CREATE TABLE tab4 (a INTEGER);

Ситуация 1: необработанное исключение

Ситуация 2: обработанное исключение

– все в рамках одной транзакции

– первый блок (успешно)

SQL> BEGIN

2 INSERT INTO tab4 VALUES(1);

3 INSERT INTO tab4 VALUES(2);

4 END;

5 /

PL/SQL procedure successfully completed.

– второй блок (с ошибкой)

SQL> BEGIN

2 INSERT INTO tab4 VALUES(3);

3 INSERT INTO tab4 VALUES('abc');

4 END;

5 /

BEGIN

*

ERROR at line 1:

ORA-01722: invalid number

ORA-06512: at line 3

SQL> SELECT * FROM tab4;

A

–

1

2

– все в рамках одной транзакции

– первый блок (успешно)

SQL> BEGIN

2 INSERT INTO tab4 VALUES(1);

3 INSERT INTO tab4 VALUES(2);

4 END;

5 /

PL/SQL procedure successfully completed.

– второй блок (успешно)

SQL> BEGIN

2 INSERT INTO tab4 VALUES(3);

3 INSERT INTO tab4 VALUES('abc');

4 EXCEPTION

5 WHEN OTHERS THEN NULL;

6 END;

7 /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM tab4;

A

–

1

2

3

В первой ситуации (есть блок с необработанным исключением):

для второго блока PL/SQL при ошибке выполнения команды INSERT со значением abc произошла отмена до неявной точки сохранения перед INSERT и изменения, внесенные этим предложением, были отменены;

так как во втором блоке PL/SQL нет раздела обработки исключений, то с ошибкой завершился весь блок и произошла отмена к неявно установленной точке сохранения перед ним (в результате была отменено успешное добавление тройки на второй строке кода этого блока);

первый блок PL/SQL выполнился без ошибок; последовавшие потом ошибки второго блока, как и должно быть, никак на внесенные его командами изменения данных не повлияли и обе добавленные в ходе обработки первого блока строки есть в таблице (единица и двойка).

Во второй ситуации (исключение обработано):

точно так же для второго блока PL/SQL при ошибке выполнения предложения INSERT со значением abc произошла отмена к неявной точке сохранения перед INSERT и изменения, внесенные этим предложением, были отменены;

так как во втором блоке есть раздел обработки исключений с OTHERS-обработчиком, то вызов второго блока завершился успешно, без передачи ошибки вызывающей среде, поэтому добавление тройки не отменялось;

после выполнения обоих блоков в таблице tab4 будет три строки – две от первого блока и одна от второго.

Использование именованных точек сохранения

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

Приведем правила работы с именованными точками сохранения:

область видимости точки сохранения не ограничивается блоком PL/SQL, в котором она установлена (точки сохранения «живут» на уровне всей транзакции, в частности, вообще можно установить точку сохранения в PL/SQL, а выполнить отмену до нее в SQL и наоборот);

если в ходе транзакции имя точки сохранения используется повторно, эта точка сохранения просто передвигается вперед по транзакции;

после отмены до точки сохранения отменяются изменения данных, сделанные после установки этой точки, также снимаются наложенные после ее установки блокировки (блокировки и изменения данных, сделанные транзакцией до точки сохранения, остаются);

транзакция после отмены до точки сохранения остается активной, ее можно возобновить (начать выполнять новые предложения SQL) и потом зафиксировать или отменить;

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

В качестве примера работы с точками сохранения рассмотрим программу, реализующую следующую бизнес-логику.

Имеется таблица с тремя необработанными заданиями и две пустые таблицы для сохранения результатов обработки заданий:

CREATE TABLE jobs (id INTEGER,state VARCHAR2(10),a INTEGER,b INTEGER);

INSERT INTO jobs VALUES (1,'created',8,4);

INSERT INTO jobs VALUES (2,'created',4,0);

INSERT INTO jobs VALUES (3,'created',15,5);

CREATE TABLE jobs_mult_res (id INTEGER, result NUMBER);

CREATE TABLE jobs_div_res (id INTEGER, result NUMBER);

Обработка задания заключается в умножении и делении двух чисел из столбцов a и b таблицы jobs с сохранением результатов в двух таблицах. После успешного выполнения и умножения и деления задание должно менять состояние на processed. Видно, что попытка деления для задания из второй строки jobs должна завершиться ошибкой деления на ноль (4/0). Ошибочные задания должны получать статус error. Для ошибочных заданий не должно быть строк ни в одной из двух таблиц результатов.

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

SQL> BEGIN

2 FOR job_rec IN (SELECT * FROM jobs WHERE state='created') LOOP

3 BEGIN

4 SAVEPOINT sp_job;

5 INSERT INTO jobs_mult_res VALUES(job_rec.id,job_rec.a*job_rec.b);

6 DBMS_OUTPUT.PUT_LINE('Insert mult for job '||job_rec.id||' OK');

7 INSERT INTO jobs_div_res VALUES(job_rec.id,job_rec.a/job_rec.b);

8 DBMS_OUTPUT.PUT_LINE('Insert div for job '||job_rec.id||' OK');

9 UPDATE jobs SET state='processed' WHERE id=job_rec.id;

10 EXCEPTION

11 WHEN OTHERS THEN

12 ROLLBACK TO sp_job;

13 UPDATE jobs SET state='error' WHERE id=job_rec.id;

14 END;

15 END LOOP;

16 END;

17 /

Insert mult for job 1 .. OK

Insert div for job 1 .. OK