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

Отметим, что повторное считывание из курсора последней строки выборки не приводит к ошибкам. Если в выборке, например, 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.Атрибуты явного курсора.

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

Описание атрибута

%FOUND

TRUE, если из курсора считана очередная строка

%NOTFOUND

FALSE, если из курсора считана очередная строка

%ROWCOUNT

количество считанных до настоящего момента строк

%ISOPEN

TRUE, если курсор открыт

Основная нагрузка при считывании всех строк результирующей выборки ложится на атрибуты курсора %NOTFOUND и %FOUND, которые всегда находятся в связке – либо принимают противоположные логические значения TRUE и FALSE, либо оба UNKNOWN.

Атрибут %FOUND равен TRUE и атрибут %FOUND равен FALSE в то время, пока команда FETCH считывает из курсора все новые и новые (очередные) строки. После того, как последняя строка результирующей выборки будет считана дважды (второй раз, выходит, уже не как очередная), атрибут курсора %FOUND станет FALSE, а %NOTFOUND станет TRUE. На этом поведении атрибутов курсора обычно и формируется условие выхода из циклов, предназначенных для считывания из курсора всех строк результирующей выборки.

Еще одним важным фактом является то, что после открытия курсора, но до выполнения первой команды FETCH, атрибуты %FOUND и %NOTFOUND имеют неопределенное логическое значение (UNKNOWN). Если это не учитывать, то можно совершить одну из распространенных ошибок – в цикле WHILE с условием на истинность атрибута %FOUND цикл не будет выполнен ни разу, несмотря на то, что в результирующей выборке есть строки. Выполнить команду FETCH первый раз надо еще до входа в цикл, тем самым проинициализировав атрибуты курсора.

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

SQL> DECLARE

2 CURSOR cur1 IS SELECT * FROM tab1;

3 rec cur1%ROWTYPE;

4 BEGIN

5 OPEN cur1;

6 FETCH cur1 INTO rec;

7 WHILE cur1%FOUND LOOP