Хранимые процедуры
Хранимые процедуры - это подпрограммы на языке SQL, хранящиеся в базах данных и представляющие собой один из видов их общих ресурсов. Тело любой хранимой процедуры представляет последовательность SQL-операторов, например таких, как выборка данных (SELECT), их модификация (UPDATE), удаление данных (DELETE), операторы цикла (LOOP), условные операторы (IF, CASE) и ряд других. Процедуры вызываются оператором CALL и могут использовать как входные параметры (передающие значения в процедуру), так и выходные параметры (возвращающие результаты процедуры вызывающему программному объекту). Процедуры могут вызываться из процедур, функций и других типов программных объектов.
Хранимые процедуры, создаются оператором CREATE PROCEDURE. Модификация тела хранимой процедуры осуществляется оператором ALTER PROCEDURE. Эти операторы могут использовать:
Приводимый пример иллюстрирует применение оператора 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. Они предназначены для указания типа формального параметра. Ниже приводится пояснения к типам формальных параметров:
В процедуре 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
Любая хранимая процедура всегда выполняется с привилегиями ее владельца. Это позволяет пользователям, не имеющим прямого доступа к таблицам данных, возможность обращения к этим таблицам через процедуры, которые они имеют право использовать. Для каждой таблицы хранимые процедуры представляет собой унифицированный механизм взаимодействия с ней. Это лишает пользователей сделать что-нибудь лишнее с таблицами данных и способствует поддержанию целостности данных.
