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

Команда 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 позволяет создавать и процедуры, и функции, и пакеты.

Для создания этих хранимых программ в схемах других пользователей требуется наличие системной привилегии CREATE ANY PROCEDURE, предоставленной явно или через роль. Для создания триггеров требуются отдельные привилегии CREATE TRIGGER и CREATE ANY TRIGGER.