Программирование динамического PL/SQL на примерах

Динаимческий PL/SQLДинамический 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 будет не столь прямолинейным, и все же потенциал для повышения эффективности огромен.

 

Вас заинтересует / Intresting for you:

Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 14739 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 4634 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Символьные функции и аргументы...
Символьные функции и аргументы... 18546 просмотров Анатолий Wed, 23 May 2018, 18:54:01
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 12240 просмотров Doctor Thu, 12 Jul 2018, 08:41:33
Войдите чтобы комментировать

anders7777 аватар
anders7777 ответил в теме #9481 4 года 5 мес. назад
Статья действительно крута!)
OraCool аватар
OraCool ответил в теме #9476 4 года 5 мес. назад
Doc, поддерживаю! Статья бесценна!! Можно и в о..пу!!))))))
Doc аватар
Doc ответил в теме #9190 5 года 6 мес. назад
Динамический SQL дает обширнейшие возможности для программирования кода на PL/SQL. Спасибо за внятную статью по теме! Начинающие проггеры должны Вас расцеловать (и пардон за мой французский) буквально в ..опу за такой подробный мануал!