Массовая обработка
Во время интерпретации байт-кода программ 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 с привилегиями на ее выполнение ничего не происходит.
Находящиеся в базе данных хранимые программы можно перекомпилировать с помощью DDL-команды ALTER:
SQL> ALTER PROCEDURE proc1 COMPILE;
Procedure altered.
Как и другие объекты базы данных, хранимые программы могут быть удалены. Пользователю не требуются дополнительные привилегии для удаления программ в своей схеме, для удаления программ в схеме другого пользователя необходимо наличие привилегии DROP ANY PROCEDURE.
SQL> DROP PROCEDURE proc1;
Procedure dropped.
Процедуры и функции
Функция отличается от процедуры тем, что функция возвращает значение указанного при создании функции типа данных, а процедура ничего не возвращает. Вызов функции всегда включается в некоторое выражение, то есть возвращаемый функцией результат обязательно нужно куда-то детьприсвоить его значение некоторой переменной или передать в качестве параметра другой функции или процедуре. Функции на PL/SQL можно использовать в предложениях SQL наряду со встроенными функциями языка SQL.
Обычно процедуры и функции создаются для решения определенных небольших задач. При продуманной структуре исходного кода каждая процедура или функция со всеми разделами и вложенными блоками должна умещаться на одном экране (максимум 30-40 строк). Если код процедуры или функции разрастается, то имеет смысл продумать его декомпозицию, использовать пакеты или перегружаемые программы.
Процедуры
Команда создания процедуры имеет следующий синтаксис:
CREATE [OR REPLACE]
раздел заголовка блока PL/SQL
PROCEDURE
[имя схемы.]имя процедуры
[(имя параметра [{IN | OUT | IN OUT}] тип данных
[,имя параметра [{IN | OUT | IN OUT}] тип данных ])]
{IS | AS}
остальные разделы блока PL/SQL (объявлений,исполняемый,обработки исключений)
В процедурах не используется ключевое слово DECLAREобъявление пользовательских типов данных, переменных, курсоров начинается сразу после ключевого слова AS. Областью видимости объявленных здесь элементов будет являться вся процедура. В разделе объявлений процедуры можно реализовать и другую процедуру или функцию, которые будут видны только внутри родительской процедуры:
CREATE OR REPLACE PROCEDURE proc2 AS
FUNCTION nested_proc RETURN INTEGER IS
BEGIN
NULL;
END;
BEGIN
nested_proc();
END;
Пусть таблица tab1 создана следующей DDL-командой:
CREATE TABLE tab1 (at1 NUMBER, at2 DATE);
Создадим процедуру insRec, которая заносит в таблицу 1/2 переданного значения числового параметра и текущую дату.
SQL> CREATE OR REPLACE PROCEDURE insRec(p_arg1 IN NUMBER) AS
2 coeff CONSTANT NUMBER := 0.5;
3 BEGIN
4 INSERT INTO tab1 VALUES(coeff*p_arg1,SYSDATE);
5 END;
/
Procedure created.
После создания процедуру можно вызвать из любого блока PL/SQL, указав ее имя и параметры.
SQL> DECLARE
2 l_arg1 NUMBER := 240;
3 BEGIN
4 insRec(l_arg1);
5 END;
6 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM Tab1;
AT1 AT2
120 04.05.2015
В SQL*Plus для вызова процедур есть команда EXECUTE.
SQL> EXECUTE insRec(100);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM Tab1;
AT1 AT2
120 04.05.2015
50 04.05.2015
В процедурах можно использовать команду RETURN. Как только в потоке команд в процедуре встретится команда RETURN, выполнение процедуры прекращается и управление передается вызвавшему процедуру блоку.
Функции
Команда создания функции имеет следующий синтаксис:
CREATE [OR REPLACE] FUNCTION
раздел заголовка блока PL/SQL
[имя схемы.]имя функции
[(имя параметра [{IN | OUT | INOUT}] тип данных
[,имя параметра [{IN | OUT | INOUT}] тип данных ])] RETURN тип данных AS
остальные разделы блока PL/SQL (объявлений,исполняемый,обработки исключений)
Пусть таблица tab1 создана и заполнена следующим образом:
CREATE TABLE tab1 (at1 NUMBER, at2 DATE);
INSERT INTO tab1 VALUES(5, SYSDATE);
INSERT INTO tab1 VALUES(6, SYSDATE);
INSERT INTO tab1 VALUES(7, SYSDATE+1);
Создадим функцию, которая вычисляет сумму значений столбцов таблицы, таких, что дата попадает в заданный интервал.
SQL> CREATE OR REPLACE FUNCTION sumRecInt(arg1 IN DATE,
2 arg2 IN DATE) RETURN NUMBER AS
3 sum_var NUMBER := 0;
4 BEGIN
5 SELECT SUM(at1) INTO sum_var FROM tab1
6 WHERE at2 BETWEEN arg1 AND arg2;
7 RETURN sum_var;
8 END;
9 /
Function created.
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(sumRecInt(SYSDATE-1/2, SYSDATE+1/2));
3 END;
4 /
11
PL/SQL procedure successfully completed.
Ход вычислений функции обязательно должен завершаться вызовом в ее теле команды RETURN возвращаемое значение. Если этого не произойдет, то возникнет ошибка этапа выполнения:
SQL> CREATE FUNCTION func2 RETURN INTEGER AS
2 BEGIN
3 NULL;
4 END;
5 /
Function created.
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(func2);
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "U1.FUNC2", line 3
ORA-06512: at line 2
Иногда на лекциях студентами задается вопрос, поддерживаются ли в PL/SQL рекурсивные функции, то есть функции, вызывающие сами себя. Поддерживаются, приведем пример наиболее понятной на все времена рекурсивной функции:
SQL> CREATE OR REPLACE FUNCTION factorial(n IN INTEGER) RETURN INTEGER IS
2 BEGIN
3 IF n=0 THEN
4 RETURN 1;
5 ELSE
6 RETURN n*factorial(n-1);
7 END IF;
8 END;
9 /
Function created.
SQL> DECLARE
2 l_number INTEGER := 3;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(factorial(l_number));
5 DBMS_OUTPUT.PUT_LINE(factorial(COS(0)));
6 END;
7 /
6
1
PL/SQL procedure successfully completed.
Параметры процедур и функций
Процедуры и функции могут иметь параметры, для которых указываются имена, типы данных и режимы передачи значений.
Важно понимать различия между формальными и фактическими параметрами. Формальные параметры указываются в списке параметров заголовка программы при ее объявлении, тогда как фактические параметрыэто значения и выражения, которые помещаются в список параметров при ее вызове. Иными словами, значения фактических параметров передаются при вызове внутрь процедур и функций, где становятся значениями формальных параметров. Фактическим параметром при первом вызове функции factorial являлась переменная l_number, объявленная в вызывающем блоке. Эта переменная имела значение 3, которое и было использовано внутри функции (3!=6). При втором вызове функции factorial фактическим параметром являлось выражение COS(0). Как известно, 1!=1;
Соответствие формальных и фактических параметров
Соответствие между формальными и фактическими параметрами можно устанавливать двумя способами:
связывание по позиции (неявное связывание);
связывание по имени.
При неявном связывании фактические параметры указываются в круглых скобках после имени программы в той же последовательности, в которой были перечислены формальные параметры при создании программы.
Связывание формальных и фактических параметров по имени осуществляется с помощью конструкций вида
имя формального параметра => имя фактического параметра
С точки зрения выполнения программы нет разницы между используемыми ней способами установления соответствия между параметрами, которые обычно определяются принятым стилем программирования, корпоративными стандартами кодирования и рядом других факторов. В программах с небольшим числом формальных параметров оправдано использование соответствия параметров по позиции. В программах с большим числом формальных параметров связывание параметров по имени более информативно, более ясно показывает связь между формальными и фактическими параметрами.
SQL> CREATE PROCEDURE print(phrase IN VARCHAR2,punctuation_mark IN CHAR) IS
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE(phrase||' '||punctuation_mark);
4 END;
5 /
Procedure created.
SQL> BEGIN
2 print('Hello,world','!');
3 END;
4 /
Hello,world !
PL/SQL procedure successfully completed.
SQL> BEGIN
2 print(punctuation_mark=>'!',phrase=>'Hello,world');
3 END;
4 /
Hello,world !
PL/SQL procedure successfully completed.
Если у программы в будущем появятся новые формальные параметры, то код, в котором она вызывается со связыванием параметров по имени, останется работоспособным. Если для новых параметров указаны значения по умолчанию, то они будут использованы как фактические параметры, если значения по умолчанию отсутствуют, то новые параметры получат значения NULL. А вот все вызовы со связыванием по позиции при появлении у вызываемой программы новых формальных параметров потребуется изменить так, чтобы фактических параметров снова стало столько же, сколько формальных.
Отметим, что при хорошем стиле программирования не принято объявлять процедуры и функции с большим (больше 10) числом параметров скалярных типов данных. В этом случае надо использовать небольшое число параметров составных типов данных (записи PL/SQL или коллекции).