Использование динамического SQL - легко и понятно (часть первая EXECUTE IMMEDIATE)

добавлено: 06 сен 13
понравилось:0
просмотров: 52316
комментов: 3

теги:

Автор: Myp3_u_K

Чалышев М.М www.orasource.ru изучаем SQL группа в контакте - присоединяйтесь


Динамический SQL. Часть первая
В некоторых задачах требуется использование динамических запросов. Это случается , когда мы заранее не знаем какой нам нужен SQL запрос, какие обьекты будут использоваться для получения данных.

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

Так же динамический SQL незаменим в случаях ,когда требуется назначить или изменить для определённого пользователя права или привилегии, или роли, или для изменения параметров сессии

Ключевое отличие динамического sql от статического , в том, что команда динамического SQL строится непосредственно во время выполнения процедуры или функции PL/SQL.

Таким образом, команда динамического SQL - это , построенная во время выполнения программы строка SQL запроса , так же такая строка может быть использована во время выполнения анонимного PLSQL блока.

Для выполнения динамических SQL команд , которые возвращают одну строчку рациональнее всего использовать конструкцию EXECUTE IMMEDIATE , так же EXECUTE IMMEDIATE применяется для выполнения динамических команд обновления и изменения данных UPDATE, DELETE, и для выполнения DDL команд.

Использование EXECUTE IMMEDIATE
Оператор EXECUTE IMMEDIATE разбирает и выполняет динамический оператор SQL или анонимный PL / SQL блок. Синтаксис оператора EXECUTE IMMEDIATE следующий
 EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable[, define_variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument
    [, [IN | OUT | IN OUT] bind_argument]...]
[{RETURNING | RETURN} INTO bind_argument[, bind_argument]...];


где dynamic_string это строковое выражение, которое представляет SQL запрос или PL / SQL блок,
define_variable это переменные, которая сохраняет выбранное значение столбца, так же можно использовать вместо данных переменных record тип, определенный пользователем, или %ROWTYPE запись, которая совпадает по формату с выбранной строкой.

Входной bind_argument является выражением, значение которого передается в динамический оператор SQL или PL / SQL блок. bind_argument это переменная, в которую будет сохранятся значение, возвращаемое динамическим оператором SQL или PL / SQL блоком.

Следует помнить, что конструкция EXECUTE IMMEDIATE используется только для запросов которые возвращают одну и только одну строчку, конструкция INTO определяет переменные или записи , куда извлекаются значения выражения SELECT. Результат вывода колонки запроса должен быть совместим по типу для переменной в конструкции в INTO .

Так же EXECUTE IMMEDIATE используется для выполнения DML-инструкций
связыващие аргументы задаются в конструкции USING .
Динамический SQL поддерживает все типы данных SQL. Так, например, задаются связанные переменные они могут быть коллекциями, LOB типами, типами объекта, и ссылками.
Примеры динамического SQL с оператором EXECUTE IMMEDIATE

Следующий PL / SQL блок содержит несколько примеров:
 DECLARE
   sql_stmt    VARCHAR2(200);
   plsql_block VARCHAR2(500);
   emp_id      NUMBER(4) := 7566;
   salary      NUMBER(7,2);
   dept_id     NUMBER(2) := 50;
   dept_name   VARCHAR2(14) := 'PERSONNEL';
   location    VARCHAR2(13) := 'DALLAS';
   emp_rec     emp%ROWTYPE;
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
   sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
   EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
   sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
   EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
   plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
   EXECUTE IMMEDIATE plsql_block USING 7788, 500;
   sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
      RETURNING sal INTO :2';
   EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
   EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
      USING dept_id;
   EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
END;


В приведенном ниже примере, \процедура принимает имя таблицы базы данных (таблица 'emp' ) и дополнительные параметры для WHERE (такие как 'sal > 2000' ). Если опустить условие, процедура удаляет все строки из таблицы. В противном случае, процедура удаляет только те строки, которые удовлетворяют условию.

  CREATE PROCEDURE delete_rows (
   table_name IN VARCHAR2,
   condition IN VARCHAR2 DEFAULT NULL) AS
   where_clause VARCHAR2(100) := ' WHERE ' || condition;
BEGIN
   IF condition IS NULL THEN where_clause := NULL; END IF;
   EXECUTE IMMEDIATE 'DELETE FROM ' || table_name || where_clause;
EXCEPTION
   ...
END;


Использование переменных связки в EXECUTE IMMEDIATE. Конструкция RETURNING
Если динамический INSERT , UPDATE или DELETE возвращают значение ,тогда используйте RETURNING ,вы можете связать выходные данные используя конструкции USING out или RETURNING INTO .

Примеры
 DECLARE
   sql_stmt VARCHAR2(200);
   my_empno NUMBER(4) := 7902;
   my_ename VARCHAR2(10);
   my_job   VARCHAR2(9);
   my_sal   NUMBER(7,2) := 3250.00;
BEGIN
   sql_stmt := 'UPDATE emp SET sal = :1 WHERE empno = :2
      RETURNING ename, job INTO :3, :4';

   /* Bind returned values through USING clause. */
   EXECUTE IMMEDIATE sql_stmt
      USING my_sal, my_empno, OUT my_ename, OUT my_job;

   /* Bind returned values through RETURNING INTO clause. */
   EXECUTE IMMEDIATE sql_stmt
      USING my_sal, my_empno RETURNING INTO my_ename, my_job;
   ...
END;


Параметры , специальные типы, RETURNING, EXECUTE IMMEDIATE

В конструкции RETURNING INTO вы не указываете тип параметра для связки выходных аргументов, потому что, по определению, параметр типа OUT . Ниже приведен пример:

  DECLARE
   sql_stmt VARCHAR2(200);
   dept_id  NUMBER(2) := 30;
   old_loc  VARCHAR2(13);
BEGIN
   sql_stmt := 
      'DELETE FROM dept WHERE deptno = :1 RETURNING loc INTO :2';
   EXECUTE IMMEDIATE sql_stmt USING dept_id RETURNING INTO old_loc;
   ...
END;


В некоторых случаях необходимо указать OUT или IN OUT тип аргументов, передаваемых в качестве параметров используются. Например, предположим, вы хотите вызвать из динамического SQL следующую процедуры:
  CREATE PROCEDURE create_dept (
   deptno IN OUT NUMBER,
   dname  IN VARCHAR2,
   loc    IN VARCHAR2) AS
BEGIN
   SELECT deptno_seq.NEXTVAL INTO deptno FROM dual;
   INSERT INTO dept VALUES (deptno, dname, loc);
END;

Для вызова процедуры из динамического PL / SQL, необходимо обязательно указать IN OUT тип для связывающей переменно, связанной с формальным параметром deptno , вот таким образом:
  DECLARE
   plsql_block VARCHAR2(500);
   new_deptno NUMBER(2);
   new_dname  VARCHAR2(14) := 'ADVERTISING';
   new_loc    VARCHAR2(13) := 'NEW YORK';
BEGIN
   plsql_block := 'BEGIN create_dept(:a, :b, :c); END;';
   EXECUTE IMMEDIATE plsql_block
      USING IN OUT new_deptno, new_dname, new_loc;
   IF new_deptno > 90 THEN ...
END;

Комментарии


  • гуглил EXECUTE IMMEDIATE

    спасибо за статью!

    хотелось бы обсудить или почитать о достоинствах и недостатках конструкции... есть практический опыт использования? поддерживается ли в современных версиях ORACLE \ др СУБД... хочу предварительно собрать информацию и потом решить использовать это или нет и если использовать - то где это уместно, а где - не уместно :)

  • пожалуйста , есть конечно опыт , работает во всех современных версиях СУБД
    Здесь такая штука - рекомендуется использовать динамический SQL по необходимости , то есть там , где без динамического SQL никак нельзя обойтись...
    например динамический поиск , динамический запрос с множеством инвариантных условий... по другим СУБД , скорее нет , я не встречался ...

  • declare
    t_obj c_obj%ROWTYPE;
    begin
    --работает такая конструкция
    EXECUTE IMMEDIATE 'select t.* from c_obj t where t.obj_id=:parmOBJ_ID'
    into t_obj using parmOBJ_ID;
    --работает такая конструкция
    insert into c_obj t values t_obj;
    --работает такая конструкция. b_obj и с_obj таблицы одной структуры
    execute immediate 'insert into b_obj
    select c.* from c_obj c where c.obj_id=:parmOBJ_ID'
    using parmOBJ_ID;
    -- к сожалению не работает такая конструкция
    EXECUTE IMMEDIATE 'insert into c_obj t values :t_obj'
    using t_obj;
    --работает такая конструкция
    update c_obj t set row=t_obj where t.obj_id=parmOBJ_ID;
    -- к сожалению не работает такая конструкция
    EXECUTE IMMEDIATE 'update c_obj t set row=:t_obj where t.obj_id=:parmOBJ_ID'
    using t_obj,parmOBJ_ID;

    Ошибка: PLS-00457: выражения должны иметь тип SQL Текст: using t_obj;

    Ошибка: PLS-00457: выражения должны иметь тип SQL Текст: using t_obj,parmOBJ_ID;

    --И пробовали как это можно обойти??



Необходимо войти на сайт, чтобы оставлять комментарии