Термином «динамический SQL» обозначаются команды SQL, которые конструируются и вызываются непосредственно во время выполнения программы. Статическими называются жестко закодированные команды SQL, которые не изменяются с момента компиляции программы. «Динамическим PL/SQL» называют целые блоки кода PL/SQL, которые строятся динамически, а затем компилируются и выполняются.
Пожалуй, написание динамических команд SQL и динамических программ PL/SQL было самым интересным делом из всего, что я когда-либо делал на языке PL/SQL. Конструирование и динамическое выполнение обеспечивает невероятную гибкость. У разработчика появляется возможность создавать обобщенный код с широким спектром применения. Несмотря на это, динамический SQL следует применять лишь там, где это необходимо; решения со статическим SQL всегда являются предпочтительными, потому что динамические решения более сложны, создают больше проблем с отладкой и тестированием, обычно медленнее работают и усложняют сопровождение. Что же можно делать с динамическими конструкциями SQL и PL/SQL? Лишь несколько идей:
- Выполнение команд DDL. Со статическим кодом SQL в PL/SQL могут выполняться только запросы и команды DML. А если вы захотите создать таблицу или удалить индекс? Используйте динамический 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;
Проще не бывает, верно?
- О Создание хранимой процедуры, выполняющей любую команду DDL:
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;
- Привязка значения
BOOLEAN
, специфического дляPL/SQL
, командойEXECUTE IMMEDIATE
(новая возможность 12c):
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
:
- связывает курсорную переменную с командой SQL, заданной в строке запроса;
- вычисляет значения параметров и заменяет ими формальные параметры в строке запроса;
- выполняет запрос;
- идентифицирует результирующий набор;
- устанавливает курсор на первую строку результирующего набора;
- обнуляет счетчик обработанных строк, возвращаемый атрибутом
%rowcount
. Обратите внимание: параметры подстановки, заданные в секцииUSING
, вычисляются только при открытии курсора. Это означает, что для передачи тому же динамическому запросу другого набора параметров нужно выполнить новую командуOPEN FOR
.
Для выполнения многострочного запроса (то есть запроса, возвращающего набор строк) необходимо:
- объявить тип ref cursor (или использовать встроенный тип
sys_refcursor
); - объявить на его основе курсорную переменную;
- открыть курсорную переменную командой
OPEN FOR
; - с помощью команды
fetch
по одной извлечь записи результирующего набора; - при необходимости проверить значения атрибутов (
%found, %notfound, %rowcount, %isopen)
; - закрыть курсорную переменную обычной командой Как правило, после завершения работы с курсорной переменной следует явно закрыть ее.
Следующая простая программа выводит значения поля заданной таблицы в строках, отбираемых с помощью секции 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, встречаются редко.