Программные объекты баз данных
Публикация прежнего Webclub · 2010 год. Примеры и сведения сохранены в историческом контексте.
Как и все СУБД, поддерживающие технологию "клиент-сервер", 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
Любая хранимая процедура всегда выполняется с привилегиями ее владельца. Это позволяет пользователям, не имеющим прямого доступа к таблицам данных, возможность обращения к этим таблицам через процедуры, которые они имеют право использовать. Для каждой таблицы хранимые процедуры представляет собой унифицированный механизм взаимодействия с ней. Это лишает пользователей сделать что-нибудь лишнее с таблицами данных и способствует поддержанию целостности данных.
