6.2. Возврат результатов хранимых процедур

Процедуры могут возвращать результаты вызывающим их программным объектам одним из следующих способов:

• через фактические параметры типов OUT и INOUT;
• путем формирования результирующего множества;
• используя оператор RETURN.

В качестве примера процедуры, возвращающей результат через параметры типа OUT, ниже приводится текст процедуры Get_number_absent. Данная процедура определяет количество книг, находящихся на руках у заданного читателя:

//Пример процедуры с параметрами типа OUT
CREATE PROCEDURE 
            Get_number_absent
/* Процедура определяет количество книг, 
находящихся на руках у читателя, имеющего 
читательскую карточку с номером par_N_reader.
Результат возвращается через выходной 
параметр count_books. */
(
IN Par_N_reader SMALLINT, 
OUT Count_books SMALLINT
)
BEGIN 
SELECT COUNT(*)	//Подсчет числа записей
    INTO Count_books    //Результат записыва-
                        //ется в Count_books
    FROM Stepanov.Absent    //Указание 
                            //используемой таблицы
    WHERE               //Условие выбора записей
        N_reader = Par_N_reader;
END

Для того, чтобы проверить работу процедуры Get_number_absent необходимо выполнить следующую последовательность SQL-операторов:

//Проверка работы процедуры Get_number_absent
    /* Создание переменной Rez. Она существует только
    в течение текущего соединения с базой данных */
CREATE VARIABLE Rez SMALLINT;
        //Вызов процедуры
CALL Get_number_absent (80, Rez);
    /* Вывод значения переменной Rez в 
    окно Data утилиты ISQL */
SELECT Rez

В результате получим, у читателя с номером читательской карточки (N_reader) 80 на руках находятся четыре книги.

В процедуре Get_number_absent используется только один параметр тира OUT. В общем случае параметров такого типа в процедурах может быть несколько. По этой причине результат, формируемый процедурой Get_number_absent можно получить и с использованием оператора RETURN. Данный оператор возвращает в качестве результата одно значение. Для демонстрации этой возможности создадим процедуру Num_absent на базе приведенной выше процедуры Get_number_absent, путем незначительной модификации последней:

/* Процедура, возвращающая результат 
при помощи оператора RETURN */
CREATE PROCEDURE Num_absent
/* Процедура определяет количество книг, 
находящихся на руках у читателя, имеющего 
читательскую карточку с номером Par_N_reader.
Результат возвращается посредством оператора
RETURN. */
(
IN Par_N_reader SMALLINT
)
BEGIN
    DECLARE Count_books SMALLINT;
     SELECT COUNT(*) INTO Count_books
        FROM Stepanov.Absent
        WHERE N_reader = Par_N_reader;
 RETURN Count_books;
END

Чтобы убедиться в работоспособности процедуры Num_absent следует воспользоваться такой последовательностью SQL-операторов:

//Проверка работоспособности процедуры Num_absent
         /* Если переменная уже есть, то
         создавать ее заново нельзя */
//CREATE VARIABLE Rez SMALLINT;
         /* Вызов процедуры и присваивание
         ее результатов переменной Rez */
SET Rez = Num_absent (80);
         /* Вывод значения переменной Rez в 
         окно Data утилиты ISQL */
SELECT Rez

Эта процедура выдает те же данные, что и Get_number_absent.

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

//Процедура, формирующую результирующе множество
CREATE PROCEDURE Get_list_absent
/* Процедура определяет перечень книг, 
находящиеся на руках у читателя, имеющего
читательскую карточку с номером par_N_reader */
(
IN Par_N_reader SMALLINT 
)
RESULT (    Fld_Author LONG VARCHAR, 
        Fld_Title_book LONG VARCHAR, 
        Fld_City_publish CHAR(15), 
        Fld_Publisher CHAR(20), 
        Fld_Year_publish SMALLINT)
BEGIN
/* В операторе SELECT введены псевдонимы B и
A для  таблиц Stepanov.Books и Stepanov.Absent, 
соответственно */
SELECT B.Author, B.Title_book,
        B.City_publish, B.Publisher,
        B.Year_publish 
    FROM(Stepanov.Books AS B NATURAL
            JOIN Stepanov.Copies) NATURAL
            JOIN Stepanov.Absent AS A
    WHERE A.N_reader= 	Par_N_reader 
END

Результирующее множество формируется оператором SELECT. Этот оператор всегда формирует такое множество, если в нем не используется отсутствует служебное слово INTO. Данное служебное слово применяется для указания переменных, в которые будут записываться результаты выполнения оператора SELECT.

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

При описании параметров оператора RESULT их количество и типы должны соответствовать количеству и типам элементов списка выбора оператора SELECT. При этом имена этих параметров и элементов могут не совпадать друг с другом. Для проверки процедуры Get_list_absent выполните ее:

/* Проверка работоспособности 
процедуры Get_list_absent */
CALL Get_list_absent(80)

Теперь проверьте полученные результаты. Они будут представлены в окне Data утилиты ISQL. Содержимое результатов представлено в табл. 23.

При выполнении процедуры, формирующей результирующее множество, создается временная таблица - курсор (CURSOR). В курсор записывается результирующее множество. В дальнейшем пользователь может обрабатывать данные курсора по-своему усмотрению. Это будет обсуждаться в следующем п.5.3.

ПРИМЕЧАНИЕ

Следует обратить особое внимание на использование оператора RESUME в связи с результирующим множеством. Он продолжает выполнение процедурыв среде утилиты ISQL в связи с ее обязательной остановкой после формирования такого множества.

В среде утилиты ISQL процедура работает до своего нормального или аварийного завершения или до тех пор, пока не будет сформировано результирующее множество. Последнее возможно только, если для этого множества при помощи оператора OPEN не был открыт курсор.

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

Исходя из этого, следует отметить, что выполнение процедуры Get_list_absent не завершено. Она все еще находится в "зависшем" состоянии на сервере. Для ее завершения выполните оператор RESUME.

Покажем использование оператора RESUME на примере процедуры For_RESUME:

// Процедура, использующая несколько 
// результирующих множеств 
CREATE PROCEDURE For_RESUME ()
/* Демонстрация оператора RESUME. При каждом 
вызове процедуры Get_list_absent  формируется ре-
зультирующее множество и процедура For_RESUME  
приостанавливает свою работу. Требуется выполнить
 оператор RESUME для продолжения работы. Таким 
образом для завершения процедуры For_RESUME 
необходимо выполнить три оператора RESUME */
BEGIN
 MESSAGE '_______Parameter =80';
    CALL Get_list_absent (80);
                /*остановка до выполнения 
                оператора RESUME */
MESSAGE '____ Parameter =60';
SELECT B.Author, B.Title_book, 
        B.City_publish, B.Publisher,
        B.Year_publish 
    FROM Stepanov.Books B
    WHERE B.Code_book = 60;
                /*остановка до выполнения 
                оператора RESUME */
MESSAGE '_ Parameter =40';
    CALL Get_list_absent (40);
                /*остановка до выполнения 
                оператора RESUME */
MESSAGE '****FINISH';
END
//завершение работы процедуры

После вызова процедуры For_RESUME в утилите ISQL:

• В окне Data будет представлено результирующее множество, сформированное в результате выполнения в процедуре For_RESUME оператора CALL Get_list_absent(80).
• В окне Statistics будет выдано сообщение "... Procedure is executing.Use RESUME to continue.", означающее, что происходит выполнение процедуры приостановлено и для продолжения ее выполнения следует выполнить оператор RESUME.
• Выполнение процедуры будет приостановлено. Об этом свидетельствует сообщение в окне Statistics . Кроме того в окне локального сервера (см. рис. 2) или окне Messages удаленного сервера (см. рис. 4) в данной программе при помощи первого оператора MESSAGE выводится только одно сообщение "_______Parameter =80". Сообщения , формируемые тремя другим операторами MESSAGE пока не выводятся, потому что выполнение процедуры For_RESUME приостановлено.

После выполнения оператора RESUME функционирование процедуры For_RESUME будет продолжено до тех пока не будет сформировано следующее результирующее множество. В данном случае это сделает оператор SELECT.

В случае необходимости процедура завершения работы процедуры до конца следует воспользоваться оператором RESUME ALL. При этом кроме текущей завершаются также все процедуры формирующие результирующие множества, которые находятся в "подвешенном" состоянии на сервере.

После выполнения оператора SELECT происходит следующее:

• В окне Data будет представлено результирующее множество, сформированное оператором SELECT.
• В окне Statistics опять будет предложено выполнить оператор RESUME для продолжения функционирования процедуры.
• Об остановке работы процедуры свидетельствует сообщение в окне Statistics. Кроме того в окне сервера появится сообщение "____ Parameter =60". Остановка процедуры произойдет, несмотря на то, что в данном случае результирующее множество будет пустое.

После выполнения оператора RESUME функционирование процедуры For_RESUME будет продолжено до следующего результирующее множество.

После его анализа выполним последний третий раз оператор RESUME. В результате этого получим:

• Окне Data будет пустым.
• В окне Statistics будет выдано сообщение о завершении процедуры "Procedure completed.".
• В окне сервера появится сообщение "****FINISH". Его формирует последний оператор процедуры For_RESUME.

ПРИМЕЧАНИЕ

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

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