Массовая обработка
Во время интерпретации байт-кода программ 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 позволяет создавать и процедуры, и функции, и пакеты.
Для создания этих хранимых программ в схемах других пользователей требуется наличие системной привилегии CREATE ANY PROCEDURE, предоставленной явно или через роль. Для создания триггеров требуются отдельные привилегии CREATE TRIGGER и CREATE ANY TRIGGER.
DDL-команды CREATE для создания хранимых программ PL/SQL имеют необязательные ключевые слова CREATE [OR REPLACE], указывающую на замену существующей программы новой программой с тем же именем. Если слова OR REPLACE не указаны в команде CREATE, а хранимая программа с таким именем в базе данных уже есть, то создание программы завершится с ошибкой.
SQL> CREATE PROCEDURE proc1 AS
2 BEGIN
3 NULL;
4 END;
5 /
Procedure created.
SQL> CREATE PROCEDURE proc1 AS
2 BEGIN
3 NULL;
4 END;
5 /
CREATE PROCEDURE proc1 AS
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> CREATE OR REPLACE PROCEDURE proc1 AS
2 BEGIN
3 NULL;
4 END;
5 /
Procedure created.
Можно было бы сначала удалить существующую программу, а потом создать новую с тем же именем, но рекомендуется так не делать по следующей причине.
Для хранимых программ PL/SQL пользователям и ролям базы данных предоставляются объектные привилегии на их выполнение. Если удалить хранимую программу, то эти привилегии пропадут (правильнее сказатьавтоматически отзовутся в связи с удалением объекта доступа). После того, как хранимая программа с таким же именем заново будет создана, привилегии эти сами по себе не восстановятся, владельцу программы придется предоставлять их другим пользователям снова. При пересоздании хранимой программы DDL-командой CREATE OR REPLACE с привилегиями на ее выполнение ничего не происходит.