Хранимые функции
Хранимые функции являются разновидностью хранимых процедур. Они включены в состав программных объектов баз данных с целью наибольшего соответствия языкам программирования, например Си или Паскаль. Как и в этих языках программирования, каждая хранимая функция рассматривается в качестве выражения, формирующего одно единственное значение. Хранимые функции применяются для расширения функциональных возможностей операторов SELECT и ряда других SQL-операторов.
Функции создаются оператором CREATE FUNCTION. Их модификация производится при помощи оператора ALTER FUNCTION. Для разработки функций пользователи должны иметь класс полномочий RESOURCE (). В качестве примера представим хранимую процедуру Get_number_absent () в виде хранимой функции Fun_get_num_abs:
//Создание новой функции CREATE FUNCTION Fun_get_num_abs /* Функция определяет количество книг, находящихся на руках у читателя, имеющего читательскую карточку с номером Par_N_reader. Это количество возвращается в качестве результата функции. */ ( Par_N_reader SMALLINT ) RETURNS SMALLINT BEGIN DECLARE Count_books SMALLINT; SELECT COUNT(*) INTO Count_books FROM Stepanov.Absent WHERE N_reader = Par_N_reader; RETURN (count_books); END
Для проверки работы этой функции следует выполнить следующие SQL-операторы:
//Проверка процедуры Fun_get_num_abs /* Если переменная уже есть, то создавать ее заново нельзя */ //CREATE VARIABLE Rez SMALLINT; /* Вызов функции и присваивание ее результатов переменной Rez */ SET Rez = Fun_get_num_abs(80); SELECT Rez
В результате обращения к функции Fun_get_num_abs в переменную Rez запишется значение четыре, точно такое же как после выполнения процедуры Get_number_absent.
Оператор CREATE FUNCTION несколько отличается от оператора CREATE PROCEDURE. Эти отличия состоят в следующем:
В функциях допустимы только входные формальные параметры (типа IN). В связи с этим при описании формальных параметров не используются служебные слова IN, OUT и INOUT. После описания формальных параметров функции должен следовать оператор RETURNS. Данный оператор указывает тип возвращаемого функцией значения. В теле функции обязательно должен присутствовать оператор RETURN, который специфицирует возвращаемое функцией значение. Функция не может формировать результирующее множество.
Хранимые функции могут вызываться любым пользователем, имеющего для соответствующей функции привилегию EXECUTE. Для предоставления права вызова функций, также как и для процедур, используется оператор GRANT EXECUTE. Чтобы предоставить библиотекарям возможность применения функции fun_get_num_abs необходимо выполнить следующий оператор:
//Установка привилегии EXECUTE библиотекарям GRANT EXECUTE ON Fun_get_num_abs TO LIBRARIANS
Опять же, как для процедур, лишение привилегий на использование функций осуществляется оператором REVOKE EXECUTE:
//Лишение библиотекарей привилегии EXECUTE REVOKE EXECUTE ON Fun_get_num_abs TO LIBRARIANS
Ранее созданные функции хранятся в базах данных до тех, пока они не удаляются посредством оператора DROP FUNCTION. Для удаления из базы данных функции Fun_get_num_abs необходимо выполнить этот оператор следующим образом:
//Удаление функции DROP FUNCTION Fun_get_num_abs.
Удаление функции может производить только ее владелец или пользователь с полномочиями класса DBA ().
Вызов Функции должен осуществляться там, где требуется выражение, формирующее значение. В связи с этим, функции могут непосредственно использоваться в выражениях. Например, они могут применяться в арифметических выражениях, вместо фактических параметров процедур или функций, а также прямо в составе SQL-операторов. Все эти качества позволяют в значительной степени расширить функциональные возможности языка SQL, как средства разработки приложений.
Хранимые процедуры
Хранимые процедуры - это подпрограммы на языке SQL, хранящиеся в базах данных и представляющие собой один из видов их общих ресурсов. Тело любой хранимой процедуры представляет последовательность SQL-операторов, например таких, как выборка данных (SELECT), их модификация (UPDATE), удаление данных (DELETE), операторы цикла (LOOP), условные операторы (IF, CASE) и ряд других. Процедуры вызываются оператором CALL и могут использовать как входные параметры (передающие значения в процедуру), так и выходные параметры (возвращающие результаты процедуры вызывающему программному объекту). Процедуры могут вызываться из процедур, функций и других типов программных объектов.
Хранимые процедуры, создаются оператором CREATE PROCEDURE. Модификация тела хранимой процедуры осуществляется оператором ALTER PROCEDURE. Эти операторы могут использовать:
пользователи, которым разрешено создавать объекты базы данных, т.е. тем кто имеет класс полномочий - RESOURCE; администратор базы данных.
Приводимый пример иллюстрирует применение оператора CREATE PROCEDURE для создания процедуры Ins_absent. Эта процедура предназначена для фиксирования в базе данных факта выдачи некоторой книги одному из читателей:
//Создание новой процедуры CREATE PROCEDURE Ins_absent /* Процедура, фиксирующая факт выдачи книги с инвентарным номером par_N_books читателю, имеющему читательскую карточку, с номером Par_N_read Этот факт записывается в таблицу Absent. Владельцем таблицы Absent является пользователь Stepanov. В данной таблице при выполнении оператора INSERT полю Beg_date (дата выдачи книги) по умолчанию присваивается текущая дата. Книга выдается на 15 дней. На основании этого определяется дата ее возврата - значение для поля End_date */ ( IN Par_N_books SMALLINT, IN Par_N_reader SMALLINT ) BEGIN INSERT INTO Stepanov.Absent (n_books, N_reader, End_date) VALUES( Par_N_books, Par_n_reader, (current date+15)) END
Если выполнить этот оператор в утилите ISQL, то в базе данных будет создана хранимая процедура Ins_absent. Проверить этот факт можно проанализировав содержимое системного представления SYS.SYSPROCPARMS.
Тело хранимой процедуры является составным оператором, т.е. совокупностью операторов, заключенных между служебными словами BEGIN и END. В приведенном примере составной оператор состоит из одного оператора INSERT. Наряду с SQL-операторами в составном операторе могут быть определены локальные переменные, курсоры, временные таблицы данных и исключительные ситуации. Они доступны только в пределах составного оператора и не видимы за его пределами. Время их существования ограничено периодом исполнения составного оператора. Локальные определения широко используются при разработке программных объектов. Будут они применяться и в данной главе.
Описание каждого формального параметра в процедуре начинается с одного из служебных слов IN, OUT или INOUT. Они предназначены для указания типа формального параметра. Ниже приводится пояснения к типам формальных параметров:
IN - обозначает, что формальный параметр является входным, т. е. передающим значение процедуре; OUT - формальный параметр является выходным, т.е. посредством его осуществляется передача одного из результатов работы хранимой процедуры вызывающему программному объекту; INOUT - формальный параметр процедуры, выполняет роль как входного, так выходного параметра.
В процедуре Ins_absent используются только входные параметры. Тип данных каждого формального параметра должен соответствовать одному из типов данных, поддерживаемых SQL Anywhere ().
Вызов хранимых процедур производится оператором CALL с соответствующими фактическими параметрами.
Установим соединение с в утилите ISQL и обратимся к процедуре Ins_absent следующим образом:
//Вызов процедуры на исполнение CALL Ins_absent (1001, 25)
После этого при помощи оператора SELECT просмотрим содержимое таблицы Stepanov.Absent, чтобы удостовериться в работоспособности процедуры Ins_absent:
//Просмотр таблицы Stepanov.Absent //для проверки внесенных изменений SELECT * FROM Stepanov.Absent WHERE N_books>1000 AND N_books<1050
Теперь вернем базу данных в исходное состояние при помощи оператора ROLLBACK.
При определении хранимых процедур, помимо явного указания значений фактических параметров, имеется возможность использования значений параметров по умолчанию. В связи с этим, текст процедуры Ins_absent может быть видоизменен в части определения значения по умолчанию для параметра, задающего дату, когда должна быть возвращена книга (поле Absent.End_date). Это позволит в отдельных случаях предоставлять право пользования книгой менее 15 дней для дефицитных книг или более этого срока в исключительных случаях. Присвоим этому параметру имя par_End_date.
Тексты хранимых процедур в базе данных содержатся в системной таблице SYS.SYSPROCEDURE. Для получения текста процедуры Ins_absent необходимо выполнить следующие операторы:
//Получение текста хранимой процедуры SELECT SYSPROCEDURE.Proc_defn FROM SYS.SYSPROCEDURE /* Связь с другой таблицы если необходимо использовать имя владельца */ //KEY JOIN SYS.SYSUSERPERM WHERE SYSPROCEDURE.Proc_name
='Ins_absent' // Указание имени владельца при необходимости //AND User_name='Stepanov'; //оператор экспорта результатов оператора SELECT OUTPUT TO d:\Log_db\proctext.sql FORMAT ASCII
Загрузим в окно Command утилиты ISQL текст процедуры из файла d:\Log_db\proctext.sql. , выполнив команду меню File|Open. Отредактируем этот текст, заменив в нем название процедуры на Ins_absent_new и дополним состав формальных параметров параметром par_End_date. С учетом этого текст процедуры Ins_absent_new будет иметь вид:
//Создание процедуры с параметрами по умолчанию CREATE PROCEDURE Ins_absent_new /* Процедура, фиксирующая факт выдачи книги с инвентарным номером Par_N_books читателю, имеющим читальскую карточку, с номером Par_N_reader.Этот факт записывается в таблицу Absent. Владельцем таблицы Absent является пользователь Stepanov. В данной таблице дата выдачи книги (поле Beg_date) по умолчанию является текущая дата. Обычно книга выдается на 15 дней и на основании этого определяется дата ее возврата (поле End_date) по умолчанию. В случае необхо- димости книга может быть выдана на срок отличный от 15 дней путем использования фактического параметра Par_End_date */ ( IN Par_N_books SMALLINT, IN Par_N_reader SMALLINT, IN Par_End_date DATE DEFAULT current date+15 ) BEGIN INSERT INTO Stepanov.Absent (N_books, N_reader, End_date) VALUES( par_N_books, Par_N_reader, Par_End_date) END
После выполнения этого оператора в базу данных будет записана процедура Ins_absent_new. При обращении к этой процедуре в качестве фактических может использоваться два или три значения. Проверку этого факта автор предоставляет читателю.
Процедуры Ins_absent и Ins_absent_new могут быть созданы в одном сеансе ISQL. В этом случае для преобразования первой процедуры в вторую, текст процедуры Ins_absent может быть вызван для редактирования из списка ранее выполнявших последовательностей SQL-операторов () при помощи клавиш Ctrl+R.
В СУБД SQL Anywhere в процедурах может использоваться несколько параметров по умолчанию. В этом случае для указания какому именно из таких формальных параметров передаются значения фактических параметров используются имена соответствующих формальных параметров. Для иллюстрации этой возможности проведем редакцию процедуры Ins_absent_new:
// Модификация тела ранее созданной процедуры ALTER PROCEDURE Ins_absent_new /* ... */ ( IN Par_N_books SMALLINT, IN Par_N_reader SMALLINT DEFAULT 48, IN Par_End_date DATE DEFAULT Current date+15 ) BEGIN .............. END
ПРИМЕЧАНИЕ.
Для параметра Par_N_books нельзя задать значение по умолчанию. Это связано с тем, что параметр Par_N_books предназначен для указания значения поля Absent.N_books (). Данное поле содержит инвентарный номер выданной книги, который должен быть уникальным , так как один и тот же экземпляр книги не может быть выдан несколько раз. По этой причине для параметра Par_N_books недопустимо задание значения по умолчанию.
Процедуру Ins_absent_new можно вызвать несколькими способами, некоторые из которых имеют вид:
/* Примеры вызова процедуры с передачей параметров по умолчанию */ CALL Ins_absent_new(5) // значение пере- // дается только для пара- //метра par_N_books ; CALL Ins_absent_new(255, //через фактические параметры Par_End_date='1997-12-30') // передаются значение для или // параметров Par_N_books CALL Ins_absent_new( // и Par_End_date Par_N_books =255, // - Par_End_date='1997-12-30') //
Как было сказано ранее, хранимые процедуры записываются в базу данных, откуда они по мере необходимости могут быть удалены. Для удаления таких процедур из базы данных используется оператор DROP PROCEDURE. Например, для удаления процедуры Ins_absent этот оператор используется следующим образом:
//Удаление процедуры DROP PROCEDURE Ins_absent.
Пользователь, создавший хранимую процедуру, т.е. ее владелец, имеет доступ к данной процедуре без всяких ограничений. Он может вызывать процедуру на исполнение, модифицировать ее текст или просто удалить ее. Другим же пользователям привилегии на использование такой процедуры, и причем только в части ее вызова, осуществляется оператором GRANT ().
ПРИМЕЧАНИЕ.
Кроме владельца процедуры Х, все привилегии по отношению к ней имеют еще и пользователи, наделенные полномочиями администратора базы данных (класс полномочий DBA.). В дополнении к этому, такие пользователи могут при создании процедуры объявить ее владельцем любого другого пользователя.
Рассмотренная выше процедура Ins_absent была создана пользователем с именем Stepanov и предназначена для библиотекарей. В связи с этим возникает необходимость предоставления библиотекарям право вызова этой процедуры.
Как было сказано в приложении, все библиотекари объединены группу пользователей LIBRARIANS. Для того, чтобы члены этой группы получили возможность использовать процедуру Ins_absent следует выполнить оператор GRANT вида:
/* Предоставление полномочий на вызов хранимой процедуры */ GRANT EXECUTE ON Ins_absent TO LIBRARIANS
Лишение привилегий производится оператором REVOKE. В нашем случае это можно осуществит так:
/* Лишение полномочий на вызов хранимой процедуры */ REVOKE EXECUTE ON Ins_absent TO LIBRARIANS
Любая хранимая процедура всегда выполняется с привилегиями ее владельца. Это позволяет пользователям, не имеющим прямого доступа к таблицам данных, возможность обращения к этим таблицам через процедуры, которые они имеют право использовать. Для каждой таблицы хранимые процедуры представляет собой унифицированный механизм взаимодействия с ней. Это лишает пользователей сделать что-нибудь лишнее с таблицами данных и способствует поддержанию целостности данных.
Использование курсоров в хранимых процедурах
Одним из способов возврата результатов работы хранимых процедур является формирование результирующего множества. Данное множество формируется при выполнении оператора 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
//Процедура, использующая обработку курсора 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, она представляет собой весьма наглядный пример обработки результирующего множества.
Таким образом, анализ возможностей хранимых процедур показал, что их использование как общего ресурса баз данных позволяет унифицировать обработку содержимого баз данных и повысить эффективность функционирования приложений.
Обработка исключительных ситуаций
При выполнении программных объектов пользователь должен иметь возможность оперативно реагировать на возникающие отклонения от нормального процесса их выполнения. Для решения этой задачи разработчики SQL Anywhere применили механизм обработки исключительных ситуаций. Данный механизм представляет собой прием, обеспечивающий перехват и обработку ошибок и предупреждений. Исключительные ситуации в SQL Anywhere возникают при выполнении SQL-операторов. Исключительные ситуации представляются в виде кодов возврата, которые формируются после завершения выполнения операторов. Коды возврата, как и в большинстве баз данных SQL, одновременно записываются в системные переменные SQLSTATE и SQLCODE. В этих переменных возникновение исключительных ситуаций кодируются по разному. Так в переменной SQLCODE они представляются в виде десятичных чисел. При этом отрицательные значения соответствуют ошибкам, положительные - предупреждениям, а ноль - успешному завершению оператора. Кодирование исключительных ситуаций в переменной SQLSTATE соответствует стандарту SQL/92. При этом коды возврата представляются в виде строк из пяти символов. В переменной SQLSTATE первые два символа составляют код класса ошибок, следующие три символа - код внутри класса. Код "00000" соответствует успешному выполнению оператора. Перечень кодов для переменных SQLSTATE и SQLCODE вы можете найти в документации SQL Anywhere или ее справочной системе. В зависимости от значений кодов возврата может быть принято решение о повторении оператора, прерывания функционирования приложения и т.д.
ПРИМЕЧАНИЕ.
После считывания значения одной из переменных SQLSTATE или SQLCODE, обе они приводятся в состояние отсутствия ошибок.
Рассмотрим сначала, что происходит с работой программных объектов при возникновении исключительных ситуаций в виде ошибок, когда обработка этих ошибок не предусмотрена. Обработка ошибок и поведение программных объектов при возникновении предупреждений рассмотрена п.п. , .
6.6.1. Работа программных объектов при отсутствии реакции на ошибки
Если при функционировании программного объекта возникает исключительная ситуаций в виде ошибки и их обработка нем не определена, то происходит следующее. Программный объект сразу прекращает свое функционирование и происходит передача управления вызвавшему его программному объекту. Туда же через переменные SQLSTATE и SQLCODE передается код возврата ошибки. Если и там не определена обработка возникшей ошибки, то происходит передача управления и кода возврата ошибки еще выше по иерархии вызовов. Такая передача происходит до тех пор пока не встретится программный объект, в котором предусмотрена обработка возникшей ошибки, или пока будет достигнут самый верхний уровень иерархии вызовов. В последнем случае произойдет аварийное завершение функционирования приложения.
Допустим, что ни в одном из программных объектов A, B и C не предусмотрена обработка ошибок. Допустим также, что для них имеется следующая динамика вызовов: A -> B ->C. Тогда при возникновении ошибки в C произойдет последовательное аварийное завершение функционирования программных объектов C,B и A , т.е. всего приложения в целом. Ниже приводится пример, демонстрирующий поведение программных объектов при отсутствии обработки ошибок. В данном примере процедура Proc_First вызывает Proc_Second , в которой возникает ошибка.
CREATE PROCEDURE Proc_First() BEGIN MESSAGE '===Работает Proc_First .'; CALL Proc_Second(); MESSAGE '=== SQLSTATE= ', SQLSTATE,' в Proc_First.' END //___конец___процедуры CREATE PROCEDURE Proc_Second() BEGIN /* Объявление исключительной ситуации colum_not_found -ошибка "поле не найдено" */ DECLARE colum_not_found EXCEPTION FOR SQLSTATE '52003'; MESSAGE '***Работает Proc_Second.'; /* Искусственно генерируем возникновение исключительной ситуации colum_not_found */ SIGNAL colum_not_found; MESSAGE '******* SQLSTATE= ', SQLSTATE,' в Proc_Second .' ; END //___конец___процедуры
Сделаем некоторые комментарии к процедурам Proc_Firsrt и Proc_Second. Оператор MESSAGE осуществляет выдачу в окно локального сервера () или окно Messages удаленного сервера () строку, формируемую из параметров оператора. Оператор DECLARE..EXCEPTION объявляет псевдоним для исключительной ситуации colum_not_found. Эта исключительная ситуация в процедуре Proc_Second вызывается искусственно. Для этой цели используется оператор SIGNAL. Для выполнения данного примера применим следующий оператор:
//Вызов процедуры CALL Proc_First(). Тогда в окне сервера получим следующие сообщения: ===Работает Proc_First ***Работает Proc_Second
Кроме того, на клиентской ЭВМ пользователю выдается сообщение colum not found.
В процедуре Proc_Second после выполнения оператора SIGNAL эмулируется исключительная ситуация. В связи с этим второе сообщение о значении переменной SQLSTATE из данной процедуры формироваться не будет. Дело в том, в что в этой процедуре не определена обработка ошибки colum not found. Поэтому после ее возникновения процедура сразу прекращает свое функционирование. Следовательно все операторы, следующие за оператором SIGNAL , в том числе и второй оператор MESSAGE, выполняться не будут.
После аварийного завершения процедуры Proc_Second управление передается процедуре Proc_First c выдачей кода возврата о ошибке colum_not_found. В этой процедуре также не предусмотрена обработка данной ошибки. По этой причине процедура Proc_First тоже аварийно завершает свое функционирование. При этом на экран клиентской ЭВМ, с которой была запущена процедура Proc_First, будет выдано сообщение о возникшей ошибке colum not found.
ПРИМЕЧАНИЕ
Определить оператор, при выполнении которого возникла ошибка, позволяет вызов в утилите ISQL функции Traceback(*).
6.6.2. Обработка ошибок в программных объектах
Рассмотрим как происходит обработка ошибок в программных объектах. Их обработка производится механизмом обработки исключительных ситуаций. Использование этого механизма состоит:
в определении псевдонимов для исключительных ситуаций соответствующие ожидаемым ошибкам, посредством оператора DECLARE..EXCEPTION; в определении реакции на описанные исключительные ситуации в программном блоке, начинающемся служебным словом EXCEPTION.
Покажем как применяются исключительные ситуации для обработки ошибок на примере процедур Proc_First_Ex и Proc_Second|_Ex. Эти процедуры представляют собой незначительные модификации процедур Proc_Firsrt и Proc_Second.
CREATE PROCEDURE Proc_First_Ex() BEGIN MESSAGE '===Работает Proc_First_Ex'; CALL Proc_Second_Ex(); MESSAGE '=== SQLSTATE= ', SQLSTATE, ' в Proc_First_Ex.' END //___конец___процедуры CREATE PROCEDURE Proc_Second_Ex() BEGIN /* Объявление исключительной ситуации colum_not_found - ошибка "поле не найдено)" */ DECLARE colum_not_found EXCEPTION FOR SQLSTATE '52003'; MESSAGE '***Работает Proc_Second_Ex .'; //искусственно генерируем возникновение // исключительной ситуации colum_not_found SIGNAL colum_not_found; MESSAGE 'Выполнен оператор SIGNAL'; EXCEPTION WHEN colum_not_found THEN MESSAGE '***Сolum_not_found', ' SQLSTATE= ', SQLSTATE; MESSAGE '***' SQLSTATE= ', SQLSTATE, ' в Proc_Second_Ex'; WHEN OTHERS THEN RESIGNAL; END //___конец___процедуры
После выполнения данного примера посредством оператора
//Вызов процедуры CALL Proc_First_Ex()
в окне сообщений сервера появится следующая информация:
===Работает Proc_First_Ex . ***Работает Proc_Second_Ex . ***Colum_not_found SQLSTATE= 52003 *** SQLSTATE= 00000 в Proc_Second_Ex === SQLSTATE= 00000 в Proc_First_Ex.
При использовании исключительных ситуации для обработки ошибок после возникновении последних происходит переход к блоку EXCEPTION. При этом операторы, следующие за ошибочным оператором не выполняются. В блоке EXCEPTION выполняется реакция на возникшую ошибку, если такая реакция определена. Если ошибка не определена, то в пределах этого блока осуществляется переход на метку OTHERS. В рассматриваемом примере в последнем случае должен выполняться оператор RESIGNAL. Он позволяет аварийно завершить процедуру, несмотря на наличие операторов обработки ошибок. Кроме того, данный оператор отменяет все действия совершенные в процедуре.
После выполнения соответствующих операторов в блоке EXCEPTION происходит завершение процедуры. При этот, если не используется оператор RESIGNAL, то в вызвавший программный объект передается код возврата об отсутствии ошибки (SQLSTATE='00000', SQLCODE=0).
Использование механизма исключительных ситуаций является удобным средством контроля за возникающими ошибками. При его применении от пользователя не требуется проверять коды возврата после каждого SQL-оператора. Эту функции берет на себя СУБД. Код программного объекта становится короче, нагляднее и, как правило, в нем уменьшается вероятность наличия ошибок.
6.6.3. Обработка предупреждений в программных объектах
Обработка предупреждений отличается от обработки ошибок. При формировании предупреждения в переменные SQLSTATE и SQLCODE также записывается код возврата. Но прерывания вычислительного процесса при этом не происходит и программный объект продолжает свое функционирование. Если возникает необходимость отреагировать на предупреждение, то достаточно проверить содержимое переменной SQLSTATE или SQLCODE.
Рассмотрим функционирование программных объектов в условиях формирования предупреждений на примере процедур Proc_Firsrt_W и Proc_Second_W. Они разработаны на базе процедур Proc_Firsrt и Proc_Second.
CREATE PROCEDURE Proc_First_W() BEGIN MESSAGE '===Работает Proc_First_w.'; CALL Proc_Second_W(); MESSAGE '=== SQLSTATE= ', SQLSTATE,' в Proc_First_W.' END //___конец___процедуры CREATE PROCEDURE Proc_Second_W() BEGIN /* Объявление исключительной ситуации row_not_found - предупреждение запись не найдена */ DECLARE row_not_found EXCEPTION FOR SQLSTATE '02000'; MESSAGE '***Работает Proc_Second_W .'; /* искусственно генерируем исключи- тельную ситуацию row_not_found */ SIGNAL row_not_found; IF sqlstate='02000' THEN MESSAGE '***Warning row_not_found', ' SQLSTATE= ', SQLSTATE; END IF ; MESSAGE '***SQLSTATE= ', SQLSTATE,' в Proc_Second_W .' ; END //___конец___процедуры
Запустив пример на исполнение оператором
//Вызов процедуры CALL Proc_First_W()
в окне сообщений сервера появится следующая информация:
===Работает Proc_First_w . ***Работает Proc_Second_W . ***row_not_found SQLSTATE= 02000 ***SQLSTATE= 00000 в Proc_Second_W . === SQLSTATE= 00000 в Proc_First_W.
Данный пример показывает, что управлять обработкой предупреждений казалось бы значительно проще, чем обработкой ошибок. Однако использование проверок после каждого SQL-оператора вызывает нагромождение вспомогательных конструкций в тексте программного объекта. Это никак не способствует снижению программных ошибок.
Программные объекты баз данных
Как и все СУБД, поддерживающие технологию "клиент-сервер", SYBASE SQL Anywhere наряду с данными хранит в базах данных программные объекты. Такими объектами являются: хранимые процедуры, хранимые функции и триггеры. Программные объекты могут использоваться большим количеством приложений. Такие объекты позволяют повысить эффективность функционирования приложений с базами данных, обеспечить высокую степень защиты последних и унифицировать способы обращения к данным из приложений.
Создание программных объектов заслуживает особого внимания и поэтому данный вопрос вынесен в отдельную главу.
В данной главе все примеры будут излагаться применительно к утилите ISQL. И только в конце главы будут проанализированы возможности SQL Central по работе с программными объектами.
При использовании ISQL необходимо удостовериться, что в данной утилите установлен режим завершения транзакции по окончанию работы с утилитой (). Это позволит проводить все действия с базой данных в виртуальной памяти сервера (), не изменяя содержимого базы данных на внешнем носителе. При этом имеется возможность отменить все изменения, проводимые в виртуальной памяти.
Работа с программными объектами в SQL Central
В п.п. 6.1 - 6.6 были рассмотрены основные принципы разработки программных объектов SYBASE SQL Anywhere с использованием утилиты ISQL. Теперь рассмотрим какой дополнительный сервис по разработке программных объектов предлагается пользователям в утилите SQL Central.
6.7.1. Формирование заготовок текстов процедур и функций
Как и в ISQL в утилите, в SQL Central содержательная часть каждого программного объекта формируется при помощи текстового редактора. Однако при этом утилита SQL Central предоставляет возможность использования заготовок текстов для новых программных объектов и удобный сервис по редактированию имеющихся программных объектов. Для хранимых процедур и функций такие заготовки предлагаются шаблонами Add Procedure(Template), Add Function(Template) и мастером Add Procedure/Function(Wizard). Все они являются элементами папки Procedures & Functions (рис. 77).
Рис.77. Содержимое папки Procedures & Functions
Как видно на рис. 77, в папке Procedures & Functions представлены вышеуказанные шаблоны и мастер вместе со всеми процедурами и функциями, использовавшимися в этой главе. Эти программные объекты были разработаны при написании текущей главы. На этом же рисунке отображено контекстное меню процедур, появляющееся в результате щелчка правой кнопкой мыши по пиктограмме любой процедуры или функции (это меню будет обсуждаться ниже). Двойной щелчок по такой пиктограмме левой кнопки мыши приводит к вызову текстового редактора утилиты с загруженным текстом выбранной процедуры в его окне.
В результате щелчка мыши по пиктограмме шаблона Add Procedure(Template) производится вызов текстового редактора. При этом окно текстового редактора содержит заготовку оператора CREATE PROCEDURE (рис. 78).
Рис.78. Окно текстового редактора утилиты SQL Central с заготовкой оператора CREATE PROCEDURE
В случае использования шаблона Add Function(Template) в текстовом редакторе автоматически формируется аналогичная заготовка для оператора CREATE FUNCTION.
Мастер Add Procedure/Function(Wizard) предлагает пользователю выполнить ряд шагов и создает более полную заготовку процедуры или функции. Рассмотрим эти шаги на примере. Для этого проанализируем как использовался мастер Add Procedure/Function(Wizard) при создании процедуры Get_list_absent, рассмотренной в .
Шаг 1. Начальная стадия создания хранимой процедуры/функции (рис. 79).
Рис.79. Создание новой хранимой процедуры или функции
Пояснение. Добро пожаловать в Мастер создания Процедур и Функций. Этот Мастер поможет вам в создании хранимой процедуры или функции. Процедуры и Функции представляют собой совокупности SQL-операторов и управляющих операторов, которые в дальнейшем транслируются для повышения эффективности функционирования.
Вопрос. Вы хотите создать хранимую процедуру или функцию?
Ниже приводится переключатель. Установка его в одно из двух положений позволяет ответить на поставленный вопрос.
После установки переключателя необходимо "нажать" на кнопку Далее для выполнения следующего шага.
В нашем случае для создания процедуры было выбрано положение Stored Procedure (no return value).
Шаг 2. Определение названия процедуры и ее владельца (рис. 80).
Рис.80. Определение названия процедуры и ее владельца
Вопрос 1. Как будет называться новая процедура?
Находящееся ниже поле предназначено для имени создаваемой процедуры. Введем в это поле строку Get_list_absent.
Вопрос 2. Кто из пользователей будет владельцем процедуры?
В приводимом поле отображается имя владельца новой процедуры. По умолчанию это пользователь создающий ее.
Пользователи, обладающие правами администратора базы данных, имеют возможность назначить владельцем процедуры любого пользователя. Для этого они могут воспользоваться кнопкой Change.
Ответив на оба вопроса, при помощи кнопки Далее осуществляется переход к очередному шагу.
В нашем случае в качестве владельца был определен пользователь Stepanov.
Шаг 3. Выбор диалекта языка SQL для создаваемой процедуры (рис. 81).
Рис.81. Выбор диалекта языка SQL для создаваемой процедуры
Вопрос 1. Какой из диалектов языка SQL будет использоваться для создания новой процедуры?
Для ответа на этот вопрос пользователю предлагается установить переключатель в одно из положений Transact_SQL (compatible with Sybase SQL Server) или Watcom-SQL (SQL Anywhere native dialect).
В п.1 говорилось, что SYBASE SQL Anywhere максимально, насколько это возможно для СУБД такого класса, совместима с SYBASE SQL Server. Одной из сторон такой совместимости является поддержка в SYBASE SQL Anywhere такого диалекта языка SQL как Transact-SQL. Этот диалект используется в SYBASE SQL Server и может применяться в SYBASE SQL Anywhere наряду с собственным диалектом языка SQL - Watcom-SQL. Эти диалекты во многом совместимы, что схематично на рис. 82.
Рис.82. Совместимость Watcom-SQL и Transact-SQL
Вопрос 2. Вы можете возвращать в качестве результата более чем одну запись, используя результирующее множество. Вы хотите использовать такое множество?
Флажок Include result set () позволяет ответить на этот вопрос.
Выбрав диалекта языка SQL и установив признак необходимости использования результирующего множества, можно приступать к следующему шагу
При создании процедуры Get_list_absent был выбран Watcom-SQL. В создаваемой процедуре результирующее множество необходимо. По этой причине был установлен флажок Include result set.
Шаг 4. Включение оператора SELECT, формирующего результирующее множество в текст создаваемой процедуры (рис. 83).
Рис.83. Включение оператора SELECT, формирующего результирующее множество в текст создаваемой процедуры
Пояснение. Если вам известен оператор SELECT, который в дальнейшем будет использоваться в процедуре для формирования результирующего множества, то вы можете ввести его текст здесь. В результате этого мастер автоматически сгенерирует текст описания результирующего множества (фраза RESULT). Если вы этого не знаете, то можете просто нажать на кнопку Далее и определить результирующее множество вручную в текстовом редакторе.
Для ввода текста оператора SELECT ниже приводится поле окна. Для перехода к завершающему шагу следует "нажать" на кнопку Далее.
На рис. 83 в этом окне представлен такой оператор, примененный в процедуре Get_list_absent.
Шаг 5. Подтверждение готовности к созданию новой процедуры (рис. 84).
Рис.84. Подтверждение готовности к созданию новой процедуры
Пояснение 1. Вы можете включить комментарий для новой процедуры.
Ниже располагается поле для ввода комментария для создаваемой процедуры.
Пояснение 2. Мастер сгенерирует заготовку для следующей процедуры:
| Название: | Get_list_absent |
| Владелец | Stepanov |
| Диалект SQL: | Watcom-SQL |
| Использование результирующего множества: | Да |
После "нажатия" на эту кнопку мастер сформирует заготовку процедуры и отобразит ее в окне текстового редактора утилиты.
При создании процедуры Get_list_absent была получена заготовка, представленная на рис. 85.
Рис.85. Заготовка процедуры Get_list_absent
Процедура Get_list_absent состоит всего из одного оператора SELECT, определенного при выполнении шага 4. По этой причине в данном случае фактически была получена почти готовая процедура. В нее следует добавить описание входного параметра Par_N_reader и дополнить комментариями (). В общем случае, процедура, как правило, содержит несколько SQL-операторов. В связи с этим для формирования окончательного текста процедуры может потребоваться значительная доработка.
В случае использования мастера Add Procedure/Function(Wizard) для разработки функции, он формирует заготовку примерно такого же уровня как для процедуры.
6.7.2. Формирование заготовок текстов триггеров
Утилита SQL Central предоставляет сервис по формированию заготовок не только для процедур и функций, но и для триггеров. Для формирования заготовок триггеров используется мастер Add Trigger, являющийся элементом папки Triggers (рис. 86).
Рис.86. Содержимое папки Triggers
Как видно на рис. 86 в папке Triggers содержатся еще все имеющиеся для текущей таблицы триггеры (в данном случае для таблицы Books). Здесь же отображено контекстное меню триггеров, появляющееся в результате щелчка правой кнопкой мыши по пиктограмме любого триггера (это меню будет обсуждаться ниже). Двойной щелчок по такой пиктограмме левой кнопки мыши приводит к вызову текстового редактора утилиты с загруженным текстом выбранного триггера в его окне.
Рассмотрим как использовался мастер Add Trigger для формирования заготовки триггера Add_copies (). Мастер предлагает Add Trigger выполнить ряд шагов, которые представлены ниже.
Шаг 1. Начальная стадия создания триггера (рис. 87).
Рис.87. Создание нового триггера
Пояснение. Добро пожаловать в Мастер создания Триггеров. Этот Мастер позволит вам разработать триггер для текущей таблицы.
В SQL Anywhere пользовательские триггеры используются для реализации сложный форм ссылочной целостности.
Вопрос. Как будет называться новый триггер?
В имеющемся ниже поле окна необходимо ввести название триггера и затем при помощи кнопки Далее перейти к выполнению следующего шага.
В нашем случае было указано название Add_copies.
Шаг 2. Определение событий, на которые должен реагировать триггер (рис. 88).
Рис.88. Определение событий, на которые должен реагировать триггер
Вопрос. Какое из событий должно вызвать активизацию триггера?
Для ответа на этот вопрос мастер предлагает установить флажки соответствующие событиям, на возникновение которых может реагировать триггер. При использовании события UPDATE OF мастер предоставляет возможность указать модификация каких полей вызовет активизацию триггера. Выбор одного или нескольких событий и использование кнопки Далее является условием продолжения дальнейшего использования мастера.
При создании триггера Add_copies было определено, что причиной его активизации должно быть добавление новых записей в таблицу (событие INSERT).
Шаг 3. Выбор уровня триггера (рис. 89).
Рис.89. Выбор уровня триггера
Пояснение. Строчный триггер (триггер уровня Row-level trigger) может быть определен для выполнения до или после добавления, модификации или удаления каждой записи. Операторный триггер (триггер уровня Statement-level trigger) выполняется после завершения всего оператора.
Вопрос. Триггер какого уровня вы хотите создать?
Расположенный ниже переключать позволяет сделать требуемый выбор и затем приступить к следующему шагу.
Триггер Add_copies был определен как строчный триггер.
Шаг 4. Определение времени и порядка активизации триггера (рис. 90).
Рис.90. Определение времени и порядка активизации триггера
Вопрос 1. Когда должна производиться активизация триггера?
Переключатель ниже позволяет указать, что триггер может активизироваться до или после возникновения события.
Пояснение. Если для одного и того же события определено несколько триггеров, то для данного триггере вы можете указать очередность его активизации.
Вопрос 2. Каким по очереди должен активизироваться триггер?
Расположенный ниже счетчик, позволяет ответить на этот вопрос. Для перехода к завершающему шагу следует "нажать" на кнопку Далее.
Триггер Add_copies был определен как предваряющий триггер, который должен активизироваться в первую очередь.
Шаг 5. Подтверждение готовности к созданию нового триггера(рис. 91).
Рис.91. Подтверждение готовности к созданию нового триггера
Пояснение 1. Вы можете включить комментарий для нового триггера.
Ниже располагается поле для ввода комментария для создаваемого триггера.
Пояснение 2. Мастер сгенерирует заготовку для следующего триггера:
| Add_copies | |
| Событие, активизи-рующее триггер: | INSERT |
| Время активизации: | До выполнения оператора |
| Уровень триггера: | Строчный триггер |
После "нажатия" на эту кнопку мастер сформирует заготовку триггера и отобразит ее в окне текстового редактора утилиты.
При создании триггера Add_copies была получена заготовка, представленная на рис. 92.
Рис.92. Заготовка триггера Add_copies
Полученная заготовка содержит обязательные элементы оператора CREATE TRIGGER, необходимые для создания предваряющего строчного триггера Add_copies. Необязательные элементы этого оператора заключены в квадратные скобки. Теперь осталось отредактировать текст заготовки для получения окончательного текста триггера.
6.7.3. Контекстные меню программных объектов
При щелчке правой кнопки мыши по любому объекту в SQL Central появляется контекстное меню. Не является исключением и программные объекты. Вид их представлен на рис. 77 и 86. Контекстные меню триггеров и процедур во многом совпадают и реакция на выбор их одноименных пунктов одинакова. Только в первом случае объектом воздействия являются триггеры, а во втором - процедуры и функции. Меню процедур имеет более широкий состав пунктов и его изучение позволит без проблем использовать контекстное меню триггеров. По этим причинам предметом дальнейшего обсуждения является только контекстное меню процедур. Такое меню включает в себя пункты Open, Open as Watcom-SQL, Open as Transact-SQL, Test in ISQL, Copy, Paste, Delete и Properties. (). Рассмотрим назначение этих пунктов.
Пункт Open предназначен для вызова текстового редактора с тестом текущей процедуры. При этом текст процедуры отображается на том диалекте SQL, на котором он создавался. Ранее уже отмечалось, что программные объекты могут разрабатываться на любом из диалектов SQL, поддерживаемые SYBASE SQL Anywhere (Watcom-SQL или Transact-SQL).
Выбор пунктов Open as Watcom-SQL или Open as Transact-SQL позволяет отображать процедуру в требуемом диалекте языка SQL, вне зависимости от того какой диалект использовался при создании процедуры. Как выглядит на языке Transact-SQL рассмотренная в процедура Get_list_absent приведено на рис. 93.
Рис.93. Процедура Get_list_absent на языке Transact-SQL
Если сравнить текст процедуры Get_list_absent на обоих диалектах, то можно отметить всего два отличия. Одно состоит в служебном слове AS, находящемся перед служебным слово BEGIN на диалекте Transact-SQL., второе - в замене при описании параметра процедуры служебного слова IN на символ @. В других случаях различия могут быть более существенными, но в конечном итоге не очень значительными ().
Пункт Test in ISQL служит для организации процесса тестирования. Реакцией на его выбор является запуск утилиты ISQL, если она еще не работает. При этом в ее окне Command формируются необходимые подготовительные операторы и оператор вызова процедуры с требуемыми параметрами. К вспомогательным операторам относятся операторы CREATE VARIABLE (создание переменных) , SET (присваивание), RESUME ALL (завершение всех процедур, использующих результирующее множество - см. ) и ряд других. Использование пункта Test in ISQL для процедуры Get_list_absent вызывает формирование следующей последовательности SQL-операторов, в которую вставлен перевод комментариев:
% % % SQL Central generated procedure test script % % % Ensure any previously executing procedures are completed /* SQL Central сгенерировал процедуру тестиро- вания программного объекта. Обеспечиваем завер- шение выполнения всех запущенных процедур */ RESUME ALL; % % % Ensure our test variables do not already exist /* Предотвращаем ситуацию повторного объявления переменной, используемой в тесте */ SET OPTION On_error = 'continue'; DROP VARIABLE "Par_N_reader"; SET OPTION On_error = 'prompt'; % % % Create input/output variables /*Описание переменных */ CREATE VARIABLE "Par_N_reader" smallint; % % % Edit the following lines to set the input values /* Отредактируйте следующие строки для присвоения значений пременным */ SET "Par_N_reader" = '1'; % % % Execute the procedure /* Выполнение процедуры */ CALL "Stepanov"."Get_list_absent"( "Par_N_reader" ); % % Use the RESUME command to view multiple result sets /* Используйте оператор RESUME для просмотра нескольких результирующих множеств */
Данные SQL-операторы отображаются в окно Command утилиты ISQL (). В случае необходимости эти операторы могут быть отредактированы и запущены на исполнение.
Выбор пункта Copy позволяет запомнить в системном буфере текст выбранной процедуры/функции с целью ее копирования в другую базу данных. Для этого требуется в текущем сеансе работы утилиты SQL Central осуществить соединение с другой базой данных. Затем для новой базы данных необходимо отобразить в правой панели содержимое папки Procedures & Functions. После этого следует щелкнуть в любом свободном месте правой панели правой кнопкой мыши и выбрать в появляющемся контекстном меню пункт Paste. В результате этого на основании данных системного буфера создается копия процедуры первой базы данных.
Пункт Delete предназначен для удаления процедур и функций.
Особого внимания заслуживает пункт меню Properties. Его выбор выводит на экран окно свойств процедуры (рис. 94).
Рис.94. Вкладка General окна свойств процедуры
Это окно содержит две вкладки General и Permissions. Первая из них представлена на рис. 94. В ней содержится общая информация процедуре: название, владелец, использовавшийся диалект языка SQL и комментарий. Вкладка Permissions предназначена для установки привилегии EXECUTE для процедуры (рис. 95).
Рис.95. Вкладка Permissions окна свойств процедуры
В этом окне видно, что группе пользователей LIBRARIANS предоставлено право использовать процедуру Get_list_absent. Установка такого права (привилегии EXECUTE) осуществляется при помощи кнопки Grant Execute. Кнопка Revoke Execute предназначена для лишения этого права. Такие же действия, но только применительно к таблицам рассмотрены в п.5.6 (см. рис. ).
Таким образом, анализ программных объектов SYBASE SQL Anywhere показал, что их использование позволяет реализовать сложные формы ограничений целостности и существенно расширить функциональные возможности разрабатываемых приложений баз данных.
Результаты выполнения процедуры Get_list_absent(80)
| Гмурман В.Е. | Теория вероятностей и математическая статистика.Учебное пособие для студентов ВТУЗов | Москва | Высшая школа | 1972 |
| Гмурман В.Е. | Руководство к решению задач по теории вероятностей и математической статистике.Учебное пошкола пособие для студентов ВТУЗов | Москва | Высшая школа | 1979 |
| Дектярев Ю.И. | Методы оптимизации | Москва | Советское радио | 1980 |
| Габасов Р. | Методы оптимизации | Минск | БГУ | 1981 |
При выполнении процедуры, формирующей результирующее множество, создается временная таблица - курсор (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 . Кроме того в окне локального сервера () или окне Messages удаленного сервера () в данной программе при помощи первого оператора 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, то число полей результирующего множества и их типы должны для каждого множества быть одни и теми же.
Таким образом, рассмотренные разнообразные способы получения результатов функционирования хранимых процедур придают гибкость процессу разработки приложений баз данных.
Назначение псевдонимов, вводимых фразой
REFERENCING для обращения к исходным и обновленным записям при определении триггеров
| Строчный | Предваряющий | NEW AS | Для обращения к полям вводимой записи | -- | Для обращения к новым значениям модифицируемых полей записи |
| OLD AS | -- | Для обращения к полям удаляемой записи | Для обращения к исходным значениям модифицируемых полей записи | ||
| Завершающий | NEW AS | Для обращения к полям введенной записи | -- | Для обращения к новым значениям модифицируемых полей записи | |
| OLD AS | -- | Для обращения к полям удаленной записи | Для обращения к исходным значениям модифицируемых полей записи | ||
| Операторный | Завершающий | NEW AS | Для обращения к времненой таблице, содержащей введенные записи | -- | Для обращения к временной таблице,содержащей модифицированные записи |
| OLD AS | -- | Для обращения к временной таблице, содержащей удаленные записи | Для обращения к временной таблице, содержащей исходные значения обновленных записей |
ПРИМЕЧАНИЕ
При активизации операторного триггера создаются одна или две временные таблицы. В этих таблицах хранятся записи, добавленные к исходной таблицы данных, удаленные из нее или записи, подвергнувшиеся изменениям, до и после выполнения оператора UPDATE.
Для завершения обсуждения псевдонимов, используемых в триггерах, приведем еще один пример триггера:
//Демонстрация псевдонимов триггера CREATE TRIGGER Update_Number /* Строчный завершающий триггер, выдающий при модификации поля Number старое и новое его значения */ AFTER UPDATE OF Number ON Stepanov.Books REFERENCING OLD AS Old_books NEW AS New_books FOR EACH ROW BEGIN MESSAGE '*** UPDATE ****'; MESSAGE 'OLD NUMBER=', Old_books.number; MESSAGE 'NEW NUMBER=', New_books.number; END
Строчный завершающий триггер Update_Number, активизируется при модификации поля Stepanov.Books.Number. В процессе своей работы он выдает на сервер старое и новое значение этого поля. Для проверки его работы выполним следующей SQL-оператор:
//Проверка триггера Update_Number UPDATE Stepanov.Books SET Number=7 WHERE Code_book=1
В результате в окне сервера будет выдано следующее сообщение:
*** UPDATE **** OLD NUMBER=49 NEW NUMBER=7
После этого выполним оператор ROLLBACK для отмены проведенной модификации таблицы Stepanov.Books.
Рассмотрим теперь пример операторного триггера. Определим его снова для таблицы Stepanov.Books и опять для события Добавление новой записи. Для данной таблицы на это событие уже настроен один триггер - Add_copies. Поэтому для задания очередности выполнения триггеров установим, чтобы новый триггер запускался во вторую очередь (фраза ORDER 2). В данном случае иначе и нельзя, так как триггер Add_copies - это предваряющий триггер, а новый триггер - завершающий. Назовем новый триггер Copies_Books. Текст его представлен ниже:
// Создание операторного триггера CREATE TRIGGER Copies_Books AFTER INSERT ORDER 2 ON Stepanov.Books FOR EACH STATEMENT /* Операторный завершающий триггер сранивает число экземпляров книг зафиксированных в таблицах Stepanov.Books и Stepanov.Copies. Если эти данные не совпадают, то значит нарушена целостность данных. В этом случае искусственно генерируется ошибка с кодом SQLSTATE '99999' */ BEGIN DECLARE Err_referencins EXCEPTION FOR SQLSTATE '99999'; DECLARE Count_copies SMALLINT; DECLARE Sum_copies SMALLINT; //Определяем число книг в библиотеке, //используя таблицу Stepanov.Copies SELECT COUNT(*) INTO Count_copies FROM Stepanov.Copies; //Определяем число книг в библиотеке, //используя таблицу Stepanov.Books SELECT SUM(Books.Number) INTO Sum_copies FROM Stepanov.Books; //Сравниваем результаты подсчетов IF Count_copies <> Sum_copies THEN MESSAGE 'Err_referencins'; SIGNAL Err_referencins; END IF; END
В данном триггере нет обращений отдельно к вновь введенным записям таблицы Stepanov.Books, обращение происходит целиком к таблице. По этой причине фраза REFERENCING в триггере не используется. Рассматриваемый триггер представляет собой еще один пример механизма поддержания сложных форм ограничений целостности данных. Оба триггера взаимно дополняют друг друга. Предваряющий строчный триггер Add_copies согласованно добавляет записи в таблицы Stepanov.Copies и Stepanov.Books. Завершающий операторный триггер Copies_Books проверяет согласованность содержимого этих таблиц.
Действия, реализуемые в этом триггере и в триггере Add_copies, не могут быть реализованы стандартными механизмами поддержания ссылочной целостности. Это является наглядным примером того какое мощное средство поддержания целостности данных представляют собой триггеры.
По аналогии с ранее рассмотренными программными объектами - хранимыми процедурами и функциями, любой триггер содержится в базе данных. Там он хранится до тех пор пока не будет удален из нее посредством оператора DROP TRIGGER. Таким образом для удаления триггера Add_copies этот оператор должен быть использован так:
//Удаление триггера DROP TRIGGER Add_copies.
Триггеры всегда выполняются с полномочиями владельца таблицы, с которой связаны триггеры. Привилегии пользователя, активизировавшего триггер, или пользователя, создавшего триггер, на выполнение самого триггера не оказывают никакого влияния. Допустим, что библиотекари не имеют вообще никаких привилегий на использование таблиц Stepanov.Copies и Stepanov.Books. Однако они имеют право вызывать процедуру Ins_absent, которая осуществляет пополнение состава записей таблицы Stepanov.Books. Тогда библиотекари при обращении к процедуре Ins_absent, добавляющей новые записи в таблицу Stepanov.Books, активизируется триггер Add_copies. Этот триггер выполняется с привилегиями пользователя Stepanov. - владельца таблицы Stepanov.Copies. Следовательно этот триггер имеет возможность вводить новые записи в эту таблицу. В связи с этим библиотекари, не имея право работать с таблицей Stepanov.Copies-напрямую, тем не менее имеют возможность взаимодействовать с этой таблицей косвенно. Данное свойство позволяет разработать для пользователей единый способ манипулирования с конкретной таблицей, что служит еще одним средством поддержания целостности данных.
Таким образом, исходя из сказанного, следует, что триггеры являются мощным средством обеспечения сложных форм ограничений целостности данных.
Триггеры
Триггеры - это один из видов программных объектов СУБД, поддерживаемых SQL Anywhere. Каждый триггер связан с одной из таблиц данных, входящих состав базы данных. С каждой такой таблицей может быть связано несколько триггеров. Основное назначение триггеров состоит в автоматическом использовании их в качестве реакции на некоторые события, происходящие с таблицами, с которыми связанны триггеры. Это свойство триггеров позволяет использовать их для реализации сложных форм ограничений целостности данных. Кроме того, рассматриваемое свойство превращает сервер из пассивного наблюдателя за происходящими изменениями данных в систему, оперативно реагирующую на такие изменения. Правила, в соответствие с которыми осуществляются активные действия сервера, определяются триггерами. Иногда эти правила или сами триггеры называют бизнес - правилами .
Триггеры, создаются оператором CREATE TRIGGER. Модификация триггеров производится при помощи оператора ALTER TRIGGER, а удаление - оператором DROP TRIGGER. Эти операторы могут использовать:
пользователь - владелец таблицы; пользователи, владеющие полномочиями администратора базы данных; пользователи с классом полномочий RESOURCE (), имеющие привилегию ALTER () для таблицы, с которой связанны триггеры.
ПРИМЕЧАНИЕ
Независимо от того, кто создал триггер его владельцем всегда является владелец таблицы.
В СУБД SQL Anywhere триггеры могут быть определены для одного из приведенных ниже событий или сразу на несколько из них:
Добавление новой записи в таблицу. Данное событие возникает при выполнении оператора INSERT. Оно приводит к активизации триггера при добавлении новой записи в связанную таблицу. Удаление записей. Это событие наступает в результате воздействия оператора DELETE. Данный оператор производит удаление записей из таблиц, с которыми связанны триггеры. связанной с триггером таблицы, что является причиной активизации триггера. Модификация записей. Событие этого типа инициируется оператором UPDATE. Оно возникает при изменении значений любого из полей записей таблицы, с которой связан активизируемый триггер. Изменение значений заданного списка полей таюлицы. Это событие, как и предыдущее, возникает при выполнении оператора UPDATE (UPDATE OF colums-list), но при модификации только заданных полей таблицы данных (colums-list).
Таким образом, триггеры автоматически запускаются при изменении содержимого таблицы данных, с которой они связаны.
Для каждого триггера должно быть определено время его выполнения - либо перед операторами INSERT, DELETE, UPDATE (предваряющий триггер), либо после них (завершающий триггер). Типичный пример использования предваряющих триггеров - проверка вводимых данных. Завершающие триггеры полезны в тех случаях, когда при модификации записей необходимо сравнивать исходные значения полей с их новыми значениями.
Операторы INSERT, DELETE, UPDATE, которые содержат служебное слово WHERE или подзапросы, как правило, воздействуют (добавляют, удаляют, модифицируют) на несколько записей таблицы. Когда при этом должен выполняться триггер? Каждый раз при изменение очередной записи или один раз после модификации всех записей? Для ответа на этот вопрос в SQL Anywhere реализованы триггеры двух уровней. Триггер первого уровня Statement-level trigger выполняется однократного после полного завершения одного из вышеуказанных операторов (операторный триггер) Триггер второго уровня Row-level trigger предназначен для многократного выполнения после каждого изменения одной из записей (строк) таблицы (строчный триггер).
Если при выполнении триггера возникает ошибка, тогда все действия осуществленные оператором, активизировавшим триггер, и действия, уже произведенные самим триггером, отменяются. В результате этого база данных возвращается в состояние, предшествующее началу выполнения вышеуказанного оператора.
ПРИМЕЧАНИЕ
Следует особо подчеркнуть, что триггеры активизируются только при выполнении операторов INSERT, DELETE, UPDATE и никаких других. Данное обстоятельство необходимо отметить в связи со следующим. Как показал опрос ряда пользователей, многие из них ошибочно считают, что триггеры активизируются и при добавлении новых записей в таблицы в результате импорте данных. Однако импорт производится операторами INPUT и LOAD TABLE, который не входят в число вышеуказанных операторов. В связи с этим, при осуществлении импорта данных механизм триггеров не работает.
Рассмотрим пример использования триггера. В предметной области - в библиотеке, для которой разрабатывается приложение, вполне типична следующая ситуация. В библиотеку периодически поступают новые книги. Каждую из них необходимо учесть и завести карточки для всех ее экземпляров. Применительно к следует для каждой книги добавить одну запись в таблицу Stepanov.Books и несколько записей (по числу экземпляров) в таблицу Stepanov.Copies. Для реализации этих действий следует выполнить операторы INSERT для обеих вышеуказанных таблиц. Если один из таких операторов не выполнить, то произойдет нарушение целостности базы данных. Такое требование должно соблюдаться для всех приложений (допустим их несколько), производящих регистрацию поступления новых книг. В этом случае хорошим решением является применение для таблицы Stepanov.Books триггера, реагирующего на выполнение оператора INSERT. Как и таблица, этот триггер будет в храниться в база данных и можно обеспечить доступ к нему всем приложениям. Тогда один и то же механизм поддержания целостности будет использоваться для всех таких приложений. При изменении механизма (изменении тела триггера), этот факт станет "достоянием гласности" для всех приложений. Реализация механизма поддержания целостности базы данных при пополнения библиотечного фонда представлен в виде триггера Add_copies:
//Определение триггера CREATE TRIGGER Add_copies BEFORE INSERT ORDER 1 ON Stepanov.Books REFERENCING NEW AS New_books FOR EACH ROW /* Предваряющий строчный триггер, активизирую- щийся при наступлении события "Добавление новой записи" в таблицу Stepanov.Books. В этой таблице имеются три поля, для двух из которых определены значения по умолчанию. Поэтому в операторе INSERT достаточно ввести значения только одного поля - Code_book */ BEGIN DECLARE Kol SMALLINT; SET Kol=New_books.number; /* MESSAGE 'Code_book=', New_books.Code_book; */ WHILE Kol>0 LOOP INSERT INTO Stepanov.Copies( Code_book) VALUES( New_books.Code_book); SET Kol=Kol-1 END LOOP END
Триггер Add_copies является предваряющим строчным триггером, о чем указывают служебные слова BEFORE и FOR EACH ROW, соответственно. Данный триггер активизируется при добавлении новой записи (служебное слово INSERT) в таблицу (служебное слово ON) Stepanov.Copies. Триггер Add_copies формирует данные об экземплярах поступившей книги путем добавления новых записей о каждом экземпляре книги в таблицу Stepanov.Copies. В рассматриваемом случае процесс использования этого триггера происходит следующим образом. Данный триггер активизируется при выполнении оператора INSERT. Этот оператор позволяет вставлять сразу несколько записей. Каждый раз перед добавлением очередной записи запускается триггер и после его успешного выполнения производится непосредственное добавление следующей записи в таблицу Stepanov.Books. Далее при попытке вставить еще одну запись происходит повторная активизация триггера, а затем добавление этой записи в таблицу. Так происходит до тех пор пока не завершится выполнение оператор INSERT. Другим условием завершением этого оператора является возникновения ошибки при выполнении самого оператора или триггера. В этом случае происходит аварийное завершение оператора и база данных возвращается в состояние, в котором она была до начала выполнения оператора.
Общий порядок применения триггеров состоит в последовательном выполнении следующих шагов при вызове на выполнение операторов INSERT, DELETE или UPDATE:
1.Активизируются предваряющие триггеры, если они есть. 2.Выполняются действия по проверке ограничений ссылочной целостности, установленной при определении схемы базы данных и возможно нарушенной действиями предваряющих триггеров. 3.Выполняется непосредственно операция, активизирующая триггер. 4.Активизируются завершающие триггеры, в случае если таковые имеются .
Теперь детально проанализируем триггер Add_copies. Как уже было сказано ранее, с одной таблицей может быть связано несколько триггеров. Некоторые из них могут быть настроены на одни и те же события. Для того, чтобы в этом случае установить порядок их выполнения применяется служебное слово ORDER. Для других случаев его применение является не обязательным. В триггере Add_copies это слово используется для указания того, что данный триггер будет выполняться первым при наступлении события Добавление новой записи в таблицу. Это сделано в связи с тем, что далее для этого события будет предложен еще один триггер.
Проверим функционирование триггера Add_copies. Для этого сымитируем факт поступления в библиотеку новой книги:
//Имитация поступления в библиотеку новой книги INSERT Stepanov.Books (Author, Coauthors, Title_book, City_publish, Publisher, Year_publish, Number) VALUES ('Степанов Ю.Л.', 'Карпова Т.С., Гурко А.В.', 'ЭФФЕКТИВНАЯ РАБОТА С БАЗАМИ ДАННЫХ ПО ТЕХНОЛОГИИ КЛИЕНТ-СЕРВЕР', 'Санкт-Петербург','ПИТЕР', 1998, 10)
Данный триггер при попытке добавления новой записи в таблицу Stepanov.Books сначала осуществляет вставку записи в таблицу Stepanov.Copies. Далее производится контроль ограничений целостности. При этом стоит напомнить, что в таблице Stepanov.Copies определен внешний ключ Books_Copies, который ссылается на таблицу Stepanov.Books. Создание этого внешнего ключа , но только для базы данных Dubl_Biblia обсуждалось в п.5.8.
Одним из основных назначений внешних ключей является предотвращение "висячих" ссылок. Однако при использовании триггера Add_copies сначала создаются именно такие ссылки. Это связано с тем, что в таблицу Stepanov.Copies будет добавлена запись с информацией об первом экземпляре i-ой книги, а данных о самой книге в таблице Stepanov.Books еще нет. В связи с этих внешний ключ Books_Copies ссылается на не существующую запись таблицы Stepanov.Books. Результатом этого является возврат базы данных в исходное состояние (состояние до начала выполнения оператора INSERT) и выдача сообщения о возникновении исключительной ситуации "no primary key value for foreign key 'Book_Copies' in table 'Copies'". Оно означает отсутствие значения первичного ключа для внешнего ключа Books_Copies таблицы Copies.
Таким образом, условием наступления данной исключительной ситуации является наличие предваряющего триггера. Другим условием является то, что во внешнем ключе Books_Copies определена проверка ограничений целостности сразу после изменения содержимого таблицы данных. Выходом из создавшегося положения может быть одна из двух альтернатив:
преобразование предваряющего триггера Add_copies в завершающий триггер; установка во внешнем ключе Books_Copies проверку ограничений целостности только при завершении транзакции, т.е. при выполнении оператора COMMIT.
Использовании первой альтернативы заключается в замене в тексте тела триггера служебного слова BEFORE на AFTER. Тогда первая строчка в определении триггера Add_copies будет выглядеть следующим образом:
/* Преобразование предваряющего триггера в завершающий */ ALTER TRIGGER Add_copies AFTER.
Тексты триггеров в базе данных содержатся в системном представлении SYS.SYSTRIGGERS. Для получения текста триггера Add_copies необходимо выполнить следующие операторы:
//Получение текста триггера SELECT SYS.SYSTRIGGERS.Trigdefn FROM SYS.SYSTRIGGERS WHERE Trigname ='Add_copies' //Указание имени владельца //AND Owner='Stepanov' //Указание наименивания таблицы //AND Tname='Books' ;OUTPUT TO d:\Log_db\trigtext.sql FORMAT ASCII
В утилите SQL Central применение второй альтернативы состоит в установке флажка Check on commit закладки Integrity окна свойств внешнего ключа Books_Copies в утилите SQL Central. Окно свойств внешнего ключа Books_Copies появляется после щелчка правой кнопкой мыши по его пиктограмме (). В утилите ISQL для достижения такого эффекта необходимо выполнить следующие SQL-операторы:
//Изменение свойств внешнего ключа //Сначала удалением внешнего ключа таблицы ALTER TABLE Stepanov.Copies DELETE FOREIGN KEY Books_Copies; //Создание заново внешнего ключа //с новыми свойствами ALTER TABLE Stepanov.Copies ADD "Books_Copies " NOT NULL FOREIGN KEY(Code_book) REFERENCES Stepanov.Books(Code_book) ON UPDATE CASCADE ON DELETE RESTRICT CHECK ON COMMIT /* признак проверки целостности только при выполнении оператора COMMIT */ // комментарий к внешнему ключу COMMENT ON FOREIGN KEY Stepanov.Copies.Books_Copies IS 'Внешний ключ для поддержания ограничений ссылочной целостности между таблицами Copies и Books'
Из двух вышеуказанных альтернатив по причинам, которые будут понятны далее, установим во внешнем ключе Books_Copies проверку ограничений целостности при завершении транзакции.
Теперь повторим попытку выполнения оператора INSERT для добавления новой записи в таблицу Stepanov.Books. Она должна завершиться успешно и в таблице Stepanov.Books появится одна новая запись. При этом в результате активизации триггера Add_copies таблица Stepanov.Copies пополнится десятком записей. Все они будут связанны с записью появившейся в таблице Stepanov.Books.
В рассматриваемом примере порядок очередности активизации триггера (предваряющий или завершающий) имеет важное значение. В других случаях совершенно безразлично в какой последовательности будут происходить обращение к триггеру. К этому следует добавить, что случае термины "предваряющий" и "завершающий" применятся только для строчных триггеров. Операторные триггеры, как следует из определения их названия, могут быть только завершающими.
При работе триггеров часто возникает необходимость обращаться как к исходным значениям полей таблицы, так и к их обновленным значениям. Для этой цели используются фраза REFERENCING с параметрами NEW AS или OLD AS. С ее помощью вводятся псевдонимы для обращения к исходным и обновленным данным. В триггере Add_copies фраза REFERENCING с параметром NEW AS определяет псевдоним New_books для обращения к значениям полей вновь введенной записи таблицы Stepanov.Books. В конечном же итоге смысл таких псевдонимов зависит от типа триггера и от событий, при возникновении которых активизируются триггеры. Назначение псевдонимов, вводимых фразой REFERENCING, приведено в табл. 24.
Возврат результатов хранимых процедур
Процедуры могут возвращать результаты вызывающим их программным объектам одним из следующих способов:
через фактические параметры типов 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.
