Иван Задворьев - Язык PL/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

Insert mult for job 2 .. OK

Insert mult for job 3 .. OK

Insert div for job 3 .. OK

PL/SQL procedure successfully completed.

SQL> SELECT j.*,jmr.result mult,jdr.result div FROM jobs j

2 LEFT OUTER JOIN jobs_mult_res jmr ON j.id=jmr.id

3 LEFT OUTER JOIN jobs_div_res jdr ON j.id=jdr.id

4 ORDER BY j.id;

ID STATE A B MULT DIV

– – – – – –

1 processed 8 4 32 2

2 error 4 0

3 processed 15 5 75 3

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

Автономные транзакции

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

ведение журналов изменений данных;

изменение данных в функциях PL/SQL, вызываемых в SQL.

Для объявления блока PL/SQL автономной транзакцией достаточно указать директиву компилятору PRAGMA AUTONOMOUS_TRANSACTION.

Ведение журнала изменений данных

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