Курсоры

Управление курсором

Создание курсора

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

В PL/SQL поддерживаются два типа курсоров: явные и неявные.

Явный курсор объявляется разработчиком, а неявный курсор не требует объявления.

Курсор может возвращать одну строку, несколько строк или ни одной строки.

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

Курсор может быть объявлен в секциях объявлений любого блока PL/SQL, подпрограммы или пакета.

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

Оператор CURSOR выполняет объявление явного курсора .

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

Оператор FETCH выполняет последовательное извлечение строк из результирующего набора от начала до конца.

Оператор CLOSE закрывает курсор и освобождает занимаемые им ресурсы.

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

CURSOR cursor_name 
    [(parameter[,parameter]...)]
    [RETURN return_type]
    IS select_statement;

Каждый параметр parameter определяется как:

cursor_parameter_name [IN] 
	datatype [{:= | DEFAULT} expr]

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

Одновременно с созданием результирующего набора можно выполнить блокировку выбираемых строк. Для этого в операторе SELECT следует указать фразу FOR UPDATE.

Для задания параметров курсора подходит как позиционная, так и именованная нотация.

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

• %ISOPEN - возвращает значение TRUE, если курсор открыт.
• %FOUND - определяет, найдена ли строка, удовлетворяющая условию.
• %NOTFOUND - возвращает TRUE, если строка не найдена.
• %ROWCOUNT - возвращает номер текущей строки.

Оператор FETCH может быть выполнен в цикле LOOP-END LOOP. Это позволяет последовательно просматривать весь результирующий набор, который был открыт оператором OPEN .

Например:

DECLARE
   CURSOR c1 IS SELECT f1, f2, f3, f4
   FROM tbl1
   WHERE f4 > 100;
   CURSOR c2 RETURN tbl2%ROWTYPE IS
                    SELECT * FROM tbl2
                    WHERE f1_t2 = 10;
                    - Список параметров
                    - курсора
   CURSOR c3 (p1 INTEGER DEFAULT 10,
               p2 INTEGER DEFAULT 1300)
               IS SELECT f1, f2, f3, f4
               FROM tbl1
               WHERE f4 > p1 AND f2 = p2;
- ...
BEGIN
   OPEN c1;   - Открытие курсора c1
LOOP
   - Выборка одной строки
   FETCH c1 INTO rec1;
   - Строка успешно выбрана
   EXIT WHEN c1%NOTFOUND;
END LOOP;
- Закрытие курсора
CLOSE c1;
- Открытие курсора c3
OPEN c3(10,700);
- ...
END;

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

При выполнении SQL-оператора, для которого не был объявлен явный курсор, Oracle автоматически открывает неявный курсор.

При применении неявного курсора нельзя использовать операторы управления курсором OPEN , FETCH и CLOSE .

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

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

Объявление курсора при создании пакета может иметь следующее формальное описание:

CURSOR cursor_name [(parameter
	[, parameter]...)]
RETURN return_type;

Например:

- Создание пакета
CREATE PACKAGE p1 AS
   - Объявление курсора
   CURSOR c1 RETURN tbl1%ROWTYPE;
END p1;
- Создание тела пакета
CREATE PACKAGE BODY p1 AS
- Спецификация курсора
CURSOR c1 RETURN tbl1%ROWTYPE
      IS SELECT * FROM tbl1
      WHERE f3 > 700;
END p1;