Динамический SQL и NDS команды PL/SQL на примерах

команды PL/SQL, EXECUTE IMMEDIATE, OPEN FOR в PL/SQLТермином «динамический SQL» обозначаются команды SQL, которые конструируются и вызываются непосредственно во время выполнения программы. Статическими называются жестко закодированные команды SQL, которые не изменяются с момента компиляции программы. «Динамическим PL/SQL» называют целые блоки кода PL/SQL, которые строятся динамически, а затем компилируются и выполняются.


Оглавление статьи[Показать]


Пожалуй, написание динамических команд SQL и динамических программ PL/SQL было самым интересным делом из всего, что я когда-либо делал на языке PL/SQL. Конструирование и динамическое выполнение обеспечивает невероятную гибкость. У разработчика появляется возможность создавать обобщенный код с широким спек­тром применения. Несмотря на это, динамический SQL следует применять лишь там, где это необходимо; решения со статическим SQL всегда являются предпочтительны­ми, потому что динамические решения более сложны, создают больше проблем с от­ладкой и тестированием, обычно медленнее работают и усложняют сопровождение. Что же можно делать с динамическими конструкциями SQL и PL/SQL? Лишь несколько идей:

Начиная с Oracle7, поддержка динамического SQL осуществлялась в виде встроенного пакета DMBS_SQL. В Oracle8i для этого появилась еще одна возможность — встроенный динамический SQL (Native Dynamic SQL, NDS). NDS интегрируется в язык PL/SQL; пользоваться им намного удобнее, чем DBMS_SQL. Впрочем, для некоторых ситуаций

лучше подходит DBMS_SQL. На практике в подавляющем большинстве случаев NDS является более предпочтительным решением.

 

Команды NDS в PL/SQL

Главным достоинством NDS является его простота. В отличие от пакета DBMS_SQL, для работы с которым требуется знание десятка программ и множества правил их исполь­зования, NDS представлен в PL/SQL единственной новой командой EXECUTE IMMEDIATE, которая немедленно выполняет заданную команду SQL, а также расширением существу­ющей команды OPEN FOR, позволяющей выполнять многострочные динамические запросы.

Команды EXECUTE IMMEDIATE и OPEN FOR не будут напрямую доступны в Oracle Forms Builder и Oracle Reports Builder до тех пор, пока версия PL/SQL этих инструментов не будет обновлена до Oracle8i и выше. Для более ранних версий придется создавать хранимые программы, скрывающие вызовы этих конструкций; эти хранимые программы могут выполняться в клиентском коде PL/SQL.

 

 

Команда EXECUTE IMMEDIATE

Команда EXECUTE IMMEDIATE используется для немедленного выполнения заданной команды SQL. Она имеет следующий синтаксис:

EXECUTE IMMEDIATE строка_SQL
   [ [ BULK COLLECT] INTO {переменная[, переменная]... | запись}]
   [USING [IN | OUT | IN OUT] аргумент
      [, [IN | OUT | IN OUT] аргумент]...];

Здесь строка_SQL — строковое выражение, содержащее команду SQL или блок PL/SQL; переменная — переменная, которой присваивается содержимое поля, возвращаемого за­просом; запись — запись, основанная на пользовательском типе или типе %ROWTYPE, при­нимающая всю возвращаемую запросом строку; аргумент — либо выражение, значение которого передается команде SQL или блоку PL/SQL, либо идентификатор, являющийся входной и/или выходной переменной для функции или процедуры, вызываемой из блока PL/SQL. Секция INTO используется для однострочных запросов. Для каждого значения столбца, возвращаемого запросом, необходимо указать переменную или поле записи совместимого типа. Если INTO предшествует конструкция BULK COLLECT, появляется воз­можность выборки множественных строк в одну или несколько коллекций. Секция USING предназначена для передачи аргументов строке SQL. Она используется с динамическим SQL и PL/SQL, что и позволяет задать режим параметра. Этот режим актуален только для PL/SQL и секции RETURNING. По умолчанию для параметров используется режим IN (для команд SQL допустима только эта разновидность аргументов).

Команда execute immediate может использоваться для выполнения любой команды SQL или блока PL/SQL. Строка может содержать формальные параметры, но они не могут связываться с именами объектов схемы (например, именами таблиц или столбцов).

При выполнении команды DDL в программе также происходит закрепление опе­рации. Если вы не хотите, чтобы закрепление, обусловленное DDL, отражалось на текущих изменениях в других частях приложения, поместите динамическую команду DDL в процедуру автономной транзакции. Пример такого рода приведен в файле auton_ddl.sql.

При выполнении команды исполняющее ядро заменяет в SQL-строке формальные параметры (идентификаторы, начинающиеся с двоеточия — например, :salary_value)

фактическими значениями параметров подстановки в секции USING. Не допускается передача литерала NULL — вместо него следует указывать выражение соответствующего типа, результат вычисления которого может быть равен NULL.

NDS поддерживает все типы данных SQL. Переменные и параметры команды могут быть коллекциями, большими объектами (LOB), экземплярами объектных типов, до­кументами XML и т. д. Однако NDS не поддерживает типы данных, специфические для PL/SQL, такие как BOOLEAN, ассоциативные массивы и пользовательские типы записей. С другой стороны, секция INTO может содержать запись PL/SQL, количество и типы полей которой соответствуют значениям, выбранным динамическим запросом. Рассмотрим несколько примеров.

BEGIN
   EXECUTE IMMEDIATE 'CREATE INDEX emp_u_1 ON employees (last_name)';
END;

Проще не бывает, верно?

PROCEDURE exec_DDL (ddl_string IN VARCHAR2)
IS
BEGIN
   EXECUTE IMMEDIATE ddl_string;
END;

При наличии процедуры exec_ddl тот же индекс может быть создан следующим образом:

BEGIN
   exec_DDL ('CREATE INDEX emp_u_1 ON employees (last_name)');
END;

Получение количества строк в произвольной таблице для заданного предложения WHERE:

FUNCTION tabcount (table_in IN VARCHAR2)
   RETURN PLS_INTEGER 
IS
   l_query VARCHAR2 (32767) := 'SELECT COUNT(*) FROM ' || table_in;
   l_return PLS_INTEGER;
BEGIN
   EXECUTE IMMEDIATE l_query INTO l_return;
   RETURN l_return;
END;

Таким образом, нам больше не понадобится писать команду SELECT COUNT(*) ни в SQI*Plus, ни в программах PL/SQL. Она заменяется следующим блоком кода:

BEGIN
   IF tabCount ('employees') > 100 THEN
      DBMS_OUTPUT.PUT_LINE ('We are growing fast!');
   END IF;
END;

 Изменение числового значения в любом столбце таблицы employees:

/* Файл в Сети: updnval.sf */
FUNCTION updNVal( 
   col IN VARCHAR2,
   val IN NUMBER,
   start_in IN DATE,
   end_in IN DATE)
   RETURN PLS_INTEGER 
IS
BEGIN
   EXECUTE IMMEDIATE
      'UPDATE employees SET ' || col || ' = :the_value
        WHERE hire_date BETWEEN :lo AND :hi'
      USING val, start_in, end_in;
   RETURN SQL%ROWCOUNT;
END;

Безусловно, для такой гибкости объем кода получился совсем небольшим! В этом примере показано, как используется подстановка: после разбора команды UPDATE ядро PL/SQL заменяет в ней формальные параметры (:the_value, :lo и :hi) значениями переменных. Также обратите внимание, что в этом случае атрибут курсора SQL%ROWCOUNT используется точно так же, как при выполнении статических команд DML. Выполнение разных блоков кода в одно время в разные дни. Имя каждой программы строится по схеме ДЕНЬ_set_sd^edule. Все процедуры получают четыре аргумента: при вызове передается код работника employee_id и час первой встречи, а процедура возвращает имя работника и количество встреч в заданный день. Задача решается с ис­пользованием динамического PL/SQL:

/* Файл в Сети: run9am.sp */
PROCEDURE run_9am_procedure (
   id_in IN employee.employee_id%TYPE, hour_in IN INTEGER)
IS
   v_apptCount INTEGER; v_name VARCHAR2(100);
BEGIN
   EXECUTE IMMEDIATE
     'BEGIN ' || TO_CHAR (SYSDATE, 'DAY') ||
     '_set_schedule (:id, :hour, :name, :appts); END;'
   USING IN
      id_in, IN hour_in, OUT v_name, OUT v_apptCount;
      DBMS_OUTPUT.PUT_LINE (
         'Employee ' || v_name || ' has ' || v_apptCount ||
         ' appointments on ' || TO_CHAR (SYSDATE));
END;
CREATE OR REPLACE PACKAGE restaurant_pkg
AS
   TYPE item_list_t
         IS TABLE OF VARCHAR2 (30);

   PROCEDURE eat_that (
      items_in              IN item_list_t,
      make_it_spicy_in_in    IN BOOLEAN);
END;
/

CREATE OR REPLACE PACKAGE BODY restaurant_pkg
AS
   PROCEDURE eat_that (
      items_in	        IN item_list_t,
      make_it_spicy_in_in	IN BOOLEAN)
   IS
   BEGIN
      FOR indx IN 1 .. items_in.COUNT
      LOOP
         DBMS_OUTPUT.put_line (
            CASE
               WHEN make_it_spicy_in_in
               THEN
                  'Spicy ' 
            END
         || items_in (indx));
      END LOOP;
   END;
END;
/

DECLARE
   things restaurant_pkg.item_list_t
      := restaurant_pkg.item_list_t (
         'steak',
         'quiche',
         'eggplant');
BEGIN
   EXECUTE IMMEDIATE
      'BEGIN restaurant_pkg.eat_that(:l, :s); END;'
      USING things, TRUE;
END;
/

Как видите, команда EXECUTE IMMEDIATE позволяет исключительно легко выполнять динамические команды SQL и блоки PL/SQL с удобным синтаксисом.

 

Команда OPEN FOR

Команда OPEN FOR изначально не была включена в PL/SQL для NDS; она появилась в Oracle7 и предназначалась для работы с курсорными переменными. Затем ее синтаксис был расширен для реализации многострочных динамических запросов. При использо­вании пакета DBMS_SQL реализация многострочных запросов получается очень сложной: приходится производить разбор и подстановку, отдельно определять каждый столбец, выполнять команду, выбирать сначала строки, а затем — последовательно значения каждого столбца. Код получается весьма громоздким.

Для динамического SQL разработчики Oracle сохранили существующий синтаксис OPEN, но расширили его вполне естественным образом:

OPEN {курсорная_переменная| :хост_переменная} FOR строка_SQL
 [USING аргумент [, аргумент]...];

Здесь курсорная переменная — слаботипизированная курсорная переменная; хост_ переменная — курсорная переменная, объявленная в хост-среде PL/SQL, например в программе OCI (Oracle Call Interface); cmpoка SQL — команда SELECT, подлежащая динамическому выполнению.

Курсорные переменные рассматривались в этом блоге. Здесь мы подробно расскажем об их использовании с NDS.

В следующем примере объявляется тип REF CURSOR и основанная на нем переменная- курсор, а затем с помощью команды OPEN FOR открывается динамический запрос:

PROCEDURE show_parts_inventory (
   parts_table IN VARCHAR2,
   where_in IN VARCHAR2)
IS
   TYPE query_curtype IS REF CURSOR;
   dyncur query_curtype;
BEGIN
   OPEN dyncur FOR
      'SELECT * FROM ' || parts_table
       ' WHERE ' || where_in;
   ...

После того как запрос будет открыт командой OPEN FOR, синтаксис выборки записи, за­крытия курсорной переменной и проверки атрибутов курсора ничем не отличается от синтаксиса статических курсорных переменных и явных курсоров.

Давайте поближе познакомимся с командой OPEN FOR. При выполнении OPEN FOR ядро PL/SQL:

  1. связывает курсорную переменную с командой SQL, заданной в строке запроса;
  2. вычисляет значения параметров и заменяет ими формальные параметры в строке запроса;
  3. выполняет запрос;
  4. идентифицирует результирующий набор;
  5. устанавливает курсор на первую строку результирующего набора;
  6. обнуляет счетчик обработанных строк, возвращаемый атрибутом %rowcount. Обратите внимание: параметры подстановки, заданные в секции USING, вычисляются только при открытии курсора. Это означает, что для передачи тому же динамическому запросу другого набора параметров нужно выполнить новую команду OPEN FOR.

Для выполнения многострочного запроса (то есть запроса, возвращающего набор строк) необходимо:

  1. объявить тип ref cursor (или использовать встроенный тип sys_refcursor);
  2. объявить на его основе курсорную переменную;
  3. открыть курсорную переменную командой OPEN FOR;
  4. с помощью команды fetch по одной извлечь записи результирующего набора;
  5. при необходимости проверить значения атрибутов (%found, %notfound, %rowcount, %isopen);
  6. закрыть курсорную переменную обычной командой Как правило, после завершения работы с курсорной переменной следует явно закрыть ее.

Следующая простая программа выводит значения поля заданной таблицы в строках, отбираемых с помощью секции WHERE (столбец может содержать числа, даты или строки, файл showcol.sp):

/* Файл в Сети: showcol.sp */
CREATE OR REPLACE PROCEDURE showcol (
   tab IN VARCHAR2,
   col IN VARCHAR2,
   whr IN VARCHAR2 := NULL)
IS
   cv SYS_REFCURSOR; 
   val VARCHAR2(32767);  
BEGIN
   OPEN cv FOR 
      'SELECT ' || col || 
      '  FROM ' || tab ||
      ' WHERE ' || NVL (whr, '1 = 1');
      
   LOOP
      /* Fetch and exit if done; same as with explicit cursors. */
      FETCH cv INTO val;
      EXIT WHEN cv%NOTFOUND;
      
      /* If on first row, display header info. */
      IF cv%ROWCOUNT = 1
      THEN
         DBMS_OUTPUT.PUT_LINE (RPAD ('-', 60, '-'));
         DBMS_OUTPUT.PUT_LINE (
            'Contents of ' || UPPER (tab) || '.' || UPPER (col));
         DBMS_OUTPUT.PUT_LINE (RPAD ('-', 60, '-'));
      END IF;
      
      DBMS_OUTPUT.PUT_LINE (val);
   END LOOP;
   
   /* Don't forget to clean up! Very important... */
   CLOSE cv;
END;
/ 

Примерный результат выполнения этой процедуры выглядит так:

SQL> EXEC showcol ('emp', 'ename', 'deptno=10')

-----------------------------
Contents of EMP.ENAME
-----------------------------
CLARK
KING
MILLER

Столбцы даже можно комбинировать:

BEGIN
   showcol (
      'emp',
      'ename || ''-$'' || sal',
      'comm IS NOT NULL');END;/
-------------------------------------
Contents of EMP.ENAME || '-$' || SAL
-------------------------------------
ALLEN-$1600
WARD-$1250
MARTIN-$1250
TURNER-$1500

 

Выборка в переменные или записи

Команда FETCH в процедуре showcol из предыдущего раздела осуществляет выборку в отдельную переменную. Также возможна выборка в серию переменных:

PROCEDURE mega_bucks (company_id_in IN INTEGER)
IS
   cv SYS_REFCURSOR;
   mega_bucks company.ceo_compensation%TYPE;
   achieved_by company.cost_cutting%TYPE;
BEGIN
   OPEN cv FOR
   'SELECT ceo_compensation, cost_cutting
    FROM ' || company_table_name (company_id_in);

   LOOP
      FETCH cv INTO mega_bucks, achieved_by;
      ...
   END LOOP;

   CLOSE cv;
END;

Работа с длинным списком переменных в списке FETCH может быть громоздкой и не­достаточно гибкой; вы должны объявить переменные, поддерживать синхронизацию этого набора значений в команде FETCH и т. д. Чтобы упростить жизнь разработчика, NDS позволяет осуществить выборку в запись, как показано в следующем примере:

PROCEDURE mega_bucks (company_id_in IN INTEGER)
IS
   cv SYS_REFCURSOR;
   ceo_info company%ROWTYPE;
BEGIN
   OPEN cv FOR
      'SELECT * FROM ' || company_table_name (company_id_in);

   LOOP
      FETCH cv INTO ceo_info;
      ...
   END LOOP;

   CLOSE cv;
END;

Конечно, во многих ситуациях выполнение команды SELECT * нежелательно; если ваша таблица содержит сотни столбцов, из которых вам нужны два-три, эта команда крайне неэффективна. Лучше создать тип записи, соответствующий разным требованиям. Эти структуры лучше всего разместить в спецификации пакета, чтобы их можно было ис­пользовать во всем приложении. Вот один из таких пакетов:

PACKAGE company_pkg
IS
   TYPE ceo_info_rt IS RECORD (
      mega_bucks company.ceo_compensation%TYPE,
      achieved_by company.cost_cutting%TYPE);
END company_pkg;

С таким пакетом приведенный выше код можно переписать следующим образом:

PROCEDURE mega_bucks (company_id_in IN INTEGER)
IS
   cv SYS_REFCURSOR;
   rec company_pkg.ceo_info_rt;
BEGIN
   OPEN cv FOR
      'SELECT ceo_compensation, cost_cutting FROM ' || 
      company_table_name (company_id_in);

   LOOP
      FETCH cv INTO rec;
      ...
   END LOOP;
   CLOSE cv;
END;

 

Секция USING в OPEN FOR

Как и в случае с командой EXECUTE IMMEDIATE, при открытии курсора можно передать ар­гументы. Для запроса можно передать только аргументы IN. Аргументы также повышают эффективность SQL, упрощая написание и сопровождение кода. Кроме того, они могут радикально сократить количество разобранных команд, хранящихся в общей памяти SGA, а это повышает вероятность того, что уже разобранная команда будет находиться в SGA в следующий раз, когда она вам потребуется.

Вернемся к процедуре showcol. Эта процедура получает полностью обобщенную секцию WHERE. Допустим, действуют более специализированные требования: я хочу вывести (или иным образом обработать) всю информацию столбцов для строк, содержащих столбец даты со значением из некоторого диапазона. Другими словами, требуется обеспечить поддержку запроса:

SELECT
   last_name
  FROM
   employees
 WHERE hire_date BETWEEN x AND y;

а также запроса:

SELECT flavor
 FROM
 favorites
WHERE preference_period BETWEEN x AND y;

Также нужно проследить за тем, чтобы компонент времени столбца даты не учитывался в условии WHERE.

Заголовок процедуры выглядит так:

PROCEDURE showcol
   ( tab IN VARCHAR2,
   col IN VARCHAR2,
   dtcol IN VARCHAR2,
   dt1 IN DATE, dt2 IN
   DATE := NULL)

Теперь команда OPEN FOR содержит два формальных параметра и соответствующую секцию USING:

OPEN cv FOR
   'SELECT ' || col ||
   ' FROM ' || tab ||
   ' WHERE ' || dtcol ||
      ' BETWEEN TRUNC (:startdt)
         AND TRUNC (:enddt)'
   USING dt1, NVL (dt2, dt1+1);

Команда построена таким образом, что при отсутствии конечной даты секция WHERE возвращает строки, у которых значение в столбце даты совпадает с заданным значением dt1. Остальной код процедуры showcol остается неизменным, не считая косметических изменений в выводе заголовка.

Следующий вызов новой версии showcol запрашивает имена всех работников, принятых на работу в 1982 году:

BEGIN
   showcol ('emp', 'ename', 'hiredate',
   DATE '1982-01-01', DATE '1982-12-31');
END;

Результат:

-------------------------------------------------------------------
Contents of EMP.ENAME for HIREDATE between 01-JAN-82 and 31-DEC-82
-------------------------------------------------------------------
MILLER

 

 

О четырех категориях динамического SQL

Итак, мы рассмотрели две основные команды, используемые для реализации динами­ческого SQL в PL/SQL. Теперь пришло время сделать шаг назад и рассмотреть четыре разновидности (категории) динамического SQL, а также команды NDS, необходимые для реализации этих категорий. Категории и соответствующие команды NDS пере­числены в табл. 1.

 

Таблица 1. Четыре категории динамического SQL

тип описание Команды NDS
Категория 1 Без запросов; только команды DDL и команды UPDATE, INSERT, MERGE и DELETE без параметров EXECUTE IMMEDIATE без секций USING и INTO
Категория 2 Без запросов; только команды DDL и команды
UPDATE, INSERT, MERGE и DELETE с фиксированным
количеством параметров
EXECUTE IMMEDIATE с секцией USING
Категория 3
(одна строка)
Запросы (SELECT) с фиксированным количеством
столбцов и параметров, с выборкой одной строки
данных
EXECUTE IMMEDIATE с секциями
USING и INTO
Категория 3
(несколько строк)
Запросы (SELECT) с фиксированным количеством
столбцов и параметров, с выборкой одной или
нескольких строк данных
EXECUTE IMMEDIATE с секциями
USING и BULK COLLECT INTO
или OPEN FOR с динамической строкой
Категория 4 Команда, в которой количество выбранных столб-
цов (для запроса) или количество параметров неизвестно до стадии выполнения
Для категории 4 необходим пакет DBMS_SQL

 

Категория 1

Следующая команда DDL является примером динамического SQL категории 1:

EXECUTE IMMEDIATE 'CREATE INDEX emp_ind_1 on employees (salary, hire_date)';

Команда UPDATE также относится к динамическому SQL категории 1, потому что един­ственным изменяемым аспектом является имя таблицы — параметры отсутствуют:

EXECUTE IMMEDIATE
   'UPDATE ' || l_table || ' SET salary = 10000 WHERE employee_id = 1506'

Категория 2

Если заменить оба жестко фиксированных значения в предыдущей команде DML фор­мальными параметрами (двоеточие, за которым следует идентификатор), появляется динамический SQL категории 2:

EXECUTE IMMEDIATE
   'UPDATE ' || l_table || '
      SET salary = :salary WHERE employee_id = :employee_id'
   USING 10000, 1506;

Секция USING содержит значения, которые будут подставлены в строку SQL после раз­бора и перед выполнением.

Категория 3

Команда динамического SQL категории 3 представляет собой запрос с фиксированным количеством параметров (или вообще без них). Вероятно, чаще всего вы будете создавать команды динамического SQL именно этого типа. Пример:

EXECUTE IMMEDIATE
   'SELECT last_name, salary FROM employees
     WHERE department_id = :dept_id'
         INTO l_last_name, l_salary
    USING 10;

Здесь я запрашиваю всего два столбца из таблицы employees и сохраняю их значения в двух локальных переменных из секции INTO. Также используется один параметр. Так как значения этих компонентов являются статическими на стадии компиляции, я ис­пользую динамический SQL категории 3.

Категория 4

Наконец, рассмотрим самый сложный случай: динамический SQL категории 4. Возьмем предельно обобщенный запрос:

OPEN l_cursor FOR
   'SELECT ' || l_column_list ||
   'FROM employees';

На момент компиляции кода я понятия не имею, сколько столбцов будет запраши­ваться из таблицы employees. Возникает проблема: как написать команду FETCH INTO, которая будет обеспечивать подобную изменчивость? Есть два варианта: либо вернуться к DBMS_SQL для написания относительно тривиального (хотя и объемистого) кода, либо переключиться на исполнение динамических блоков PL/SQL.

К счастью, ситуации, требующие применения категории 4, встречаются редко.

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

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

OraCool аватар
OraCool ответил в теме #9515 4 года 5 мес. назад
Рекомендуется использовать программные переменные во встроенных статических командах SQL в PL/SQL и переменные привязки в динамических командах SQL, чтобы не пре­пятствовать совместному использованию курсоров.
dbstalker аватар
dbstalker ответил в теме #9498 4 года 5 мес. назад
Спасибо! Очень внятное объяснение!!!)
apv аватар
apv ответил в теме #9195 5 года 6 мес. назад
Спасибо за статью!
Doc аватар
Doc ответил в теме #9188 5 года 6 мес. назад
А я ранее долго терзал себя вопросом зачем нужен EXECUTE IMMEDIATE в SQL! ))))
MaxNiko аватар
MaxNiko ответил в теме #9162 5 года 6 мес. назад
Динамический SQL рулит! Здорово, что этим всем можно пользоваться в PL/SQL!
ildergun аватар
ildergun ответил в теме #9142 5 года 6 мес. назад
Спасибо за статью OPEN FOR блестяще объяснен!