Мы рассмотрели несколько примеров использования параметров с 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;
