12.3.1. Явный курсор

Курсор - это средство языка SQL, позволяющее с помощью команд OPEN, FETCH и

CLOSE получить построчный доступ к результату запроса к базе данных. (Будем

также называть курсором и сам набор строк, полученный в результате выполнения

запроса.)

Для использования курсора его надо сначала объявить, т.е. дать ему имя и

указать (с помощью предложения SELECT), какие столбцы и строки базовых таблиц

должны быть помещены в набор строк, названный этим именем. Команда OPEN

инициализирует получение указанного набора и установку перед первой его строкой

указателя текущей строки. Команда FETCH служит для установки указателя текущей

строки на следующую запись (первый раз на строку с номером 1) и выборки из

текущей строки курсора значений указанных столбцов с пересылкой их в переменные

PL/SQL. (Выполнением FETCH в цикле можно последовательно выбрать информацию из

всех строк курсора.) Наконец, команда CLOSE позволяет закрыть (удалить из

памяти) набор строк (при этом описание курсора сохраняется и его можно снова

открыть командой OPEN).

Существует модификация ("Курсор в цикле FOR"), позволяющая организовать

последовательный выбор строк объявленного курсора без явного использования

команд OPEN, FETCH и CLOSE.

12.3.1.1. Объявление курсора

Перед работой с курсором его следует объявить в разделе DECLARE или другом

допустимом разделе, используя синтаксис:

CURSOR cursor_name [ (parameter [, parameter, ... ] ) ] IS SELECT ... ,

где

cursor_name - имя курсора;

SELECT ... - предложение SELECT, определяющее строки курсора;

parametr - имеет следующий синтаксис:

variable_name [IN] type_name [ { := | DEFAULT } value ] ,

а type_name - любой тип (подтип) данных PL/SQL без указания ограничений

(например, длины символьных значений).

Формальные параметры курсора используются только для передачи значений в

WHERE фразу предложения SELECT с целью отбора нужных строк запроса. Передача

таких значений производится во время открытия курсора командой OPEN. Если

значения формальных параметров отсутствуют в команде OPEN и не заданы по

умолчанию (:= value или DEFAULT value), то выдается ошибка. При наличии тех и

других используются параметры из команды OPEN.

В следующем примере использованы оба способа задания значений по умолчанию

параметрам курсора:

DECLARE

CURSOR s1 (otd INTEGER := 102, grup VARCHAR2 DEFAULT `Преподаватели`,

tdat DATE := `1.1.1996`) IS

SELECT (TO_CHAR(razr)||` `||imya_dolg) razr_dolg, stavka

FROM shtat x, dolgnosti y, grup_dolg z

WHERE x.dolgn = y.dolgn AND y.grup_dolg = z.grup_dolg AND otdel = otd

AND tdat BETWEEN nachalo AND konec AND imya_grup_dolg = grup

ORDER BY razr_dolg DESC;

Кроме того, в нем выражению "(TO_CHAR(razr)||` `||imya_dolg)" дан псевдоним

"razr_dolg", использованный во фразе ORDER BY. Oracle рекомендует создавать

псевдонимы для всех выражений фразы SELECT с целью ссылки на них при работе с

курсором.

12.3.1.2. Открытие курсора (open)

Команда OPEN имеет следующий синтаксис

OPEN cursor_name [ (value [,value]...) ];

где список значений ("value") используется для передачи параметров курсора и

должен по числу и типу данных совпадать с описанием этих параметров.

Команда выполняет объявленный в курсоре SELECT ... , используя (если есть

параметры) передаваемые из OPEN значения или значения, указанные при объявлении

курсора, создавая набор строк и устанавливая указатель текущей строки перед

первой из них. Так, по команде

OPEN s1;

будет создан набор:

razr_dolg stavka

Указатель текущей строки ---> ------------------------ ------

17 Профессор 1.75

16 Профессор 3.

15 Доцент 9.75

14 Доцент 4.

13 Старший преподаватель 8.75

11 Ассистент 1.

где использовались значения параметров, заданные при описании, а по команде:

OPEN s1 (102,`Специалисты`,`1.1.1996`);

будет создан другой набор:

razr_dolg stavka

Указатель текущей строки ---> ------------------------ ------

13 Ведущий программист 1.

13 Ведущий электроник 2.

11 Электроник 1 категории 1.5

10 Программист 2 категории 3.5

8 Инженер 2 категории 2.

4 Лаборант 3.

12.3.1.3. Выборка строк из курсора (FETCH)

Команда FETCH, используемая для продвижения на один шаг указателя текущей

строки курсора и пересылки ее значений в переменные или запись, имеет следующий

синтаксис:

FETCH cursor_name INTO {variable_name1[,variable_name2]...} | record_name ;

Для каждого значения столбца, возвращенного запросом, в списке INTO должна

иметься переменная или поле записи соответствующего типа данных. Такие

- 31 -

переменные или записи должны быть заранее описаны в декларативной части блока

PL/SQL. Например

CREATE PROCEDURE pr_shtat IS

CURSOR s1 (otd INTEGER := 102, grup VARCHAR2 DEFAULT `Преподаватели`,

tdat DATE := `1.1.1996`) IS

SELECT (TO_CHAR(razr)||` `||imya_dolg) razr_dolg, stavka

FROM shtat x, dolgnosti y, grup_dolg z

WHERE x.dolgn = y.dolgn AND y.grup_dolg = z.grup_dolg AND otdel = otd

AND tdat BETWEEN nachalo AND konec AND imya_grup_dolg = grup

ORDER BY razr DESC;

sh_raz VARCHAR2(45); -- переменная для хранения значения razr_dolg

sh_stav shtat.stavka%TYPE; -- переменная для хранения значения stavka

raz VARCHAR(500); -- переменная, в которой будет последовательно

-- накапливаться текст "разряд-должность"

BEGIN

OPEN s1;

LOOP

FETCH s1 INTO sh_raz,sh_stav;

EXIT WHEN s1%NOTFOUND; -- выход при отсутствии возвращаемой строки

-- (см. п. 12.3.1.6)

raz := raz||sh_raz||`;`;

...

END LOOP;

CLOSE s1;

END pr_shtat;

Внутри цикла можно использовать значения переменных sh_raz и sh_stav, которые

после открытия курсора равны "17 Профессор" и "1.75", после первого прохождения

цикла - "16 Профессор" и "3.", после второго - "15 Доцент" и "9.75" и т.д.

При выборке значений текущей строки в запись, например, с именем ShRec надо

немного изменить как описание, так и тело блока процедуры:

...

ORDER BY razr DESC;

TYPE ShRecTyp IS RECORD (raz_dol VARCHAR(45), -- описание типа данных

stav shtat.stavka%TYPE); -- записи ShRec

ShRec ShRecTyp; -- объявление записи ShRec

raz VARCHAR(500); -- переменная, в которой будет последовательно

-- накапливаться текст "разряд-должность"

BEGIN

OPEN s1;

LOOP

FETCH s1 INTO ShRec;

EXIT WHEN s1%NOTFOUND; -- выход при отсутствии возвращаемой строки

raz := raz||ShRec.raz_dol||`;`;

...

Теперь значения, получаемые ранее из sh_raz и sh_stav, можно получать из

полей ShRec.raz_dol и ShRec.stav записи ShRec.

Целесообразность использования записей возрастает с увеличением количества

ее полей или возможности создания описания по типу уже существующей записи с

помощью атрибута %ROWTYPE.

Наконец, если при выполнении цикла осуществлен переход за последнюю строку

набора, то значения переменных FETCH-списка будут неопределены.

- 32 -

12.3.1.4. Закрытие курсора (CLOSE)

Команда CLOSE используется для освобождения всех ресурсов, которые

поддерживались открытым курсором (при этом описание курсора сохраняется и его

можно снова открыть командой OPEN). Синтаксис команды CLOSE имеет вид:

CLOSE cursor_name;

12.3.1.5. Использование курсора в цикле FOR

В большинстве ситуаций, которые требуют явного курсора, текст программы может

быть упрощен при использовании "курсора в цикле FOR", заменяющего команды OPEN,

FETCH и CLOSE. Курсор в цикле FOR:

- неявно объявляет индекс цикла записью, поля которой соответствуют столбцам

(псевдонимам) предложения SELECT ... из описания курсора;

- передает параметры курсора (если они есть) и открывает курсор;

- выбирает в цикле строки из полученного набора в индекс цикла (поля записи);

- закрывает курсор после обработки всех строк набора или досрочному выходу

из него с помощью команд EXIT или GOTO.

Синтаксис курсора в цикле FOR имеет вид:

FOR var_rec_name IN cursor_name [ (value [,value]...) ] LOOP

ТЕЛО ЦИКЛА

END LOOP;

где - var_rec_name индекс цикла, в котором при первом прохождении цикла хранится

первая строка набора, при втором прохождении цикла - вторая строка и т.д.;

- список значений ("value") используется для передачи параметров курсора

(он заменяет в данном случае список из команды OPEN);

- ТЕЛО ЦИКЛА содержит нужные строки повторяющейся части программы, в которых

используются переменные с именами var_rec_name.column_name, а column_name

имя столбца из перечня столбцов предложения SELECT в описании курсора.

Например,

DROP PROCEDURE pr_shtat;

CREATE PROCEDURE pr_shtat IS

CURSOR s1 (otd INTEGER := 102, grup VARCHAR2 DEFAULT `Преподаватели`,

tdat DATE := `1.1.1996`) IS

SELECT otdel, (TO_CHAR(razr)||` `||imya_dolg) razr_dolg, stavka

FROM shtat x, dolgnosti y, grup_dolg z

WHERE x.dolgn = y.dolgn AND y.grup_dolg = z.grup_dolg AND otdel = otd

AND tdat BETWEEN nachalo AND konec AND imya_grup_dolg = grup

ORDER BY razr DESC;

raz VARCHAR(500); -- переменная, в которой будет последовательно

-- накапливаться текст "разряд-должность"

BEGIN

FOR s1_rec IN s1 (102,`Специалисты`,`1.6.1996`) LOOP

raz := raz||s1_rec.razr_dolg||`;`;

...

END LOOP;

END pr_shtat;