Динамический SQL в Oracle - легко и понятно (часть третья - BULK, операции с множествами)

добавлено: 08 сен 13
понравилось:0
просмотров: 15404
комментов: 0

теги:

Автор: Myp3_u_K

Чалышев Максим Михайлович
Учебный тренинг. SQL от новичка до профессионала.
Бесплатные авторские видеокурсы SQL, PLSQL, JAVA

Использование множеств при работе с динамическим SQL

Работа со сложными типами данных BULK– массивами и коллекциями повышает производительность за счет минимизации количества переключений контекста между PL / SQL и SQL. То есть целые коллекции, а не только отдельные элементы, передаются туда и обратно.

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

Синтаксис
BULK FETCH statement
BULK EXECUTE IMMEDIATE statement
FORALL statement
COLLECT INTO clause
RETURNING INTO clause
%BULK_ROWCOUNT cursor attribute

Синтаксис для динамического связывания с использованием инструкции BULK

Инструкция BULK позволяет Oracle работать переменной в SQL , так , чтобы обрабатывать множества значений. Тип множества может быть любой допустимы в PL / SQL (индекс-таблицы, вложенные таблицы, и массивы переменного размера). Однако элементы должны иметь строго заданный тип данных SQL, CHAR , DATE или NUMBER . Динамические коллекции могут применяться в командах: EXECUTE IMMEDIATE , FETCH и FORALL .

EXECUTE IMMEDIATE и BULK

Эти операторы позволяет вам позволяет связывать заданные переменные или OUT аргументы и передавать их, как параметры, для динамического оператора SQL. Синтаксис имеет следующий вид:
EXECUTE IMMEDIATE dynamic_string
   [[BULK COLLECT] INTO define_variable[, define_variable ...]]
   [USING bind_argument[, bind_argument ...]]
   [{RETURNING | RETURN} 
   BULK COLLECT INTO bind_argument[, bind_argument ...]];

Благодаря инструкции BULK можно возвращать данные в коллекции из динамического SQL.

BULK и FETCH
BULK позволяет выбрать данные из динамического курсора так же, как выборку из статического курсора. Синтаксис имеет следующий вид:
FETCH dynamic_cursor 
  BULK COLLECT INTO define_variable [, define_variable ...];


Если число определяемых переменных в BULK COLLECT INTO , превышает количество столбцов в запросе, Oracle выдает ошибку.

Использование FORALL

Этот синтаксис позволяет связать входные переменные в динамическом SQL. Кроме того, вы можете использовать EXECUTE
IMMEDIATE совместно с FORALL .Синтаксис имеет следующий вид:
  FORALL index IN lower bound..upper bound
   EXECUTE IMMEDIATE dynamic_string
   USING bind_argument | bind_argument(index)
      [, bind_argument | bind_argument(index)] ...
   [{RETURNING | RETURN} BULK COLLECT 
      INTO bind_argument[, bind_argument ... ]];


Динамическая строка может быть или INSERT , UPDATE или DELETE (но не SELECT).
Примеры динамического SQL с использованием BULK

Следующий пример демонстрирует использование BULK для вывода данных в открытые массивы с помощью FETCH
 DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   TYPE NumList IS TABLE OF NUMBER;
   TYPE NameList IS TABLE OF VARCHAR2(15);
   emp_cv EmpCurTyp;
   empnos NumList;
   enames NameList;
   sals   NumList;
BEGIN
   OPEN emp_cv FOR 'SELECT empno, ename FROM emp';
   FETCH emp_cv BULK COLLECT INTO empnos, enames;
   CLOSE emp_cv;

   EXECUTE IMMEDIATE 'SELECT sal FROM emp'
      BULK COLLECT INTO sals;
END;



Использование BULK массовом обновлении записей совместно с returning результат помещается в коллекцию enames.

DECLARE
   TYPE NameList IS TABLE OF VARCHAR2(15);
   enames    NameList;
   bonus_amt NUMBER := 500;
   sql_stmt  VARCHAR(200);
BEGIN
   sql_stmt := 'UPDATE emp SET bonus = :1 RETURNING ename INTO :2';
   EXECUTE IMMEDIATE sql_stmt
      USING bonus_amt RETURNING BULK COLLECT INTO enames;
END;


Пример показывает как использовать FORALL и USING ..
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   TYPE NameList IS TABLE OF VARCHAR2(15);
   empnos NumList;
   enames NameList;
BEGIN
   empnos := NumList(1,2,3,4,5);
   FORALL i IN 1..5
      EXECUTE IMMEDIATE
        'UPDATE emp SET sal = sal * 1.1 WHERE empno = :1
         RETURNING ename INTO :2'
         USING empnos(i) RETURNING BULK COLLECT INTO enames;
   ...
END;

Комментарии




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