Довольно часто в программах 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-запросом курсора строки и внутри цикла на каждой строке выполняются некоторые вычисления, результаты которых затем сохраняются в базе данных. При этом считываемые из курсора строки не блокируются и могут быть изменены в это время другими транзакциями, что может привести к различным проблемам.

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

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

CREATE TABLE balances (client_id INTEGER, balance NUMBER);

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

DECLARE

CURSOR c_balances IS SELECT * FROM balances;

l_client_type VARCHAR2(100);

l_new_year_coeff NUMBER;

BEGIN

FOR rec_balance IN c_balances LOOP

– определяем тип клиента (активный / не активный)

l_client_type := getClientType(rec_balance); – вызов длится 1 минуту

CASE l_client_type

WHEN 'active' THEN l_new_year_coeff := 1.05;

WHEN 'non-actve' THEN l_new_year_coeff := 1.01;

END CASE;

UPDATE balances SET balance = rec_balance.balance*l_new_year_coeff

WHERE balances.client_id = rec_balance.client_id;

END LOOP;

COMMIT;

END;

Если в базе данных 100 клиентов, то анонимный блок будет выполняться 100 минут. Пусть на 30-й минуте с момента начала выполнения блока клиент одной из еще не считанной из курсора строк (для определенности, пусть 70-й по счету) вносит платеж и его баланс увеличивается на сумму платежа. Транзакция увеличения баланса этого клиента фиксируется (изменить 70-ю строку этой транзакции можно, строка не блокирована, так как ней пока еще не обращались в ходе «подарочного» расчета).

Когда на 70-й минуте курсор c_balances дочитает до этой строки, то обнаружится, что строка с момента начала выполнения SQL-запроса курсора была изменена зафиксированной транзакцией и для обеспечения согласованности чтения восстановит ее предыдущую версию (с балансом без нового платежа). Соответственно, после выполнения команды UPDATE для этого клиента будет проставлена увеличенная на один или пять процентов сумма старого (без учета нового платежа) баланса.

В теории баз данных это называется явлением пропавшего обновления (lost update phenomena) – пропало отражение на балансе нового платежа, оно было перезаписано ранее считанными данными. В данном случае более правильная формулировка – перезаписано данными, восстановленными по состоянию, которое было ранее. Ведь собственно считывалась-то 70-я строка из курсора уже после поступления нового платежа, просто в ходе ее считывания для обеспечения согласованности произошло восстановление старой версии строки.

Чтобы не сталкиваться с проблемой пропавшего обновления, необходимо на все время «подарочной» транзакции заблокировать данные балансов от изменений. Для этого в курсоре c_balances следует записать не просто SQL-запрос, а SQL-запрос с блокировкой отбираемых строк:

CURSOR c_balances IS SELECT * FROM balances FOR UPDATE;

Посмотрим, что изменится в этом случае. Для запросов с опцией FOR UPDATE блокировка на отбираемые строки накладывается в ходе открытия курсора командой OPEN, еще до считывания первой строки командой FETCH. Поэтому если курсор с FOR UPDATE успешно открылся, то это значит, что все отбираемые SQL-запросом курсора строки уже заблокированы. Если какие-то строки с балансами заблокированы другими активными транзакциями, то наш процесс во время открытия курсора с SELECT FOR UPDATE будет ждать снятия этих блокировок.

После успешного открытия курсора c_balances с FOR UPDATE строки всех балансов будут заблокированы нашей транзакцией и до снятия этой блокировки только она может их изменять. Все другие транзакции, которые будут вносить платежи и изменять (увеличивать) балансы, сами будут переходить в режим ожидания снятия блокировки с балансов, установленной нашей «подарочной» транзакцией.

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

Конструкция WHERE CURRENT OF в DML-командах

Конструкция WHERE CURRENT OF предназначена для удаления или изменения той строки таблицы, которая является текущей в курсоре. Преимущество использования этой конструкции заключается в однократном задании критерия отбора данных в SQL.

Перепишем рассмотренный выше пример с расчетом новогоднего подарочного увеличения баланса с использованием конструкции WHERE CURRENT OF и немного изменим его, сделав расчет не для всех клиентов, а для баланса одного конкретного клиента по его идентификатору.

Первый вариант программы (без использования WHERE CURRENT OF):

DECLARE

l_client_id INTEGER := 122329;

l_balance balances%ROWTYPE;

l_new_year_coeff NUMBER;

BEGIN

SELECT * INTO l_balance FROM balances

WHERE balances.client_id=l_client_id FOR UPDATE;

CASE getClientType(l_balance);

WHEN 'active' THEN l_new_year_coeff := 1.05;

WHEN 'non-actve' THEN l_new_year_coeff := 1.01;

END CASE;

UPDATE balances SET balance = l_balance.balance*l_new_year_coeff

WHERE balances.client_id=l_client_id;

END;

Второй вариант программы (c использованием WHERE CURRENT OF):

DECLARE

CURSOR c_balances IS SELECT * FROM balances

WHERE balances.client_id=122329 FOR UPDATE;

l_client_type VARCHAR2(100);

l_new_year_coeff NUMBER;

BEGIN

FOR rec_balance IN c_balances LOOP

CASE getClientType(rec_balance)

WHEN 'active' THEN l_new_year_coeff := 1.05;

WHEN 'non-actve' THEN l_new_year_coeff := 1.01;

END CASE;

UPDATE balances SET balance = rec_balance.balance *l_new_year_coeff

WHERE CURRENT OF c_balances;

END LOOP;

END;

В первом варианте видно, что один и тот же критерий отбора данных (WHERE balances.client_id=l_client_id) используется дважды – и в SELECT и в UPDATE. Ничего особо страшного в этом нет, но нарушается принцип DRY: одна и та же логика программируется в двух местах, и при внесении каких-либо изменений придется следить за синхронизацией этих участков кода.

При использовании конструкции WHERE CURRENT OF во втором варианте программы PL/SQL компилятор неявно добавил столбец ROWID в список столбцов, возвращаемых запросом курсора. Напомним, что ROWID (row identifier) является физическим указателем на место размещения строки таблицы в файле данных и переход по ROWID – самый быстрый способ обращения к строке.

Для команды PL/SQL UPDATE с конструкцией WHERE CURRENT OF компилятором PL/SQL условие во фразе WHERE соответствующего SQL-предложения UPDATE будет сформировано как ROWID=:B1. На каждой итерации цикла перед выполнением UPDATE с переменной :B1 связывается значение ROWID строки таблицы balances, считанной из курсора на этой итерации.

Получается даже два положительных эффекта от использования конструкции WHERE CURRENT OF – и критерий отбора данных указывается один раз, и UPDATE строки таблицы по ее ROWID самый быстрый.

Оптимизация выполнения SQL из PL/SQL

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

Массовая обработка

Во время интерпретации байт-кода программ PL/SQL виртуальная машина PL/SQL имеющиеся в байт-коде предложения SQL передает ядру Oracle, которое выполняет их и возвращает результаты обработки обратно виртуальной машине PL/SQL. Передача управления между PL/SQL и SQL называется переключением контекста. Число переключений контекста определяется количеством выполненных команд INSERT, UPDATE, DELETE и количеством считанных строк результирующих выборок курсоров, причем на каждую считанную из курсора строку будет два переключения контекста – из PL/SQL в SQL и обратно.

Рассмотрим следующий пример. Пусть на обработку поступает «пачка» платежей. Требуется для каждого платежа увеличить баланс соответствующего лицевого счета на сумму платежа.

CREATE TABLE balances (account INTEGER, balance NUMBER);

INSERT INTO balances VALUES(101,500);

INSERT INTO balances VALUES(102,800);

INSERT INTO balances VALUES(103,532);

Первый вариант решения задачи – с последовательным выполнением команд UPDATE в цикле по всем платежам в «пачке»:

DECLARE

TYPE t_payment IS RECORD

(account INTEGER,

amount NUMBER,

in_date DATE);

TYPE t_payment_pack IS TABLE OF t_payment;

l_payment_pack t_payment_pack := t_payment_pack();

BEGIN

– в пачке два платежа

l_payment_pack.EXTEND(2);

– формируем первый платеж (50 рублей на лицевой счет 101)

l_payment_pack(1).account := 101;

l_payment_pack(1).amount := 50;

l_payment_pack(1).in_date := TO_DATE('02.03.2015','dd.mm.yyyy');

– формируем второй платеж (400 рублей на лицевой счет 102)

l_payment_pack(2).account := 102;

l_payment_pack(2).amount := 400;

l_payment_pack(2).in_date := TO_DATE('23.05.2015','dd.mm.yyyy');

– в цикле обновляем балансы

FOR i IN 1..l_payment_pack.count LOOP

UPDATE balances SET balance=balance+l_payment_pack(i).amount

WHERE balances.account=l_payment_pack(i).account;

END LOOP;

END;

В цикле будет выполнено две DML-команды UPDATE и произойдет четыре переключения контекста SQL-PL/SQL. Если бы в пачке платежей было 10 000 платежей, то переключений контекста было бы 20 000.

Каждое переключение контекста приводит к дополнительным затратам ресурсов, поэтому их число следует минимизировать. Идеальным решением является внесение всех изменений данных одним единственным предложением SQL. Во многих случаях этого можно добиться, однако все же бывает так, что или без выполнения команд INSERT, UPDATE, DELETE в цикле никак не обойтись, или предстоит считывание большого числа строк из курсора выполнением команды FETCH для каждой строки. Для таких случаев в языке PL/SQL есть средства массовой обработки данных (bulk processing), использование которых минимизирует число переключений контекста и повышает общую производительность программ PL/SQL:

команда FORALL для выполнения наборов команд INSERT, UPDATE, DELETE;

конструкция BULK COLLECT для считывания из курсора всех строк результирующей выборки одной командой.

Команда FORALL

Команда FORALL позволяет вместо циклического выполнения предложений SQL для команд INSERT, UPDATE, DELETE с постоянным переключением контекста PL/SQL-SQL собрать одинаковые предложения SQL в один набор и выполнить их все вместе в ходе одного обращения к ядру Oracle.

Команда FORALL имеет следующий синтаксис:

FORALL индекс IN [ нижняя граница … верхняя граница |

INDICES OF коллекция | VALUES OF коллекция][ SAVE EXCEPTIONS ]

DML-команда (INSERT | UPDATE | DELETE)

Необязательная конструкция SAVE EXCEPTIONS указывает на необходимость обработки всех предложений SQL из набора с сохранением всех возникающих исключений. Так как для одной команды FORALL выполняется несколько предложений SQL, то возникает вопрос о том, что будет, если при выполнении одного из них произойдет ошибка. Общие правила здесь следующие:

изменения, сделанные предложением SQL, завершившимся с ошибкой, отменяются;

изменения, сделанные предшествующими успешно выполненными предложениями SQL из набора этой команды FORALL, не отменяются;

если отсутствует конструкция SAVE EXCEPTIONS, то выполнение FORALL останавливается.

Приведем второй вариант решения задачи обновления балансов для нескольких поступивших платежей.

DECLARE

TYPE t_payment IS RECORD

(account INTEGER,

amount NUMBER,

in_date DATE);

TYPE t_payment_pack IS TABLE OF t_payment;

l_payment_pack t_payment_pack := t_payment_pack();

BEGIN

l_payment_pack.EXTEND(2);

l_payment_pack(1).account := 101;

l_payment_pack(1).amount := 50;

l_payment_pack(1).in_date := TO_DATE('02.03.2015','dd.mm.yyyy');

l_payment_pack(2).account := 102;

l_payment_pack(2).amount := 400;

l_payment_pack(2).in_date := TO_DATE('23.05.2015','dd.mm.yyyy');

FORALL indx IN 1..l_payment_pack.COUNT

UPDATE balances SET balance=balance+l_payment_pack(indx).amount

WHERE balances.account=l_payment_pack(indx).account;

END;

Два предложения UPDATE выполнились в составе одного набора. Вместо четырех переключений контекста PL/SQL-SQL их произошло два. Если бы в пачке платежей было 10 000 платежей, то число переключений контекста по-прежнему осталось бы равным двум, а не 20 000.

Конструкция BULK COLLECT

Использование конструкции BULK COLLECT позволяет считать из курсора сразу все строки результирующей выборки SQL-запроса. Курсор при этом может быть как явным, так и неявным – для команды SELECT INTO. «Приемником» для строк, считанных с использованием конструкции BULK COLLECT, должна быть коллекция. При массовом считывании также не происходит переключений контекстов и выборка данных осуществляется оптимальным образом.

Перепишем приведенные ранее блоки PL/SQL для считывания всех строк из явного курсора. Для наглядности приведем обе реализации (с циклом и без него).

Считывание в цикле по одной строке

Использование BULK COLLECT

DECLARE

CURSOR c1 IS SELECT * FROM tab1;

rec c1%ROWTYPE;

BEGIN

OPEN c1;

FETCH c1 INTO rec;

WHILE c1%FOUND LOOP

FETCH c1 INTO rec;

END LOOP;

CLOSE c1;

END;

DECLARE

CURSOR c1 IS SELECT * FROM tab1;

TYPE t_tab IS TABLE OF c1%ROWTYPE;

l_tab t_tab;

BEGIN

OPEN c1;

FETCH c1 BULK COLLECT INTO l_tab;

CLOSE c1;

END;

Обратите внимание, в коде объявлена коллекция на основе курсора, в эту коллекцию и осуществляется считывание. В результате получается очень компактный код, в котором, например, в коде считывания строк результирующей выборки нигде не указаны столбцы выборки, а сам код считывания занимает три строчки, при этом не используются команды циклов.