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

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

%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

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

9 FETCH cur1 INTO rec;

10 END LOOP;

11 CLOSE cur1;

12 END;

13 /

1 A

2 B

3 C

PL/SQL procedure successfully completed.

Еще один пример показывает использование атрибутов курсора для считывания всех строк выборки в простом цикле LOOP END LOOP с условием выхода EXIT WHEN.

SQL> DECLARE

2 TYPE tab1_rec_type IS RECORD

3 (arg1 tab1.at1%TYPE,

4 arg2 tab1.at2%TYPE);

5 tab1_rec tab1_rec_type;

6 CURSOR cur1 IS SELECT * FROM tab1;

7 BEGIN

8 OPEN cur1;

9 LOOP

10 EXIT WHEN (cur1%NOTFOUND);

11 FETCH cur1 INTO tab1_rec;

12 DBMS_OUTPUT.PUT_LINE(cur1%ROWCOUNT||' '||tab1_rec.arg2);

13 END LOOP;

14 CLOSE cur1;

15 END;

/

1 A

2 B

3 C

3 C

PL/SQL procedure successfully completed.

Обратите внимание на повторный вывод последней строки (3 С). Это еще одна распространенная ошибка. В ходе проведения занятий со студентами авторы десятки раз видели считывание и обработку последней строки выборки дважды. Системное исключение при повторном считывании последней строки выборки, напомним, не инициируется, поэтому такие ошибки в коде трудно обнаруживаются.

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

цикл считывания не выполняется ни разу;

последняя строка выборки в цикле обрабатывается дважды.

В приведенном выше примере показано, что значение %ROWCOUNT увеличивается на единицу с каждой считанной строкой, а не отражает общее число отобранных SQL-запросом строк. Видно и что повторное считывание последней строки выборки не влияет на значение атрибута %ROWCOUNT: оно остается равным значению, присвоенному при первом считывании последней строки. В примере значение атрибута %ROWCOUNT как стало равным трем при первом считывании последней строки, так и осталось без изменений после еще одного считывания.

Курсорный цикл FOR

Курсорный цикл FOR позволяет в цикле обработать все строки результирующей выборки SQL-запроса.

SQL> DECLARE

2 CURSOR cur1 IS SELECT at1,at2 FROM tab1;

3 v1 VARCHAR2(4000);

4 BEGIN

5 FOR rec IN cur1 LOOP

6 v1:=LTRIM(v1||' '||rec.at2);

7 END LOOP;

8 DBMS_OUTPUT.PUT_LINE(v1);

9 END;

10 /

A B C

PL/SQL procedure successfully completed.

Обратите внимание, переменная rec, в которую в цикле считываются данные, не требует объявления. Она будет являться записью PL/SQL, такой же, как записи PL/SQL, объявленные с помощью атрибута %ROWTYPE на основе курсора.

Все очень просто. Не нужно явно открывать и закрывать курсор. Вместо команды FETCH просто следует обратиться к текущему значению записи PL/SQL, которая здесь является своеобразной управляющей переменной цикла. Для выхода из цикла больше не нужно проверять атрибуты курсора %NOTFOUND и %FOUND. Если SQL-запрос не отберет ни одной строки, тело цикла просто не выполнится ни разу, если же результирующая выборка непустая, то после перебора всех строк цикл завершится автоматически.

По сути, программист тремя строчками кода говорит компилятору PL/SQL «Мне нужна каждая строка результирующей выборки, и я хочу, чтобы она была помещена в запись PL/SQL, соответствующую курсору». Компилятор PL/SQL формирует соответствующий байт-код со всеми низкоуровневыми вызовами сервера.

Простейший вариант курсорного цикла FOR имеет SQL-запрос, встроенный прямо в описание цикла:

SQL> DECLARE

2 v1 VARCHAR2(4000);

3 BEGIN

4 FOR rec IN (SELECT at1,at2 FROM tab1) LOOP

5 v1:= v1||' '||rec.at2;

6 END LOOP;

7 DBMS_OUTPUT.PUT_LINE(LTRIM(v1));

8 END;

9 /

A B C

PL/SQL procedure successfully completed.

Как и для неявных курсоров, для курсорного цикла FOR компилятор сам разместит в байт-коде низкоуровневые вызовы открытия курсора, считывания из него строк и закрытия.

Параметры курсора

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

SQL> SELECT * FROM tab1;

AT1 A

– -

1 A

2 B

3 C

SQL> DECLARE

2 CURSOR cur2 (i INTEGER) IS SELECT * FROM tab1 WHERE at1>=i;

3 cur2_rec cur2%ROWTYPE;

4 BEGIN

5 OPEN cur2(2); – курсор открыт с параметром i, равным 2

6 FETCH cur2 INTO cur2_rec;

7 WHILE cur2%FOUND LOOP

8 DBMS_OUTPUT.PUT_LINE(cur2_rec.at1);

9 FETCH cur2 INTO cur2_rec;

10 END LOOP;

11 CLOSE cur2;

12 END;

13 /

2

3

PL/SQL procedure successfully completed.

Помимо явных курсоров параметризировать можно и команды SELECT INTO и курсорные циклы FOR. Для этого в коде SQL в качестве параметров надо использовать ранее объявленные переменные PL/SQL скалярных типов данных. При препроцессинге эти переменные будут автоматически заменены компилятором PL/SQL на связываемые переменные SQL.

В исходном коде программы PL/SQL можно указывать команды добавления, изменения и удаления данных, для которых компилятор формирует в байт-коде вызовы соответствующих предложений SQL – INSERT, UPDATE и DELETE.

SQL> CREATE TABLE tab1 (at1 integer);

Table created.

SQL> DECLARE

2 l_at1 tab1.at1%TYPE;

3 BEGIN

4 l_at1 := 1;

5 INSERT INTO tab1 VALUES (l_at1);

6

7 l_at1 := 2;

8 INSERT INTO tab1 VALUES (l_at1);

9

10 l_at1 := 3;

11 INSERT INTO tab1 VALUES (l_at1);

12 DELETE FROM tab1 WHERE at1=1;

13

14 UPDATE tab1 SET at1=at1+1 WHERE at1=l_at1;

15

16 END;

17 /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM tab1;

AT1

–

2

4

Используемые для выполнения команд INSERT, UPDATE, DELETE неявные курсоры тоже имеют атрибуты. Чтобы получить их значения, следует указывать имя курсора SQL%.