Динамический PL/SQL
открывает перед программистом ряд интереснейших возможностей. Только подумайте: в то время как пользователь работает с вашим приложением, вы можете с помощью NDS
:
- создать программу, в том числе пакет, содержащий глобальные структуры данных;
- получать и модифицировать значения глобальных переменных по именам;
- вызывать функции и процедуры, имена которых не известны во время компиляции. Я использовал эту технологию для разработки очень гибких генераторов кода, которые динамически формируют вычислительные блоки и множество других компонентов программы. Динамический
PL/SQL
позволяет работать на более высоком уровне абстракции, но реализовать предоставляемые им возможности одновременно и сложно, и невероятно интересно.
Несколько правил и советов, которые пригодятся вам при работе с динамическими блоками PL/SQL
и NDS
:
- Динамическая строка должна быть допустимым блоком
PL/SQL
, начинаться с ключевого словаDECLARE
илиBEGIN
(которому может предшествовать метка или комментарий), а завершаться командойEND
и точкой с запятой (в противном случае она не считается кодомPL/SQL
). - В динамическом блоке доступны только глобальные элементы
PL/SQL
(функции, процедуры и другие элементы, объявленные в спецификации пакета). Динамические блокиPL/SQL
выполняются вне локального внешнего блока. - Ошибки, возникающие в динамическом блоке
PL/SQL
, могут быть перехвачены и обработаны локальным блоком, в котором с помощью командыEXECUTE
IMMEDIATE
выполнялись динамические командыSQL
.
Построение динамических блоков PL/SQL
Давайте применим эти правила на практике. Сначала напишем маленькую утилиту для выполнения динамического кода PL/SQL
:
PROCEDURE dynPLSQL (blk IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE
'BEGIN ' || RTRIM (blk, ';') || '; END;';
END;
В этой программе отражены многие из упоминавшихся выше правил выполнения PL/ SQL
. Заключение строки в анонимный блок BEGIN-END
гарантирует, что она будет выполнена как допустимый блок PL/SQL
. Например, следующая команда динамически выполняет процедуру calc_totals:
SQL> exec dynPLSQL ('calc_totals');
Используем эту программу для определения того, на какие структуры данных можно ссылаться из динамического блока PL/SQL
. В следующем анонимном блоке динамический PL/SQL
присваивает значение 5 локальной переменной num
:
<<dynamic>>
DECLARE
num NUMBER;
BEGIN
dynPLSQL ('num := 5');
END;
Приведенная строка выполняется в собственном блоке BEGIN-END
, который кажется вложенным в блок dynamic
. Однако при выполнении этого сценария выводятся следующие сообщения об ошибках:
PLS-00201: identifier 'NUM' must be declared
ORA-06512: at "SCOTT.DYNPLSQL", line 4
Ядро PL/SQL
не может разрешить ссылку на переменную num, причем сообщение об ошибке выводится даже при уточнении имени переменной именем блока:
<<dynamic>>
DECLARE
num NUMBER;
BEGIN
/* Тоже приводит к ошибке PLS-00302! */
dynPLSQL ('dynamic.num := 5');
END;
Предположим, переменная num
определена в пакете dynamic
:
PACKAGE pkgvars IS
num NUMBER;
END pkgvars;
Теперь динамическое присваивание этой новой переменной будет выполнено успешно:
BEGIN
dynPLSQL ('pkgvars.num := 5');
END;
Чем различаются эти два фрагмента данных? В первом случае переменная объявлялась локально в анонимном блоке PL/SQL
, а во втором она была объявлена в пакете как общая глобальная переменная. Это различие принципиально для динамического PL/SQL
. Оказывается, динамически сконструированный и выполняемый блок PL/SQL
не считается вложенным; он выполняется как процедура или функция, вызываемая из текущего блока. Поэтому любые переменные, локальные для текущего или внешнего блока, в динамическом блоке PL/SQL
недоступны. Из него можно ссылаться только на глобально определяемые программы и структуры данных, в том числе на отдельные функции и процедуры, а также структуры данных, объявляемые в спецификации пакета.
К счастью, несмотря на это, динамический блок выполняется в контексте вызывающего блока. Это означает, что если в динамическом блоке будет инициировано исключение, оно перехватывается обработчиком исключений вызывающего блока. Поэтому при выполнении в SQL*Plus
анонимного блока
BEGIN
dynPLSQL ('undefined.packagevar := ''abc''');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (SQLCODE);
END;
исключение будет обработано.
Присваивание, выполняемое в этом анонимном блоке, дает пример использования косвенной ссылки: я обращаюсь к переменной не напрямую, а указываю ее имя. Продукт
Oracle Forms Builder
(ранее известный под названиямиSQL*Forms
иOracle Forms
) предоставляет реализацию косвенных ссылок в программахNAME_IN
иCOPY
. Эта возможность позволяет разработчикам строить логику, доступную для всех форм в приложении. PL/SQL не поддерживает косвенные ссылки, но их можно реализовать в динамическомPL/SQL
. Пример такой реализации приведен в файле dynvar.pkg на сайте книги.
Ниже приведены дополнительные примеры применения динамического PL/SQL
. Хочется надеяться, что они вызовут у вас интерес.
Замена повторяющегося кода динамическими блоками
Честное слово, совершенно правдивая история. Когда я консультировал одну страховую компанию в Чикаго, меня попросили что-нибудь сделать с одной крайне неприятной программой. Она была просто огромной и постоянно увеличивалась в размерах — предполагалось, что вскоре она станет настолько большой, что перестанет компилироваться. К моему изумлению, эта программа выглядела так:
PROCEDURE process_line (line IN INTEGER)
IS
BEGIN
IF line = 1 THEN process_line1;
ELSIF line = 2 THEN process_line2;
...
ELSIF line = 514 THEN process_line514;
...
ELSIF line = 2057 THEN process_line2057;
END IF;
END;
Каждый номер представлял один из пунктов страхового полиса, которые печатаются мелким шрифтом и помогают страховой компании добиться своей главной цели (сокращения страховых выплат). Для каждого номера существовала программа process_line, которая обрабатывала все подробности. И по мере того, как страховая компания добавляла новые исключения, программа становилась все больше и больше. Сами понимаете, масштабируемость такого подхода оставляет желать лучшего.
Чтобы избежать подобной путаницы, программист должен обращать внимание на повторяющийся код. Если вам удастся выявить закономерность, вы сможете либо создать универсальную программу, которая эту закономерность инкапсулирует, либо выразить ее в виде динамической конструкции SQL
.
В тот раз я решил проблему с использованием DBMS_SQL
, но динамический SQL
стал бы идеальным решением. Реализация NDS
выглядела бы так:
PROCEDURE process_line (line IN INTEGER)
IS
BEGIN
EXECUTE IMMEDIATE
'BEGIN process_line' || line || '; END;';
END;
Тысячи строк кода свелись к одной исполняемой команде! Конечно, в большинстве случаев процесс выявления закономерности и ее преобразования в динамический SQL будет не столь прямолинейным, и все же потенциал для повышения эффективности огромен.