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, она представляет собой весьма наглядный пример обработки результирующего множества.

Таким образом, анализ возможностей хранимых процедур показал, что их использование как общего ресурса баз данных позволяет унифицировать обработку содержимого баз данных и повысить эффективность функционирования приложений.