Как и все СУБД, поддерживающие технологию "клиент-сервер", SYBASE SQL Anywhere наряду с данными хранит в базах данных программные объекты. Такими объектами являются: хранимые процедуры, хранимые функции и триггеры. Программные объекты могут использоваться большим количеством приложений. Такие объекты позволяют повысить эффективность функционирования приложений с базами данных, обеспечить высокую степень защиты последних и унифицировать способы обращения к данным из приложений.

Создание программных объектов заслуживает особого внимания и поэтому данный вопрос вынесен в отдельную главу.

В данной главе все примеры будут излагаться применительно к утилите ISQL. И только в конце главы будут проанализированы возможности SQL Central по работе с программными объектами.

При использовании ISQL необходимо удостовериться, что в данной утилите установлен режим завершения транзакции по окончанию работы с утилитой. Это позволит проводить все действия с базой данных в виртуальной памяти сервера (см.п. 1), не изменяя содержимого базы данных на внешнем носителе. При этом имеется возможность отменить все изменения, проводимые в виртуальной памяти.

6.1. Хранимые процедуры

Хранимые процедуры - это подпрограммы на языке 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 (см. п. 5.3).

Вызов хранимых процедур производится оператором CALL с соответствующими фактическими параметрами.

Установим соединение с базой данных Biblia в утилите 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-операторов (см. рис. 21) при помощи клавиш 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 (см. п. 5.2).

ПРИМЕЧАНИЕ.

Кроме владельца процедуры Х, все привилегии по отношению к ней имеют еще и пользователи, наделенные полномочиями администратора базы данных (класс полномочий DBA.). В дополнении к этому, такие пользователи могут при создании процедуры объявить ее владельцем любого другого пользователя.

Рассмотренная выше процедура Ins_absent была создана пользователем с именем Stepanov и предназначена для библиотекарей. В связи с этим возникает необходимость предоставления библиотекарям право вызова этой процедуры.

Как было сказано в приложении, все библиотекари объединены группу пользователей LIBRARIANS. Для того, чтобы члены этой группы получили возможность использовать процедуру Ins_absent следует выполнить оператор GRANT вида:

/* Предоставление полномочий на
 вызов хранимой процедуры */
GRANT EXECUTE ON Ins_absent 
TO LIBRARIANS

Лишение привилегий производится оператором REVOKE. В нашем случае это можно осуществит так:

/* Лишение полномочий на
 вызов хранимой процедуры */
REVOKE EXECUTE ON Ins_absent 
TO LIBRARIANS

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