Мы рассмотрели несколько примеров использования параметров с NDS
. Давайте познакомимся с различными правилами и специальными ситуациями, которые могут вам встретиться при передаче параметров.
SQL
-запросу могут передаваться только выражения (литералы, переменные, сложные выражения), заменяющие формальные параметры в строке значениями данных. Не допускается передача имен элементов схемы (таблиц, столбцов и т. д.) или целых фрагментов кода SQL
(например, условий WHERE
), которые должны строиться посредством конкатенации.
Допустим, вы хотите создать процедуру для очистки заданного представления или таблицы. Первая версия может выглядеть примерно так:
PROCEDURE truncobj (
nm IN VARCHAR2,
tp IN VARCHAR2 := 'TABLE',
sch IN VARCHAR2 := NULL)
IS
BEGIN
EXECUTE IMMEDIATE
'TRUNCATE :trunc_type :obj_name'
USING tp, NVL (sch, USER) || '.' || nm;
END;
На первый взгляд все выглядит вполне разумно, но при попытке выполнения этой процедуры вы получите сообщение об ошибке:
ORA-03290: Invalid truncate command - missing CLUSTER or TABLE keyword
При упрощении процедуры до следующего вида:
EXECUTE IMMEDIATE 'TRUNCATE TABLE :obj_name' USING nm;
сообщение об ошибке изменится:
ORA-00903: invalid table name
Почему же в NDS
(как, впрочем, и в пакете DBMS_SQL
) имеется такое ограничение? При передаче строки команде EXECUTE IMMEDIATE
исполняющее ядро должно прежде всего выполнить синтаксический анализ команды, чтобы убедиться в правильности ее определения. PL/SQL может определить, что следующая команда определена правильно, даже не зная значения параметра :xyz:
'UPDATE emp SET sal = :xyz'
Но корректность следующий команды PL/SQL
проверить не сможет:
'UPDATE emp SET :col_name = :xyz'
По этой причине в данной ситуации необходимо использовать конкатенацию:
PROCEDURE truncobj (
nm IN VARCHAR2,
tp IN VARCHAR2:= 'TABLE',
sch IN VARCHAR2 := NULL)
BEGIN
EXECUTE IMMEDIATE
'TRUNCATE ' || tp || ' ' || NVL (sch, USER) || '.' || nm;
END;
Режимы передачи параметров в SQL
При передаче значений параметров команды SQL
можно использовать один из трех режимов: IN (
только чтение, действует по умолчанию), OUT
(только запись) или IN OUT
(чтение и запись). При выполнении динамического запроса все параметры команды SQL
, за исключением параметра в секции RETURNING
, должны передаваться в режиме IN
:
PROCEDURE wrong_incentive (
company_in IN INTEGER,
new_layoffs IN NUMBER
)
IS
sql_string VARCHAR2(32767);
sal_after_layoffs NUMBER;
BEGIN
sql_string :=
'UPDATE ceo_compensation
SET salary = salary + 10 * :layoffs
WHERE company_id = :company
RETURNING salary INTO :newsal';
EXECUTE IMMEDIATE sql_string
USING new_layoffs, company_in, OUT sal_after_layoffs;
DBMS_OUTPUT.PUT_LINE (
'CEO compensation after latest round of layoffs $' || sal_after_layoffs);
END;
END;
Параметры подстановки команды SQL
, передаваемые в режимах OUT
и IN OUT
, используются прежде всего при выполнении динамического PL/SQL
. В этом случае режимы передачи параметров соответствуют аналогичным режимам обыкновенных программ PL/SQL
, а также использованию переменных в динамических блоках PL/SQL
. Несколько общих рекомендаций, касающихся использования секции USING при выполнении динамического PL/SQL
:
- В качестве параметра подстановки, передаваемого в режиме
IN
, может быть задан любой элемент соответствующего типа: литеральное значение, именованная константа, переменная или сложное выражение. Такой элемент сначала вычисляется, а затем передается в динамический блокPL/SQL
. - Для значения параметра динамической команды в режиме
OUT
илиIN OUT
следует объявить переменную. - Значения можно подставлять только вместо тех параметров динамического блока
PL/SQL
, тип которых поддерживаетсяSQL
. Например, если параметр процедуры имеет типBOOLEAN,
его значение нельзя задать или считать с помощью секцииUSING
.
Это ограничение частично снято в версии 12.1 и выше. Теперь разрешается подстановка многих типов PL/SQL
, включая типы записей и коллекций, но подстановка BOOLEAN
по-прежнему запрещена.
Давайте рассмотрим механизм передачи параметров на примерах. Вот заголовок процедуры с параметрами IN, OUT и IN OUT
:
PROCEDURE analyze_new_technology (
tech_name IN VARCHAR2,
analysis_year IN INTEGER,
number_of_adherents IN OUT NUMBER,
projected_revenue OUT NUMBER
)
Пример блока с динамическим вызовом этой процедуры:
DECLARE
devoted_followers NUMBER;
est_revenue NUMBER;
BEGIN
EXECUTE IMMEDIATE
'BEGIN
analyze_new_technology (:p1, :p2, :p3, :p4); END;'
USING 'Java', 2002, IN OUT devoted_followers, OUT est_revenue;
END;
Поскольку процедура имеет четыре параметра, в секции USING
также должно быть указано четыре элемента. Для первых двух параметров, передаваемых в режиме IN
, следует задать литеральные значения или выражения, а следующие два элемента должны быть именами переменных, так как для них заданы режимы OUT
и IN OUT
.
Но что, если два и более формальных параметра имеют одинаковые имена?
Дублирование формальных параметров
При выполнении динамической команды SQL
связь между формальными и фактическими параметрами устанавливается в соответствии с их позициями. Однако интерпретация одноименных параметров зависит от того, какой код используется — SQL
или PL/SQL
.
- При выполнении динамической команды
SQL (DML
- или DDL-строки, не оканчивающейся точкой с запятой) параметр подстановки нужно задать для каждого формального параметра, даже если их имена повторяются. - При выполнении динамического блока
PL/SQL
(строки, оканчивающейся точкой с запятой) нужно указать параметр подстановки для каждого уникального формального параметра.
Далее приведен пример динамической команды SQL
с повторяющимися формальными параметрами. Особое внимание обратите на повторяющийся параметр подстановки val_in
в секции USING
:
PROCEDURE updnumval (
col_in IN VARCHAR2,
start_in IN DATE, end_in IN DATE,
val_in IN NUMBER)
IS
dml_str VARCHAR2(32767) :=
'UPDATE emp SET ' || col_in || ' = :val
WHERE hiredate BETWEEN :lodate AND :hidate
AND :val IS NOT NULL';
BEGIN
EXECUTE IMMEDIATE dml_str
USING val_in, start_in, end_in, val_in;
END;
А вот динамический блок PL/SQL
с повторяющимися формальными параметрами — для него в секции USING
параметр val_in
задан только один раз:
PROCEDURE updnumval (
col_in IN VARCHAR2,
start_in IN DATE, end_in IN DATE,
val_in IN NUMBER)
IS
dml_str VARCHAR2(32767) :=
'BEGIN
UPDATE emp SET ' || col_in || ' = :val
WHERE hiredate BETWEEN :lodate AND :hidate
AND :val IS NOT NULL;
END;';
BEGIN
EXECUTE IMMEDIATE dml_str
USING val_in, start_in, end_in;
END;
Передача значений NULL
При передаче NULL
в качестве параметра подстановки — например, как в команде:
EXECUTE IMMEDIATE
'UPDATE employee SET salary = :newsal
WHERE hire_date IS NULL'
USING NULL;
вы получите сообщение об ошибке. Дело в том, что NULL
не имеет типа данных и поэтому не может являться значением одного из типов данных SQL.
Что же делать, если вам потребуется передать в динамический код значение NULL
? Это можно сделать двумя способами.
Во-первых, значение можно скрыть в переменной, для чего проще всего использовать неинициализированную переменную подходящего типа:
DECLARE
/* Исходным значением переменной по умолчанию является NULL */
no_salary_when_fired NUMBER;
BEGIN
EXECUTE IMMEDIATE
'UPDATE employee SET salary = :newsal
WHERE hire_date IS NULL'
USING no_salary_when_fired;
END;
Во-вторых, с помощью функции преобразования типа можно явно преобразовать NULL
в типизированное значение:
BEGIN
EXECUTE IMMEDIATE
'UPDATE employee SET salary = :newsal WHERE hire_date IS NULL'
USING TO_NUMBER (NULL);
END;