Иван Задворьев - Язык PL/SQL

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

Неявные курсоры для выборки данных

Неявный курсор для выборки данных используется для команды PL/SQL SELECT INTO, обладающей следующими свойствами:

результирующая выборка SQL-запроса должна содержать ровно одну строку (не ноль строк, не две, не три строки, а ровно одну);

конструкция INTO представляет собой механизм передачи значений столбцов строки выборки в переменные программы PL/SQL.

Рассмотрим пример.

Пусть в базе данных существует таблица tab1, созданная и заполненная следующим образом:

CREATE TABLE tab1 (at1 NUMBER, at2 VARCHAR2(1));

INSERT INTO tab1 VALUES (1, 'A');

INSERT INTO tab1 VALUES (2, 'B');

INSERT INTO tab1 VALUES (3, 'C');

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

SQL> DECLARE

2 l_at1 NUMBER;

3 l_at2 VARCHAR2(1);

4 BEGIN

5 SELECT at1,at2 INTO l_at1,l_at2

6 FROM tab1 WHERE at1=1;

7 DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_at1)||''||l_at2);

8 END;

9 /

1 A

SQL> DECLARE

2 l_at1 NUMBER;

3 l_at2 VARCHAR2(1);

4 BEGIN

5 SELECT at1,at2 INTO l_at1,l_at2

6 FROM tab1 WHERE at1=4;

7 DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_at1)||''||l_at2);

8 END;

9 /

DECLARE

*

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at line 5

SQL> DECLARE

2 l_at1 NUMBER;

3 l_at2 VARCHAR2(1);

4 BEGIN

5 SELECT at1,at2 INTO l_at1,l_at2 FROM tab1

6 WHERE at1 IN (1,2);

7 DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_at1)||''||l_at2);

8 END;

9 /

DECLARE

*

ERROR at line 1:

ORA-01422: exact fetch returns more than requested number of rows

ORA-06512: at line 5

Если SQL-запрос команды SELECT INTO возвращает больше одной строки, то инициируется предопределенное исключение TOO_MANY_ROWS. Если возвращается пустая выборка, то инициируется другое предопределенное исключение – NO_DATA_FOUND. По этой причине команду SELECT INTO рекомендуется помещать в отдельный блок с обработкой этих исключений:

BEGIN

…

BEGIN

SELECT INTO …

EXCEPTION

WHEN TOO_MANY_ROWS THEN …

WHEN NO_DATA_FOUND THEN …

END;

…

END;

Команда SELECT INTO обычно используется тогда, когда есть уверенность, что ее SQL-запрос вернет ровно одну строку, например, для запроса строк таблицы с условием на значение ее первичного ключа.

Явные курсоры

Объявив SQL-запрос с помощью явного курсора, программист получает полный контроль над этапами его выполнения. Можно определить, когда открыть курсор (OPEN), когда считывать из него строки (FETCH) и когда закрыть курсор (CLOSE).

Объявим курсор cur1:

CURSOR cur1 IS SELECT at1,at2 FROM tab1;

Первым шагом работы с курсором является его открытие:

OPEN cur1;

Считывание строк результирующей выборки из курсора выполняется командой FETCH в набор переменных PL/SQL подходящих типов (число переменных должно совпадать с числом столбцов выборки):

FETCH cur1 INTO l_at1, l_at2;

Полностью код для получения трех строк из tab1 выглядит так:

SQL> DECLARE

2 CURSOR cur1 IS SELECT * FROM tab1;

3 rec tab1%ROWTYPE;

4 BEGIN

5 OPEN cur1;

6 FOR i IN 1..3 LOOP

7 FETCH cur1 INTO rec;

8 DBMS_OUTPUT.PUT_LINE(TO_CHAR(rec.at1)||' '||rec.at2);

9 END LOOP;

10 END;

11 /

1 A

2 B

3 C

PL/SQL procedure successfully completed.

После того, как курсор стал ненужным, его следует закрыть:

CLOSE cur1;

Если забыть закрыть явный курсор, как в приведенном выше примере, то можно считать, что запрограммирована утечка памяти в сервере Oracle. Вообще говоря, виртуальная машина PL/SQL автоматически сама закрывает и уничтожает открытые курсоры, как только они оказываются вне области видимости для выполняющегося в настоящий момент блока. Однако делается это не сразу, какое-то время такой курсор существует и остается открытым. У экземпляра Oracle есть ограничение на число одновременно открытых курсоров, которое задается параметром экземпляра open_cursors (по умолчанию параметр выставлен в 300). Если превысить значение этого параметра, то выполнение любого предложения SQL будет завершаться ошибкой. При параллельной работе большого числа сессий это весьма вероятно, поэтому чтобы не сталкиваться с ошибками такого вида, настоятельно рекомендуется аккуратно закрывать курсоры.

Программа, представленная выше, неудачна еще и тем, что цикл FOR со счетчиком предусматривает считывание конкретного числа строк, которых, вообще говоря, может и не быть в результирующей выборке. Строк в выборке может быть больше, чем указано (в данном случае у цикла FOR счетчик изменяется до 3) и тогда какие-то строки останутся несчитанными. Также возможна ситуация, когда число строк в выборке меньше значения счетчика – тогда произойдет повторное считывание последней строки.