Иван Задворьев - Язык PL/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) и тогда какие-то строки останутся несчитанными. Также возможна ситуация, когда число строк в выборке меньше значения счетчика – тогда произойдет повторное считывание последней строки.

Отметим, что повторное считывание из курсора последней строки выборки не приводит к ошибкам. Если в выборке, например, n строк, а команда FETCH выполнена k раз (k>n), то повторные считывания последней (n-й) строки не приведут к инициированию системных исключений, просто последняя строка выборки будет считана и выведена на экран несколько (k-n+1) раз. Для организации перебора строк результирующей выборки предназначены атрибуты явных курсоров, которые рассматриваются далее.

Объявление записей PL/SQL на основе курсоров

Переменная rec, в которую считывались строки результирующей выборки, была объявлена с помощью атрибута %ROWTYPE как запись PL/SQL на основе таблицы tab1. В данном случае это оправдано, потому что в SQL-запросе осуществляется выборка всех столбцов одной таблицы tab1 (SELECT * FROM tab1). Число атрибутов записи PL/SQL будет соответствовать числу столбцов строк выборки и считывание строк пройдет без ошибок.

Однако столбцы результирующей выборки могут быть не из одной, а из нескольких таблиц или вовсе могут являться выражениями:

CURSOR cur_short_person IS

SELECT born,

surname||' '||SUBSTR(name,1,1)||'.'||SUBSTR(secname,1,1)||'.' AS fio,

passport.seria||' '||passport.num AS passport_data

FROM person, passport

WHERE person.id=13243297

AND person.id=passport.r$person

В столбце fio результирующей выборки для каждой строки таблицы person будет результат выражения – фамилия и инициалы (например, Кислов Виктор Михайлович – Кислов В.М.). В столбце passport_data будут паспортные данные из таблицы passport и тоже выражением – серия и номер паспорта, «склеенные» через пробел.

Самый правильный способ определить то, во что будем «принимать» результирующую выборку SQL-запроса курсора – это объявить с помощью атрибута %ROWTYPE переменную-запись PL/SQL, основанную не на схеме одной таблицы, а прямо на курсоре. В этом случае список атрибутов записи PL/SQL по числу столбцов результирующей выборки будет сформирован автоматически.

l_short_person cur_short_person%ROWTYPE;

OPEN cur_short_person;

FETCH cur_short_person INTO l_short_person;

DBMS_OUTPUT.PUT_LINE('ФИО: '||l_short_person.fio);

DBMS_OUTPUT.PUT_LINE('Дата рождения: '||TO_CHAR(l_short_person.born));

DBMS_OUTPUT.PUT_LINE('Паспорт: '||l_short_person.passport_data);

В приведенном коде считывание строки результирующей выборки в запись PL/SQL осуществляется одной короткой командой FETCH без указания столбцов. При появлении новых столбцов во фразе SELECT запроса курсора новые атрибуты также автоматически появятся в записях PL/SQL, объявленных на основе курсора. Таким образом, объявление записей PL/SQL на основе курсоров позволяет писать компактный, поддерживаемый и расширяемый код.

Атрибуты явного курсора

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

Таблица 3.Атрибуты явного курсора.

Атрибут курсора