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

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 самый быстрый.

Время работы программ 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