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

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

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

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

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

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

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

Курсоры FOR UPDATE

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