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

Обработка транзакций - главная обязанность программного механизма любой базы данных, и Oracle здесь не является исключением. Чтобы понять суть дела, давайте определим, что составляет транзакцию. В Oracle транзакция - это логическая единица работы, выполняемая между точками commit/rollback (фиксация/откат) одним или более SQL- (или PL/SQL-) предложениями. Это означает, что в программе, которая явно содержит предложение commit или rollback, все измененные в текущей транзакции данные будут или зафиксированы (commit) в базе, или будет произведен откат (rollback), и все изменения будут отменены.

Это может представить проблему при обработке ошибок, как проиллюстрировано ниже:

 CREATE OR REPLACE PROCEDURE p_update_employee  
 IS  
 BEGIN  
  -- ...  
  -- логика приложения  
  --  
  COMMIT;  
  --  
 EXCEPTION  
 WHEN OTHERS THEN  
  ROLLBACK;  
  INSERT INTO error_logs VALUES (20101, sqlerrm);  
  COMMIT;  
 END;  
 /  

В процедуре p_update_employee обращение к фразе WHEN OTHERS производится по любой ошибке Оracle. Цель - записать сообщение об ошибке в таблицу приложения, названную здесь error_logs. Поскольку мы хотим выделить ошибки, обнаруженные логикой приложения с указанием породивших их проблем, нам надо выполнить явный откат до задействования предложения INSERT, чтобы только затем последующий commit зафиксирует сообщение об ошибке. Такая реализация представляется несколько неуклюжей, поскольку программа реально включает в себя два процессных трека - логику приложения и регистрацию ошибок.
Автономная транзакция может разрешить эту проблему.

Автономная транзакция отделяется от начальной транзакции, создавая, тем самым, возможность для прикладной программы независимо фиксировать/откатывать (commit/rollback) свои транзакции.

Автономные транзакции должны быть объявлены с использованием фразы
PRAGMA AUTONOMOUS_TRANSACTION в PL/SQL-программе. Вот как выглядит декларация подпрограммы (routine), обрабатывающей ошибку:

 CREATE OR REPLACE PROCEDURE p_error(  
   i_error IN NUMBER,  
   i_text IN VARCHAR2)  
 IS  
  PRAGMA AUTONOMOUS_TRANSACTION;  
 BEGIN  
  INSERT INTO error_logs (error_code, error_text)   
   VALUES (i_error, i_text);  
  --  
  COMMIT;  
  --  
 END;  
 /  

Теперь процедура p_error работает независимо от вызываемой во время выполнения этой подпрограммы.
Вот пример, как мы можем "переделать" ("retool") подпрограмму p_update_employee:

 CREATE OR REPLACE PROCEDURE p_update_employee  
 IS  
  lx_test EXCEPTION;  
 BEGIN  
  -- ...  
  -- Логика приложения  
  --  
  RAISE lx_test;  
  --  
  COMMIT;  
  --  
 EXCEPTION  
 WHEN OTHERS THEN  
  ROLLBACK;  
  p_error(20101, sqlerrm);  
 END;  
 /  

Заметим, что в секции EXCEPTION фраза WHEN OTHERS вызывает откат (rollback) данных, измененных приложением, тогда как подпрограмма p_error вызывает фиксацию (commit) только, чтобы сохранить последнюю вставленную запись в журнал ошибок (error log).

Источник: Oracle Professional eXTRA #2.9, eNewsletter Autonomous Transactions

Количество изменённых записей

Пример процедуры:

 BEGIN  
   UPDATE emp e SET e.mgr=7698 WHERE e.job='SALESMAN';  
   dbms_output.put_line(sql%rowcount||' ROWS UPDATED');  
 END;  

Вывод:

 set serveroutput on  
 4 ROWS UPDATED