6.3. Использование курсоров в хранимых процедурах
Одним из способов возврата результатов работы хранимых процедур является формирование результирующего множества. Данное множество формируется при выполнении оператора SELECT. Оно записывается во временную таблицу - курсор. Применение курсора в процедурах осуществляется путем последовательного выполнения следующих шагов:
- При помощи оператора DECLARE объявляется курсор для отдельного оператора SELECT или для отдельной процедуры.
- Оператором OPEN производится открытие курсора.
- Используя оператор FETCH, осуществляется установление указателя на требуемую запись курсора. При этом значения полей текущей записи присваиваются переменным, указываемым в операторе FETCH.
- В процессе перемещения указателя текущей записи курсора при выходе указателя за пределы курсора выдается предупреждение row not found.
- После того как курсор становится ненужным, он закрывается оператором CLOSE.
По умолчанию курсор закрывается автоматически в конце транзакции (операторы COMMIT или ROLLBACK). Если при объявлении курсора указана фраза WITH HOLD (с сохранением), то курсор закрывается только явным образом оператором CLOSE. Такое использование курсора снимает все проблемы с остановкой функционирования процедур в среде утилиты ISQL, формирующих результирующие множества. В этом случае отпадает необходимость использования оператора RESUME.
В курсоре указатель может быть установлен:
- до первой записи курсора;
- на запись в пределах курсора;
- за последнюю запись курсора.
- Позиционирование курсора может осуществляться на:
- первую/последнюю запись курсора;
- на следующую/предыдущую запись;
- на несколько записей вперед/назад относительно текущей записи;
- на i-ю запись относительно начала курсора.
В соответствие с приведенными шагами рассмотрим пример обработки результатов работы процедуры Get_list_absent. Для этого создадим в учебных целях процедуру Count_publishers, которая для читателя по фамилии "Петрова В.А.", имеющей читательскую карточку с № 80, определяет количество книг, изданных в издательстве "Советское радио". Текст этой процедуры приводится ниже:
//Процедура, использующая обработку курсора
CREATE PROCEDURE Count_publishers
/* Процедура подсчитывает количество книг
взятых, читателем "Петрова В.А.", которые
изданы в издательстве "Советское радио" */
(
OUT Par_count_publishers SMALLINT
)
BEGIN
DECLARE Err_not_found //Декларируем
EXCEPTION FOR //ситуацию выхода
SQLSTATE '02000'; //за пределы курсора
DECLARE
N_Petrova SMALLINT; //№ читательской
//карточки читателя "Петрова В.А."
// Объявление переменных для хранения
// значений полей текущей записи курсора
DECLARE Tek_Author CHAR(20);
DECLARE Tek_Title_book CHAR(100);
DECLARE Tek_City_publish CHAR(15);
DECLARE Tek_Publisher CHAR(20);
DECLARE Tek_Year_publish SMALLINT;
DECLARE //Объявление курсора Rez_cursor
Rez_cursor // для результирующего множества,
CURSOR FOR //формируемого процедурой
CALL Get_list_absent(N_Petrova);
SET //Обнуляем счетчик взятых книг
Par_count_publishers = 0;
/* Определяем № читательской карточки
читателя Петровой В.А. */
SELECT Reader.N_reader
INTO N_Petrova
FROM Stepanov.Reader
WHERE Reader.Name_reader =
'Петрова В.А.';
//Открываем курсор Rez_cursor с
//результирующим множеством
// процедуры Get_list_absent
OPEN Rez_cursor;
//Последовательно обрабытываем все
//записи курсора Rez_cursor
Work_for_kurs_loop: //метка цикла
LOOP
//Перемещаем указатель текущей записи
//курсора на следующую запись. При этом
//содержимое все полей записываются в
//предназначенне для этого переменные
FETCH NEXT Rez_cursor
INTO Tek_Author, Tek_Title_book,
Tek_City_publish, Tek_Publisher,
Tek_Year_publish;
IF SQLSTATE = Err_not_found
THEN //В случае если произошел выход за
//пределы курсора, то завершается
//работа цикла Work_for_kurs_loop
LEAVE Work_for_kurs_loop;
END IF;
IF Tek_Publisher = 'Советское радио'
THEN
SET Par_count_publishers=
Par_count_publishers+1;
END IF;
END LOOP Work_for_kurs_loop;
//конец цикла
CLOSE Rez_cursor;
END
Чтобы убедиться в правильной работе процедуры Count_publishers необходимо выполнить следующие SQL-операторы:
//Проверка работы процедуры Count_publishers
//CREATE VARIABLE Rez SMALLINT;
CALL Count_publishers(Rez);
SELECT Rez
В результате вызова этой процедуры в переменную Rez записано значение один.
Несмотря на ограниченное применение процедуры Count_publishers, она представляет собой весьма наглядный пример обработки результирующего множества.
Таким образом, анализ возможностей хранимых процедур показал, что их использование как общего ресурса баз данных позволяет унифицировать обработку содержимого баз данных и повысить эффективность функционирования приложений.
Назад |
Содержание |
Вперед