Разработка хранимых процедур

Для приложений, работающих с БД, хранимые процедуры (Stored Procedure) — это подпрограммы, которые выполняются на сервере. По отношению к БД — это объекты, которые создают­ся и хранятся в БД и могут быть вызваны из клиентских приложе­ний. При этом одна процедура может быть использована в любом числе клиентских приложений, что позволяет существенно эко­номить трудозатраты на создание прикладного программного обес­печения и эффективно применять стратегию повторного исполь­зования кода. Так же как и любые процедуры в стандартных язы­ках программирования, хранимые процедуры могут иметь вход­ные и выходные параметры или не иметь их вовсе.

Хранимые процедуры могут быть активизированы не только пользовательскими приложениями, но и триггерами.

Хранимые процедуры пишутся на базовом языке программи­рования, могут включать в себя любые операторы SQL, а также включают в себя некоторый набор операторов, управляющих хо­дом выполнения программ, во многом схожих с подобными опе­раторами процедурно-ориентированных языков программирова­ния. В коммерческих СУБД для написания текстов хранимых про­цедур используются собственные языки программирования. Так, в СУБД Oracle для этого используется язык PL/SQL, а в MS SQL Server — язык Transact SQL. В последних версиях Oracle для напи­сания хранимых процедур объявлено использование языка Java.

Хранимые процедуры являются объектами БД. Каждая храни­мая процедура компилируется при первом выполнении, при этом в процессе компиляции строится оптимальный план выполнения процедуры. Описание процедуры совместно с планом ее выпол­нения хранится в системных таблицах БД.

Для создания хранимой процедуры в SQL применяется опера­тор CREATE PROCEDURE.

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

Имя хранимой процедуры является идентификатором в языке программирования, на котором она пишется, и должно удовлет­ворять всем требованиям» предъявляемым к идентификаторам в данном языке.

В MS SQL Server хранимая процедура создается оператором следующего формата:

Здесь необязательное ключевое слово VARYING определяет за­данное значение по умолчанию для определенного ранее параметра.

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

Ключевое слово ENCRYPTION определяет режим, при кото­ром исходный текст хранимой процедуры не сохраняется в БД. Такой режим применяется для сохранения авторского права на интеллектуальную продукцию, которой и являются хранимые процедуры.

Исходные тексты разработанных хранимых процедур не долж­ны быть доступны администратору базы данных заказчика.

Однако все остальные параметры помимо имени хранимой процедуры являются необязательными. При этом процедуры мо­гут быть и процедурами-функциями. Эти понятия трактуются здесь традиционно, как в языках программирования высокого уровня. Хранимая процедура-функция возвращает значение, которое при­сваивается переменной, определяющей имя процедуры. Явная про­цедура не возвращает значение, но в ней может быть использова­но ключевое слово OUTPUT, определяющее, что данный пара­метр является выходным.

Рассмотрим несколько примеров простейших хранимых про­цедур.

Хранимая процедура может быть вызвана несколькими спосо­бами. Простейшим способом является использование оператора EXEC:

EXEC <имя процедуры> <значение_входного_параметра1>... <имя_переменной_для_выходного параметра 1>...

При этом все входные и выходные параметры должны быть обязательно заданы в том порядке, в котором они определены в процедуре.

Например, если надо найти число имеющихся в библиотеке экземпляров книги «Огас1е8. Энциклопедия пользователя» с ISBN 966-7393-08-09, текст вызова ранее созданной хранимой процеду­ры может быть следующим:

Если определены несколько версий хранимой процедуры, то при вызове можно указать номер конкретной версии для испол­нения. Например, во второй версии процедуры COUNTEX пос­ледний оператор ее исполнения имеет следующий вид:

EXEC @Ntek =COUNT_EX:2 @ISBN

Однако если в процедуре определены значения входных пара­метров по умолчанию, то при ее запуске можно указывать значе­ния не всех параметров. В этом случае оператор вызова процедуры можно записать в следующем виде:

Например, создадим процедуру, которая считает число книг, изданных конкретным издательством в конкретном году (при со­здании процедуры в качестве года издания по умолчанию зададим значение текущего года):

Теперь вызовем эту процедуру, для чего подготовим перемен­ную, в которую можно, поместить результаты ее выполнения:

declare @N int

Переменная @N содержит число книг в библиотеке, изданных Издательским центром «Академия» в текущем году. Можно также обратиться к данной процедуре, задав все параметры:

В результате получим число книг, изданных Издательским цен­тром «Академия» в 2004 г. и имеющихся в наличии в библиотеке.

Задавая параметры по именам, необязательно использовать тот порядок, в котором они описаны при создании процедуры.

Каждая хранимая процедура является объектом БД, т.е. она имеет уникальное имя и уникальный внутренний номер в сис­темном каталоге. При изменении текста хранимой процедуры сле­дует сначала удалить ее как объект, хранимый в БД, и только после этого записать вместо нее новую процедуру. Отметим, что при удалении хранимой процедуры удаляются одновременно все ее версии.

Для того чтобы автоматизировать процесс удаления старой про­цедуры и замены ее новой, в начале текста хранимой процедуры можно выполнить проверку наличия объекта типа «хранимая про­цедура» с заданным именем в системном каталоге. При наличии описания данного объекта удалить его из системного каталога. В этом случае текст хранимой процедуры предваряется специальным оператором проверки и может иметь, например, следующий вид:

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

Текст процедуры в этом случае будет иметь следующий вид:

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

В данной процедуре использовалась функция преобразования типа данных dataTime в тип данных varchar(8). Это было необходи­мо сделать для согласования типов данных при выполнении опе­рации сравнения. Действительно, входная переменная ©BIRTHDAY имеет символьный тип (varchar), а поле базы дан­ных BIRTHDAY имеет тип SmallDateTime.

Хранимые процедуры допускают наличие нескольких выходных параметров, для чего каждый из этих параметров после задания ему типа данных должен иметь дополнительное ключевое слово OUTPUT. Рассмотрим пример хранимой процедуры с несколькими выходны­ми параметрами. Создадим процедуру ввода нового читателя, при­чем внутри этой процедуры выполним проверку наличия в картоте­ке данного читателя, чтобы не назначать ему новый номер читатель­ского билета. При этом выходными параметрами процедуры будут номер читательского билета, признак того, был ли ранее записан читатель с данными характеристиками в нашей библиотеке, а если он был записан, то сколько книг за ним числится.

Данная процедура будет иметь следующий вид:

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

Дата добавления: 2014-01-07