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

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

создаем функцию с целочисленным параметром, оформляя ее как автономную транзакцию, в теле функции помещаем команду вставки в таблицу журнала строки со значением переданного параметра и стандартными данными аудита – именем пользователя, IP-адресом, временем обращения; отметим, что не играет роли, что конкретно функция возвращает в качестве результата;

создаем представление (VIEW), в котором в списке столбцов указываем нашу функцию, передавая ей в качестве параметра название столбца-первичного ключа таблицы;

у всех пользователей отзываем привилегии выполнения предложений SELECT к этой таблице и предоставляем вместо них привилегии на SELECT к нашему представлению.

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

Курсоры FOR UPDATE

Обычная схема обработки данных с помощью явного курсора выглядит следующим образом: с помощью команды FETCH в цикле считываются отобранные SQL-запросом курсора строки и внутри цикла на каждой строке выполняются некоторые вычисления, результаты которых затем сохраняются в базе данных. При этом считываемые из курсора строки не блокируются и могут быть изменены в это время другими транзакциями, что может привести к различным проблемам.

Рассмотрим следующий пример.

Пусть в базе данных есть таблица балансов клиентов:

CREATE TABLE balances (client_id INTEGER, balance NUMBER);

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