Joomla 3.1 Templates by FatCow Coupon

Динамический SQL в Oracle - легко и понятно (часть вторая - курсоры FETCH , Close , Open FOR )

Категория: Блог Опубликовано 24.05.2017
Использование курсоров в динамическом SQL, команды FETCH и CLOSE
Для работы с динамическим запросом который возвращает несколько строк используются курсоры . Работа с курсорами - это всего три команды: OPEN-FOR , FETCH и CLOSE . 

Сначала , вы с помощью команды OPEN, открываете переменную курсора, далее в цикле FOR последовательно считываете результат запроса из нескольких строк запроса. В каждой итерации цикла FOR, вызывается команда FETCH которая считывает из результирующего набора по одной записи. Когда все строки обрабатываются, вы закрываете курсор вызывая метод CLOSE для переменной курсора. 

Открытие переменной курсора
Команды OPEN-FOR связыавют переменную курсора с запросом в курсоре, выполняют данный запрос, определяют набор результатов, и устанавливает курсор на первую строку в наборе результатов, количество строк которые обработаны сохраняется в %ROWCOUNT .
В отличие от статических форм курсора OPEN-FOR , динамическая форма имеет специальную секцию USING. Она нужна, чтобы во время выполнения, связать переменные в USING и внешние переменные PL SQL программы

Синтаксис
OPEN {cursor_variable | :host_cursor_variable} FOR dynamic_string
   [USING bind_argument[, bind_argument]...];


где cursor_variable является типизированной переменной курсора (один без обратного типа), host_cursor_variable находится курсор переменной, объявленной в PL / SQL и dynamic_string это строковое выражение, представляющее запрос SQL .

В следующем примере, вы объявляете переменную курсора, а затем связываете эту переменную с динамическим опратором SELECT , который возвращает строки из таблицы emp :

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;  -- define weak REF CURSOR type
   emp_cv   EmpCurTyp;  -- declare cursor variable
   my_ename VARCHAR2(15);
   my_sal   NUMBER := 1000;
BEGIN
   OPEN emp_cv FOR  -- open cursor variable
      'SELECT ename, sal FROM emp WHERE sal > :s' USING my_sal;
   ...
END;


Любая привязка аргументов в запросе выполняется только тогда, когда переменная курсора открыта. Таким образом, для выборки из курсора с использованием различных значений привязки, необходимо повторно открыть курсор с привязкой новых переменных, чтобы передать их новые значения.

Извлечение данных из курсора

FETCH оператор возвращает одну строку из результирующего набора, присваивает значения списка выбора элементов соответствующих переменных или полей в инструкции INTO , увеличивает счетчик %ROWCOUNT , и перемещает указатель к следующей строке. Синтаксис:

FETCH {cursor_variable | :host_cursor_variable}
   INTO {define_variable[, define_variable]... | record};


LOOP
   FETCH emp_cv INTO my_ename, my_sal;  -- fetch next row
   EXIT WHEN emp_cv%NOTFOUND;  -- exit loop when last row is fetched
   -- process row
END LOOP;


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

При попытке выборки из данных из закрытого курсора, PL / SQL возбуждает предопределенное исключение INVALID_CURSOR .
Закрытие переменной курсора
Курсора CLOSE закрывает переменную курсора. После этого соответствующий набор результатов не определено. 

Синтаксис имеет следующий вид:

CLOSE {cursor_variable | :host_cursor_variable};

В приведенном примере, после обработки последней строки курсора , вы закрываете переменную курсора emp_cv :
LOOP
   FETCH emp_cv INTO my_ename, my_sal;
   EXIT WHEN emp_cv%NOTFOUND;
   -- process row
END LOOP;
CLOSE emp_cv;  -- закрываем переменную курсора


Если вы попытаетесь закрыть уже закрытый или не открытый курсор, PL / SQL вызывает исключение INVALID_CURSOR .
Примеры динамического SQL - записи, объекты и коллекции
Как показывает следующий пример , вы можете извлечь строки из динамического запроса в переменную типа запись ROWTYPE.
DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   emp_cv   EmpCurTyp;
   emp_rec  emp%ROWTYPE;
   sql_stmt VARCHAR2(200);
   my_job   VARCHAR2(15) := 'CLERK';
BEGIN
   sql_stmt := 'SELECT * FROM emp WHERE job = :j';
   OPEN emp_cv FOR sql_stmt USING my_job;
   LOOP
      FETCH emp_cv INTO emp_rec;
      EXIT WHEN emp_cv%NOTFOUND;
      -- process record
   END LOOP;
   CLOSE emp_cv;
END;


Следующий пример иллюстрирует использование объектов и коллекций. Предположим, вы определили тип объекта Person и VARRAY типа Hobbies , а именно:
CREATE TYPE Person AS OBJECT (name VARCHAR2(25), age NUMBER);
CREATE TYPE Hobbies IS VARRAY(10) OF VARCHAR2(25);

Теперь, используя динамический SQL, вы можете создать пакет, который использует эти типы:

CREATE PACKAGE teams AS
   PROCEDURE create_table (tab_name VARCHAR2);
   PROCEDURE insert_row (tab_name VARCHAR2, p Person, h Hobbies);
   PROCEDURE print_table (tab_name VARCHAR2);
END;

CREATE PACKAGE BODY teams AS
   PROCEDURE create_table (tab_name VARCHAR2) IS
   BEGIN
      EXECUTE IMMEDIATE 'CREATE TABLE ' || tab_name || 
         ' (pers Person, hobbs Hobbies)';
   END;

   PROCEDURE insert_row (
      tab_name VARCHAR2,
      p Person,
      h Hobbies) IS
   BEGIN
      EXECUTE IMMEDIATE 'INSERT INTO ' || tab_name || 
         ' VALUES (:1, :2)' USING p, h;
   END;

   PROCEDURE print_table (tab_name VARCHAR2) IS
      TYPE RefCurTyp IS REF CURSOR;
      cv RefCurTyp;
      p  Person;
      h  Hobbies;
   BEGIN
      OPEN cv FOR 'SELECT pers, hobbs FROM ' || tab_name;
      LOOP
         FETCH cv INTO p, h;
         EXIT WHEN cv%NOTFOUND;
         -- print attributes of 'p' and elements of 'h'
      END LOOP;
      CLOSE cv;
   END;
END;

В анонимном PL / SQL блоке, вы могли бы вызвать процедуры из пакета teams ,:

DECLARE
   team_name VARCHAR2(15);
   ...
BEGIN
   ...
   team_name := 'Notables';
   teams.create_table(team_name);
   teams.insert_row(team_name, Person('John', 31), 
      Hobbies('skiing', 'coin collecting', 'tennis'));
   teams.insert_row(team_name, Person('Mary', 28), 
      Hobbies('golf', 'quilting', 'rock climbing'));
   teams.print_table(team_name);
END;
 
Просмотров: 141