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

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

Виды хранимых программ

В PL/SQL имеются следующие виды хранимых программ:

процедура (procedure) – программа, которая выполняет одно или несколько действий и вызывается как исполняемая команда PL/SQL;

функция (function) – программа, которая возвращает одно значение и используется как выражение PL/SQL;

пакет (package) – набор процедур, функций, переменных, констант и типов данных, объединенных общим функциональным назначением;

триггер (trigger) – программа, которая автоматически запускается при наступлении событий, указанных при создании триггера.

Создание, изменение и удаление хранимых программ

Хранимые программы являются объектами баз данных Oracle. Как и другие объекты баз данных, хранимые программы создаются DDL-командами CREATE, изменяются DDL-командами ALTER и удаляются DDL-командами DROP.

Чтобы создать хранимую процедуру в своей схеме, пользователю необходимо иметь системную привилегию CREATE PROCEDURE или роль с этой привилегией, например, роль RESOURCE. Привилегии CREATE FUNCTION в Oracle SQL нет, привилегия CREATE PROCEDURE позволяет создавать и процедуры, и функции, и пакеты.