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

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.