Курсоры

Использование курсора в цикле FOR

Вместо управления курсором операторами OPEN , FETCH и CLOSE язык PL/SQL предоставляет возможность последовательной обработки курсора в цикле FOR.

Цикл FOR с курсором выполняет следующие действия:

1. Неявно объявляет переменную цикла как запись %ROWTYPE.
2. Открывает курсор.
3. При каждой итерации извлекает следующую строку из результирующего набора в поля неявно объявленной записи.
4. По достижении конца результирующего набора закрывает курсор.

Например:

DECLARE
   CURSOR c1 IS
      SELECT f1, f2 FROM tbl2
      WHERE f1 = 10;
BEGIN
   - Неявное объявление rec1
   FOR rec1 IN c1 LOOP
         /* Выбрана следующая
            строка таблицы */
         INSERT INTO temp_tbl
         VALUES (rec1.f2);
   END LOOP;
   COMMIT;
END;

Тип REF CURSOR

При объявлении переменной курсора создается указатель типа REF CURSOR . Переменная типа REF CURSOR может передаваться через механизм RPC (вызовы удаленных процедур) между клиентским приложением и сервером Oracle. При использовании переменных курсора для передачи результирующих наборов между хранимыми подпрограммами PL/SQL и различными клиентскими приложениями каждое из приложений разделяет указатель на рабочую область, в которой расположен результирующий набор. Это позволяет одновременно ссылаться на одну и ту же рабочую область как клиентским приложениям, разработанным в Oracle Forms, в Visual Studio или в Delphi, так и OCI-клиентам или серверу Oracle.

Создание переменной курсора выполняется в два этапа: сначала определяется тип REF CURSOR , а затем объявляется переменная этого типа.

Определить тип REF CURSOR можно в любых блоках PL/SQL, подпрограммах или пакетах.

Определение типа REF CURSOR может иметь следующее формальное описание:

TYPE ref_type_name IS REF
CURSOR [ RETURN return_type ];

Параметр ref_type_name задает имя создаваемого типа, а параметр return_type должен определять запись или строку таблицы базы данных.

Если опция RETURN не указана, то переменную курсора можно использовать более гибко, ссылаясь на различные запросы, которые имеют разные типы записей. Однако применение опции RETURN обеспечивает более высокий уровень надежности, позволяя компилятору PL/SQL выполнять проверку совместимости типа переменной курсора с типом результатов запроса. Переменная курсора не может быть сохранена в базе данных.

Переменные курсора в PL/SQL аналогичны указателям языка C++.

При выполнении SQL-запроса Oracle создает неименованную рабочую область, в которой содержится сформированный результирующий набор. Для доступа к этому результирующему набору может использоваться:

• явный курсор, который именует рабочую область;
• переменная курсора, которая указывает на эту рабочую область.

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

Рабочая область будет оставаться доступной до тех пор, пока на нее ссылается хотя бы одна переменная курсора.

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

Для управления переменной курсора используются операторы OPEN-FOR, FETCH и CLOSE .

Оператор OPEN-FOR связывает переменную курсора с запросом, выполняет запрос и получает результирующий набор.

Оператор OPEN-FOR может иметь следующее формальное описание:

OPEN {cursor_variable_name |
:host_cursor_variable_name}
FOR select_statement;

Параметр указывает переменную курсора host_cursor_variable_name, которая была объявлена как переменная основного языка, если блок PL/SQL выполняется в режиме встроенного SQL. Для ссылки на хост-переменную перед ней необходимо указывать символ двоеточия.

Переменная курсора, в отличие от самого курсора, не может иметь параметров.

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

• хост-переменные;
• переменные PL/SQL;
• параметры;
• функции.

Запрос, на который ссылается переменная курсора, не может содержать фразу FOR UPDATE.

Для переменной курсора можно использовать атрибуты %FOUND , %NOTFOUND , %ISOPEN и %ROWCOUNT .

Например:

DECLARE
   TYPE VarCur IS REF CURSOR
   RETURN tbl1%ROWTYPE;
   - Объявление переменной курсора
   t1 VarCur;  
BEGIN
   IF NOT t1%ISOPEN THEN
      - Открываем
      - переменную курсора
      OPEN t1 FOR SELECT *
      FROM tbl1;
      OPEN t1 FOR SELECT *
      FROM tbl1
      - Повторно открываем
      - переменную курсора
                WHERE f2>100;
   END IF;

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

Если попытаться повторно открыть уже открытый курсор, то Oracle инициирует исключение CURSOR_ALREADY_OPEN.

Если переменная курсора объявляется как формальный параметр подпрограммы, открывающей переменную курсора, то такой параметр должен быть указан с опцией IN OUT.

Следующий пример демонстрирует применение в качестве переменной хост-переменной основного языка программирования.

/* Объявление хост-переменных */
EXEC SQL BEGIN DECLARE
   /* Объявление переменной курсоров */
   SQL_CURSOR cur1;
EXEC SQL END DECLARE SECTION;
/* Инициализация хост-переменной */
EXEC SQL ALLOCATE : cur1;
EXEC SQL EXECUTE
/* Передача хост-переменной
   курсора в блок PL/SQL */
BEGIN
      OPEN :cur1 FOR SELECT *
      FROM emp;
      - :
      OPEN :cur1 FOR SELECT *
      FROM temp_emp;
END;
END-EXEC;

Применение переменных курсора имеет ряд следующих ограничений:

• переменные курсора не могут быть объявлены в пакете (и сохранены в базе данных);
• нельзя использовать механизм RPC для передачи переменных курсора между различными серверами;
• удаленная подпрограмма не может получать значения от переменных курсора с другого сервера;
• запрос, указываемый для переменной курсора оператором OPEN-FOR, не должен содержать фразы FOR UPDATE ;
• для переменных курсора не применимы операторы равенства, сравнения или эквивалентности значению NULL ;
• переменной курсора не может быть присвоено значение NULL ;
• типы REF CURSOR не могут использоваться для определения типа столбцов в SQL-операторах CREATE TABLE и CREATE VIEW, а также для определения типов элементов коллекций;
• переменные курсора не могут быть использованы в динамическом SQL;
• переменную курсора нельзя использовать вместо курсора в цикле FOR-IN-LOOP.

Выборка строк из результирующего набора выполняется оператором FETCH , который может иметь следующее формальное описание:

FETCH {cursor_variable_name |
	:host_cursor_variable_name}
          INTO {variable_name
          [, variable_name]... |
          record_name};

Например:

DECLARE
   TYPE VarCur IS REF CURSOR
   RETURN tbl1%ROWTYPE;
   - Объявление переменной курсора
   t1 VarCur;
   tbl1_rec tbl1%ROWTYPE;
BEGIN
   IF NOT t1%ISOPEN THEN
   - Открываем
   - переменную курсора
      OPEN t1 FOR SELECT * FROM tbl1; 
      LOOP
         - Извлечение строки
         FETCH t1 INTO tbl1_rec;
         - Проверка атрибута
         EXIT WHEN t1%NOTFOUND;
      END LOOP;
   END IF;
   CLOSE t1;