Передача параметров в команды SQL на PL/SQL: IN, OUT

Передача параметров IN OUT в PL/SQL Мы рассмотрели несколько примеров использования параметров с 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;

 

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

Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 1970 просмотров Rasen Fasenger Mon, 22 Oct 2018, 04:44:08
Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 3747 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 2947 просмотров Doctor Thu, 12 Jul 2018, 08:41:33
Символьные функции и аргументы...
Символьные функции и аргументы... 6141 просмотров Анатолий Wed, 23 May 2018, 18:54:01

Войдите чтобы комментировать

Borberd аватар
Borberd ответил в теме #9233 25 сен 2018 03:42
Режимы передачи параметров отражены очень хорошо в вашем блоге. Да, примеры - супер!
Myk аватар
Myk ответил в теме #9223 19 сен 2018 05:17
IN OUT в деталях. Теперь понятно, как передавать аргументы и параметры в PL/SQL/ Спасибо!
apv аватар
apv ответил в теме #9169 03 сен 2018 16:58
Поддерживаю, отличная статья по параметрам SQL для программиста. Примеры потрясающие!)
Doc аватар
Doc ответил в теме #9168 03 сен 2018 16:57
Отличные примеры, спасибо за блог!