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

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

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

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

259

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

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

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

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

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

CREATE PRO[EDURE] [;]

[{@параметр1 тип_данных}

[VARYING] [= ] [OUTPUT]]

[, .параметрN...]

[ WITH

{ RECOMPILE

| ENCRYPTION

| RECOMPILE, ENCRYPTION}]

[FOR REPLICATION]

Тело процедуры

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

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

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

260

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

Однако кроме имени хранимой процедуры все остальные параметры являются необязательными. Процедуры могут быть процедурами или процедурами-функциями. И эти понятия здесь трактуются традиционно, как в языках программирования высокого уровня.

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

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

/* процедура проверки наличия экземпляров данной книги

параметры:

@ISBN шифр книги

процедура возвращает параметр, равный количеству экземпляров

Если возвращается ноль, то это значит, что нет свободных экземпляров данной

книги в библиотеке.

CREATE PROCEDURE COUNT_EX (@ISBN varchar(12))

/* определим внутреннюю переменную */

DECLARE @TEK_COUNT int

/* выполним соответствующий оператор SELECT

Будем считать только экземпляры, которые в настоящий момент находятся

не на руках у читателей, а в библиотеке */

select @TEK_COUNT = select count(*) FROM EXEMPLAR WHERE ISBN = @ISBN

AND READER_ID Is NULL AND EXIST = True

/* 0 - ноль означает, что нет ни одного свободного экземпляра данной книги

в библиотеке */

RETURN @TEK_COUNT

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

EXEC ...

...

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

261

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

/*определили две переменные

@Ntek - количество экземпляров данной книги в наличие в библиотеке

@ISBN - международный шифр книги */

declare @Ntek int

DECLARE @ISBN VARCHAR(14)

/* Присвоим значение переменной @ISBN */

Select @ISBN = ?966-7393-08-09?

/* Присвоим переменной @Ntek результаты выполнения хранимой процедуры

COUNT_EX */

EXEC @Ntek - COUNT_EX @ISBN

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

Так, например, в версии 2 процедуры COUNT_EX последний оператор исполнения этой процедуры имеет вид: /

EXEC @Ntek = COUNT_EX:2 @ISBN

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

EXEC =...

=...

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

CREATE PROCEDURE COUNT_BOOKS (@YEARIZD Int - Year(GetDate( )).

@PUBLICH varchar(20))

/* процедура подсчета количества книг конкретного издательства, изданных

в конкретном году

параметры:

@YEARIZD Int год издания

@PUBLICH название издательства

DECLARE @TEK_Count int

Select @TEK count - Select COUNT(ISBN)

262

From BOOKS

Where YEARIZD = @YEARIZD AND PUBLICH =@PUBLICH

/* одновременно с исполнением оператора Select мы присваиваем результаты его работы определенной ранее переменной @TEK_Count */

/* при формировании результата работы нашей процедуры мы должны учесть,

что в нашей библиотеке, возможно, нет ни одной книги некоторого издательства

для заданного года. Результат выполнения запроса SELECT в этом случае будет

иметь неопределенное значение, но анализировать все-таки лучше числовые

значения. Поэтому в качестве возвращаемого значения мы используем результаты

работы специальной встроенной функции Transact SQL COALESCE (nl.n2, ... ,nm).

которая возвращает первое конкретное, то есть не равное NULL, значение из

списка значений nl.n2, ... ,nm. */

Return COALESCE (@TEK_Count.O)

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

declare @N int •

Exec @N - COUNT_BOOKS @PUBLICH = ?Питер?

В переменной @N мы получим количество книг в нашей библиотеке, изданных издательствам "Питер" в текущем году.

Мы можем обратиться к этой процедуре и задав все параметры:

Exec @N = COUNT_BOOKS @PUBLICH = ?BHW?. @YEARIZD = 1999

Тогда получим количество книг, изданных издательством "BHW" в 1999 году и присутствующих в нашей библиотеке.

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

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

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

/* проверка существования в системном каталоге объекта с данным именем

и типом, созданного владельцем БД */

if exists (select * from sysobjects where id = object_id(?dbo.NEW_BOOKS?) and sysstat & Oxf = 4)

263

/* если объект существует, то сначала его удалим из системного каталога */

drop procedure dbo.NEW_BOOKS

CREATE PROCEDURE NEW_BOOKS (@ISBN varchar(12).@TITL varchar(255).@AUTOR varchar(30),@COAUTOR varchar(30),@YEARIZD Int.SPACES INT,@NUM_EXEMPL INT)

/* процедура ввода новой книги с указанием количества экземпляров данной книги параметры

@ISBN varchar(12) шифр книги

@TITL varchar(255) название

@AUTOR varcharOO) автор

@COAUTOR varcharOO) соавтор

@YEARIZD Int год издания

@PAGES INT количество страниц

@NUM_EXEMPL INT количество экземпляров

/*опишем переменную, в которой будет храниться количество оставшихся

не оприходованных экземпляров книги, т.е. таких, которым еще не заданы

инвентарные номера */

DECLARE @ТЕК int

/ *вводим данные о книге в таблицу BOOKS */

INSERT INTO BOOKS VALUES(@ISBN.@TITL.@AUTOR.@COAUTOR.@YEARIZD.@PAGES)

/* назначение значения текущего счетчика оставшихся к вводу экземпляров*/

SELECT @TEK = @NUM_EXEMPL

/* организуем цикл для ввода новых экземпляров данной книги */

WHILE @TEK>0 /* пока количество оставшихся экземпляров больше нуля */

BEGIN

/* так как для инвентарного номера экземпляра книги мы задали свойство

IDENTITY, то нам не надо вводить инвентарный номер. СУБД сама автоматически

вычислит его, добавив единицу к предыдущему, введет при выполнении оператора

ввода INSERT.

Поле, определяющее присутствие экземпляра в библиотеке (EXIST) - логическое

поле, мы введем туда значение TRUE,которое соответствует присутствию

экземпляра книги в библиотеке.

Даты взятия и возврата мы можем не заполнять, тогда по умолчанию СУБД

подставит туда значение, соответствующее 1 января 1900 года, если мы не хотим

хранить такие бессмысленные данные, то можем ввести для обоих полей дата

время, значения текущей даты. */

264

Insert Into EXEMPLAR (ISBN.DATA_IN,DATA_OUT,EXIST)

VALUES (@ISBN.GetDate().GetDate()).TRUE)

/* изменение текущего значения счетчика количества оставшихся экземпляров */

SELECT @ТЕК = @ТЕК - 1

End /* конец цикла ввода данных о экземпляре книги*/

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

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

/* проверка существования в системном каталоге объекта с данным именем

и типом, созданного владельцем БД */

If exists (select * from sysobjects where Id = object_id(?dbo.NEW_BOOKS?) and sysstat & Oxf = 4)

/* если объект существует, то сначала его удалим из системного каталога */

drop procedure dbo.NEW_BOOKS

CREATE PROCEDURE NEW_BOOKS (@SBN varchar(12).@TITL varchar(255).@AUTOR

varchar(30),@COAUTOR varchar(30).@YEARIZD 1nt,@PAGES INT,@NUM_EXEMPL INT)

/* процедура ввода новой книги с указанием количества экземпляров данной книги

параметры

@ISBN varchar(12) шифр книги

@TITL varchar(255) название

@AUTOR varcharOO) автор

@COAUTOR varcharOO) соавтор

@YEARIZD int год издания

@PAGES INT количество страниц

@NUM_EXEMPL INT количество экземпляров

DECLARE @TEK int

declare @INV int

INSERT INTO BOOKS VALUES(@ISBN.@TITL.@AUTOR.@COAUTOR,@YEARIZD.@PAGES)

/* назначение значения текущего счетчика оставшихся к вводу экземпляров*/

SELECT @ТЕК = @NUM_EXEMPL

/* определение максимального значения инвентарного номера в библиотеке */

265

SELECT @INV - SELECT MAX( ID_EXEMPLAR) FROM EXEMPLAR

/* организуем цикл для ввода новых экземпляров данной книги */

WHILE @TEK>0 /* пока количество оставшихся экземпляров больше нуля */

BEGIN

insert Into EXEMPLAR (IDJXEMPLAR.ISBN.DATA_IN.DATA_OUT.EXIST)

VALUES (@INV,@ISBN.GETDATE().GetDate(). TRUE)

/* изменение текущих значений счетчика и инвентарного номера */

SELECT @ТЕК = @ТЕК - 1

SELECT @INV = @INV + 1

End /* конец цикла ввода данных о экземпляре книги*/

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

if exists (select * from sysobjects where id = object_id(?dbo. CK_READER?) and sysstat & Oxf = 4)

/* если объект существует, то сначала его удалим из системного каталога */

drop procedure dbo.CK_READER

/* Процедура возвращает номер читательского билета, если читатель есть и 0 в

противном случае.

В качестве параметров передаем фамилию и дату рождения */

CREATE PROCEDURE CK_READER (@FIRST_NAME varchar(30) ,@BIRTH_DAY varchar(12))

/*опишем переменную, в которой будет храниться номер читательского билета*/

DECLARE @NUM_READER INT

/* определение наличия читателя */

select @NUM_READER = select NUM_READER from READERS

WHERE FIRST_NAME = @ FIRST_NAME AND

AND convert (varchar (8) .BIRTH_DAY.4)=@BIRTH_DAY

RETURN COALESCE(@NUM_READER.0)

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

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

266

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

/* проверка наличия данной процедуры в нашей БД*/

if exists (select * from sysobjects where id =

object_id(N?[dbo] [NEW_READER]?) and OBJECTPROPERTYdd. N?sProcedure?) = 1)

drop procedure [dbo] [NEW_READER]

/* процедура проверки существования читателя с заданными значениями вводимых

параметров

Процедура возвращает новый номер читательского билета, если такого читателя не

было сообщает старый номер и количество книг которое должен читатель в

противном случае */

CREATE PROCEDURE NEW_READER (@NAME_READER varchar(30).@ADRES

va rcha r(40).@HOOM_PHONE char(9).@WORK_PHONE char(9).

@BIRTH_DAY varchar(8),

@NUM_READER int OUTPUT,

/* выходной параметр, определяющий номер читательского билета*/

@Y_N int OUTPUT.

/* выходной параметр, определяющий был ли читатель ранее записан

в библиотеку*/

@COUNT_BOOKS int OUTPUT

/* выходной параметр, определяющий количество книг, которое числится

за читателем*/)

/* переменная, в которой будет храниться номер читательского билета,

если читатель уже был записан в библиотеку */

DECLARE @N_R int

/* определение наличия читателя */

EXEC @N_R = CK_READER @NAME_READER,@BIRTH_DAY

IF @N_R= 0 Or @N_R Is Null

/* если читатель с заданными характеристиками не найден, т. е. переменной

@N_R присвоено значение нуль или ее значение неопределено, перейдем

к назначению для нового читателя нового номера читательского билета */

BEGIN

/* так как мы номер читательского билета определили как инкрементное поле,

то в операторе ввода мы его не указываем система сама назначит новому

читателю очередной номер */

267

INSERT INTO READER(NAME_READER,ADRES.HOOM_PHONE.WORK_PHONE.ВIRTH_DAY)

VALUES (@NAME_READER.@ADRES.@HOOM_PHONE.@WORK_PHONE.Convert(smal1datetime,

@BIRTH_DAY.4) )

/* в операторе INSERT мы должны преобразовать символьную переменную @BIRTH_DAY

в тип данных sma11datetime, который определен для поля дата рождения

BIRTH_DAY. Это преобразование мы сделаем с помощью встроенной функции

Transact SQL Convert */

/* теперь определим назначенный номер читательского билета */

select @NUM_READER = NUM_READER FROM READER

WHERE NAME_READER = @NAME_READER

AND convert(varchar(8),BIRTH_DAY.4)=@BIRTH_DAY

/* здесь мы снова используем функцию преобразования типа, но в этом случае

нам необходимо преобразовать поле BIRTH_DAY из типа smalldatetime

к типу varchar(8), в котором задан входной параметр @BIRTH_DAY */

Select @Y_N =0

/* присваиваем выходному параметру @Y_N значение 0 (ноль), что

соответствует тому,что данный читатель ранее в нашей библиотеке

не был записан */

Select @COUNT_BOOKS = 0

/* присваиваем выходному параметру, хранящему количество книг, числящихся

за читателем значение ноль */

Return 1

END

else

/* если значение переменной @ N_R не равно нулю, то читатель с заданными

характеристиками был ранее записан в нашей библиотеке */

BEGIN

/* определение количества книг у читателя с найденным номером читательского

билета */

select @COUNT_BOOKS = COUNT(INV_NUMBER) FROM EXEMPLAR WHERE

NUM_READER = @N_R

select @Count_books = COALESCE( @COUNT_BOOKS.0)

/* присваиваем выходному параметру @COUNT_BOOKS значение, равное количеству

книг, которые числятся за нашим читателем, если в предыдущем запросе

@COUNT_BOOKS было присвоено неопределенное значение, то мы заменим его

на ноль, используя для этого встроенную функцию COALESCE(@COUNT_BOOKS.0),

которая возвращает первое определенное значение из списка значений,

заданных в качестве ее параметров */

Select @Y_N = 1

/* присваиваем выходному параметру @Y_N значение 1, что соответствует тому,

что данный читатель ранее в нашей библиотеке был записан */

268

Select @NUM_READER = @N_R

/* присваиваем выходному параметру @NUM_READER определенный ранее номер

читательского билета */

return 0

end

Теперь посмотрим, как работает наша новая процедура, для этого в режиме интерактивного выполнения запросов (то есть в Query Analyzer MS SQL Server 7.0) запишем следующую последовательность команд:

-- пример использования выходных параметров при вызове процедуры

-- new reader

-- зададим необходимые нам переменные

Declare @K int. @N int. @B int

exec NEW_READER ?Пушкин В.В.?.?Литовский 22-90?.

?333-55-99?. ?444-66-88?. ?01.06.83?. @NUM_READER =@K OUTPUT.

@Y_N = @N OUTPUT.@COUNT_BOOKS = @B OUTPUT

-- теперь выведем результаты работы нашей процедуры используя ранее

-- определенные нами переменные

Select ?номер билета?,@К.?да - нет?,@N.?кол-во книг?.@В

Мы получим результат:

Номер билета	да - нет	кол-во книг
18	0	0

Если же мы снова запустим нашу процедуру с теми же параметрами, то есть повторим выполнение подготовленных выше операторов, то получим уже иной ответ:

Номер билета	да - нет	кол-во книг
18	1	0

и это означает, что господин Пушкин В. В. уже записан в нашей библиотеке, но он не успел взять ни одной книги, поэтому за ним числится 0 (ноль) книг.

Теперь обратимся к оценке эффективности применения хранимых процедур.

Если рассмотреть этапы выполнения одинакового текста части приложения, содержащего SQL-операторы, самостоятельно на клиенте и в качестве хранимой процедуры, то можно отметить, что на клиенте выполняются все 5 этапов выполнения SQL-операторов, а хранимая процедура может храниться в БД в уже скомпилированном виде, и ее исполнение займет гораздо меньше времени (см. рис. 12.2).

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

269

Рис. 12.2. Процесс выполнения операторов SQL на клиенте и процесс выполнения хранимой процедуры

Хранимые процедуры также играют ключевую роль в повышении быстродействия при работе в сети с архитектурой "клиент - сервер".

На рис. 12.3 показан пример выполнения последовательности операторов SQL на клиенте, а на рис. 12.4 показан пример выполнения той же последовательности операторов SQL, оформленных в виде хранимой процедуры. В этом случае клиент обращается к серверу только для выполнения команды запуска хранимой процедуры. Сама хранимая процедура выполняется на сервере. Объем пересылаемой по сети информации резко сокращается во втором случае.

Рис. 12.3. Сетевой трафик при выполнении встроенных SQL - операторов

270

Рис. 12.4. Сетевой трафик при выполнении хранимой процедуры на сервере

Содержание раздела