Приведем правила работы с именованными точками сохранения:
область видимости точки сохранения не ограничивается блоком 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
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 после каждой операции с данными пишут сведения о том, кто ее выполнил. Проблема заключается в том, что и изменения самих данных и добавление строк в таблицу журнала обычно выполняются в рамках одной транзакции. При отмене этой транзакции вместе с отменой изменений данных будет отменено и добавление строк в таблицу журнала изменений, что может противоречить бизнес-правилам ведения журнала. Эти правила часто предусматривают, что в журнале должны фиксироваться все действия, включая отмененные.
Обычно добавление строк в таблицу журнал производят путем вызова процедуры, которой передают описание совершаемых действий. Если блок этой процедуры объявить как автономную транзакцию, то сведения в журнал изменений данных попадут независимо от фиксации или отмены основной транзакции.
CREATE TABLE accounts (id INTEGER PRIMARY KEY, status varchar2(10));
INSERT INTO accounts VALUES(133,'active');
CREATE TABLE change_log
(change_date DATE, message VARCHAR2(4000),
username VARCHAR2(100), ip_address VARCHAR2(100));
SQL> CREATE OR REPLACE PROCEDURE log(p_message IN VARCHAR2) IS
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 BEGIN
4 INSERT INTO change_log
5 VALUES(sysdate,p_message,user,sys_context('USERENV','IP_ADDRESS'));
6 COMMIT;
7 END;
8 /
Procedure created.
SQL> SELECT * FROM change_log;
no rows selected
SQL> DECLARE
2 l_account_id INTEGER := 133;
3 BEGIN
4 log('UPDATE accounts,id='||l_account_id||',set status=closed');
5 UPDATE accounts SET status='closed' WHERE id=l_account_id;
6 ROLLBACK;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM change_log;
CHANGE_DATE MESSAGE USERNAME IP_ADDRESS
16.01.2015 UPDATE accounts,id=133,set status=closed U1 192.168.0.8
Так как процедура log с помощью директивы компилятору PRAGMA AUTONOMOUS_TRANSACTION объявлена автономной транзакцией, то, несмотря на вызов команды ROLLBACK для отмены основной транзакции, в журнале запись о попытке закрытия лицевого счета не пропала.
Следует отметить, что автономные транзакции изначально использовались только внутренними механизмами сервера и были недоступны программистам. Механизмами сервера автономные транзакции используются до сих порв основном для регистрации сообщений об ошибках, сохранения статистических данных о нагрузке и т. п. Настоятельно рекомендуется в своих проектах использовать их только для решения подобных же служебных задач с низкой степенью критичности возможных допущенных при программировании ошибок. Для бизнес-логики следует продумывать схему ее реализации обычными (не автономными) транзакциями.
Изменение данных в функциях PL/SQL, вызываемых в SQL
В коде функций PL/SQL, вызываемых в предложениях SQL, нельзя использовать DML-команды INSERT, UPDATE, DELETE. Рассмотрим следующий пример:
CREATE TABLE tab5 (at5 INTEGER)
CREATE TABLE tab6 (at6 INTEGER)
INSERT INTO tab5 VALUES(5)
SQL> CREATE OR REPLACE FUNCTION function1 RETURN INTEGER AS
2 BEGIN
3 INSERT INTO tab6 VALUES(123);
4 END;
5 /
Function created.
SQL> SELECT at5,function1 FROM tab5;
SELECT at5,function1 FROM tab5
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "U1.FUNCTION1", line 3
Если функцию объявить автономной транзакцией, то это ограничение снимается.
SQL> CREATE OR REPLACE FUNCTION function1 RETURN INTEGER AS
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 BEGIN
4 INSERT INTO tab6 VALUES(123);
5 COMMIT;
6 RETURN 7;
7 END;
8 /
Function created.
SQL> SELECT at5,function1 FROM tab5;
AT5 FUNCTION1
5 7
SQL> SELECT * FROM tab6;
AT1
123
В частности, с помощью автономных транзакций можно реализовать аудит обращений к некоторой таблице с особо конфиденциальной информацией (предполагаем, что у таблицы есть целочисленный первичный ключ):