Карпова Т.С. Базы данных: модели, разработка, реализация

Подождите немного. Документ загружается.

Хранимые

процедуры

. 261

надо

будет

хранить

отдельно

некотором

текстовом файле.

это

не

самое пло-

хое,

по

вот

случае

восстановления

БД

после

серьезной

аварии

для

перекомпи-

ляции

потребуются

первоначальные

исходные

тексты

всех хранимых процедур.

Поэтому защита вещь

хорошая,

но

она

усложняет

сопровождение

модифика-

цию

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

Однако кроме имени хранимой

процедуры

все

остальные

параметры являются

необязательными.

Процедуры могут быть

процедурами

или

процедурам

и-функ-

циями.

И эти

понятия

здесь трактуются

традиционно,

как в

языках программи-

рования

высокого

уровня.

Хранимая

процедура-функция

возвращает

значение,

которое

присваивается

переменной, определяющей

имя

процедуры. Процедура

явном

виде

не

возвращает

значение,

но

ней

может

быть

использовано

клю-

чевое

слово

OUTPUT,

которое

определяет,

что

данный

параметр

является

вы-

ходным.

Рассмотрим

несколько

примеров

простейших

хранимых

процедур.

процедура проверки наличия

экземпляров

данной книги

параметры:

@ISBN

шифр

книги

процедура

возвращает

параметр,

равный количеству

экземпляров

Если

возвращается

ноль,

то это

значит,

что нет

свободных

экземпляров данной

книги

библиотеке.

CREATE

PROCEDURE

COUNTJX

(&ISBM

varchar(12))

определим

внутреннюю переменную

DECLARE

@TEK_COUNT

Int

выполним соответствующий

оператор

SELECT

Будем

считать только

экземпляры,

которые

настоящий

момент

находятся

не

на

руках

читателей,

библиотеке

select

CTEK_COUNT

select

count(*>

FROM

EXEMPLAR

WHERE

ISBN

GlSBN

AND

READERJD

NULL

AND

EXIST

True

/* 0 -

ноль

означает,

что нет ни

одного свободного экземпляра данной книги

библиотеке

RETURN

@TEKj;OUNT

Хранимая

процедура может быть вызнана

несколькими

способами.

Простейший

способ

— это

использование

оператора:

EXEC

<имя

процедуры*

*значенце_входного_параметра1>...

<имя

переменной_для_выходного

параметра!*-

При

этом

все

входные

выходные

параметры

должны

быть

заданы

обязательно

в том

порядке,

котором

они

определены

процедуре.

262

Глава

12,

Встроенный

SQL

Например,

если

мне

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

«OracIeS.

Энциклопе-

дия

пользователя»,

которая имеет ISBN

966-7393-08-09,

то

текст вызова

ранее

созданной

хранимой процедуры может быть следующим:

/Определили

две

переменные

@Ntek

количество

экземпляров

данной

книги

наличие

библиотеке

OISBN

международный

шифр

книги

declare

@Ntek

int

DECLARE

(3ISBN

VARCHAR(14)

Присвоим

значение

переменной

@ISBN

Select

@1SBN

'966-7393-08-09'

Присвоим

переменной

@Ntek

результаты выполнения

хранимой

процедуры

ОШ_ЕХ

EXEC

@Mtek

COUNTJX

&ISBH

Если

у вас

определено несколько версий

хранимой

процедуры,

то при

вызове

вы

можете указать

номер

конкретной версии

для

исполнения. Так,

например,

версии

процедуры

СОШТ_ЕХ

последний оператор исполнения этой про-

цедуры

имеет

вид:

EXEC

@Htek

COUNT_EX:2

@ISBN

Однако

если

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

по

умолча-

нию,

то при

запуске процедуры могут быть

указаны

значения

не

всех

парамет-

ров.

этом случае оператор вызова процедуры может быть записан

следую-

щем

виде;

EXEC

<имя

процедуры>

<имя_параметра1>

<значение

параметра!^..

<иия_параметра№>

<значение

параметра^-.

Например,

создадим

процедуру,

которая

считает

количество

книг,

изданных

конкретным

издательством

конкретном

году.

При

создании

процедуры

зада-

дим

для

года издания

по

умолчанию

значение

текущего года.

CREATE

PROCEDURE

COUNTBOOKS

(0YEARIZD

1nt

Year(GetDateO).

0PUBLICH

varchar(20))

процедура подсчета количества

книг

конкретного

издательства, изданных

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

параметры:

0YEARIZD

1nt год

издания

0PUBLICH

название

издательства

DECLARE

0TEK_Count

1nt

Select

@TEK

count

Select

COUNT(ISBN)

Хранимые

процедуры

263

From

BOOKS

Where

YEARIZO

OYEARIZD

AND

PU8LICH

=@PUBLICH

одновременно

исполнением

оператора

Select

ны

присваиваем

результаты

его

работы

определенной

ранее

переменной

@TEK_Count

/* при

формировании

результата работы

нашей

процедуры

мы

должны

учесть,

что в

нашей

библиотеке,

возможно,

нет ни

одной

книги

некоторого

издательства

для

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

SELECT

этом

случае будет

иметь

неопределенное

значение,

но

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

значения.

Поэтому

качестве

возвращаемого

значения

мы

используем

результаты

работы специальной встроенной функции

Transact

SQL

COALESCE

(nl,n2

nm),

которая

возвращает первое

конкретное,

то

есть

не

равное NULL,

значение

из

списка значений

nl.n2

nm. */

Return

COALESCE

(@TEK_Count.O)

Теперь

вызовем

эту

процедуру,

для

этого подготовим переменную, куда

можно

поместить

результаты выполнения процедуры.

declare

int

Exec

COUNTJOOKS

@PUBLICH

'Питер'

переменной

мы

получим количество книг

нашей

библиотеке,

изданных

издательством «Питер»

текущем году.

Мы

можем обратиться

этой процеду-

ре

задав

все

параметры:

Exec

COUNTJOOKS

0PUBLICH

'BHW,

0YEARIZD

1999

Тогда получим количество

книг,

изданных

издательством

«BHW»

1999

году

присутствующих

нашей библиотеке.

Если

мы

задаем параметры

по

именам,

то нам

необязательно

задавать

их в том

порядке,

котором

они

описаны

при

создании

процедуры.

Каждая

хранимая

процедура

является

объектом

БД. Она

имеет уникальное

имя

уникальный

внутренний номер

системном каталоге.

При

изменении

текста,

хранимой

процедуры

мы

должны

сначала

уничтожить данную процедуру

как

объект, хранимый

в БД,

только после этого записать

на ее

место новую.

Сле-

дует

отметить,

что при

удалении

хранимой

процедуры удаляются

одновременно

все

ее

версии, нельзя

удалить

только одну версию хранимой

процедуры.

Для

того чтобы автоматизировать процесс уничтожения старой процедуры

замены

се

на

новую,

начале

текста

хранимой

процедуры

можно

выполнить

проверку

наличия

объекта типа «хранимая

процедура»

данным

именем

сис-

темном

каталоге

при

наличии

описания данного объекта удалить

его из

сис-

темного каталога.

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

спе-

циальным

оператором

проверки

может иметь,

например,

вид:

проверка

существования

системном каталоге объекта

данным

именем

типом,

созданного

владельцем

БД */

exists (select

from

sysobjects

where

objectJd('dbo.NEWJOOKS')

and

sysstat

Oxf

264

Глава

12.

Встроенный

SQL

если объект

существует,

то

сначала

его

удалим

из

системного

каталога

drop procedure

dbo.NEW_BOOKS

CREATE PROCEDURE

NEWJOOKS

(QISBN

varchar(12).iaTITL

varchar(255).(3AUTOR

varchar(30).@COAUTOR

varchar(30),@YEARIZD

int.PPAGES

INT.@NUMJXEMPL

INT)

процедура

ввода

новой книги

указанием

количества

экземпляров

данной

книги

параметры

@ISBN

varchar(12)

шифр

книги

@TIU

var

char(255)

название

0AUTOR

varcharOO)

автор

0COAUTOR

varchar(SO)

соавтор

@YEARIZD

int

год

издания

@PAGES

INT

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

@NUM_EXEMPL

INT

количество

экземпляров

/*опишем

переменную,

которой

будет

храниться

количество

оставшихся

не

оприходованных экземпляров

книги,

т.е.

таких,

которым

еще не

заданы

инвентарные

номера

DECLARE

@ТЕК

int

*вводим данные

книге

таблицу BOOKS

INSERT INTO

BOOKS

VALUES(iaiSBN.(aTITL.@AUTOR,@COAUTOR.PYEARIZD,iapAGES)

назначение значения текущего счетчика

осташихся

вводу

экземпляров*/

SELECT

(ЭТЕК

0NUMJXEMPL

организуем цикл

для

ввода новых экземпляров данной

книги

WHILE

@ТЕК>0

пока количество оставшихся экземпляров больше нуля

BEGIN

/* так как для

инвентарного номера

экземпляра

книги

мы

задали свойство

IDENTITY,

то нам не

надо вводить инвентарный

номер,

СУБД

сама

автоматически

вычислит

его,'

добавив единицу

предыдущему,

'введет

при

выполнении оператора

ввода INSERT.

Поле, определяющее присутствие

экземпляра

библиотеке

(EXIST)

логическое

поле,

мы

введем туда значение

TRUE.которое

соответствует присутствию

экземпляра

книги

библиотеке.

Даты

взятия

возврата

мы

можем

не

заполнять, тогда

по

умолчанию СУБД

подставит туда значение,

соответствующее

января

1900

года,

если

мы не

хотим

хранить

такие

бессмысленные данные,

то

можем ввести

для

обоих полей дата

время,

значения текущей

даты.

Хранимые

процедуры

265

insert

Into

EXEMPLAR

(ISBN.DATAJN.DATA-OUT.EXIST)

VALUES

«aiSBN,GetDate().GetDate()).TRUE)

изменение

текущего значения счетчика количества оставшихся

экземпляров

SELECT

@ТЕК

ffFEK - 1

End

конец цикла ввода данных

экземпляре

книги*/

Если

мы не

использовали

ннкремснтное

поле

качестве

инвентарного номера

экземпляра,

то мы

могли

бы

сами

назначать

инвентарный

номер, увеличивая

на

единицу

номер

последнего

хранимого

библиотеке

экземпляра

книги.

Можно

было

бы

попробовать просто сосчитать количество существующих экземпляров

библиотеке,

но мы

могли удалить

некоторые,

тогда номер нового

экземпля-

ра

может

быть

уже

использован,

и мы не

сможем

ввести

данные,

система

не

по-

зволит

нам

нарушить

уникальность

первичного

ключа.

Текст

процедуры

этом случае

будет

иметь

вид:

проверка существования

системной

каталоге объекта

данным

именем

типом,

созданного владельцем

БД */

exists

(select

from

sysobjects

where'id

objectJd('dbo.NEW_BOOKS'}

and

sysstat

Oxf

если

объект

существует,

то

сначала

его

удалим

из

системного каталога

drop

procedure

dbo.NEWJJOOKS

CREATE PROCEDURE

NEW

BOOKS

(@ISBN

varchar(12).@TITL

varchar(255),0AUTOR

varchar(30)KOAUTOR~varchar(30).iaYEARIZD

int.tapAGES

INT.GNUMJXEMPL

INT)

процедура

ввода

новой книги

указанием

количества

экземпляров данной книги

параметры

@ISBN

varchar(12)

шифр

книги

@TITL

varchar(255)

название

@AUTOR

varcharOQ)

автор

0COAUTOR

varcharOO)

соавтор

@YEARIZD

int год

издания

@PAGES

INT

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

0NUMJXEMPL

INT

количество

экземпляров

DECLARE

@ТЁК

int

declare

@INV

int

INSERT

INTO

BOOKS

VALUESdaiSBN^TITL.PAUTOR.iaCOAUTOR^YEARlZD^PAGES)

назначение значения текущего счетчика

осташихся

вводу

экземпляров*/

SELECT

@ТЕК

GNUMJXEMPL

определение

максимального

значения инвентарного

номера

библиотеке

266

Глава

12.

Встроенный

SQL'

SELECT

@INV

SELECT

MAX(

IDJXEMPLAR)

FROM

EXEMPLAR

организуем цикл

для

ввода новых экземпляров данной

книги

WHILE

@TEK>0

пока

количество

оставшихся

экземпляров

больше

нуля

BEGIN

insert

into EXEMPLAR

CIO_EXEMPLAR.ISBN,DATA_IN,DATA_OUT,EXIST)

VALUES

(@INV.@ISBN.GETDATE().GetDate().

TRUE)

изменение

текущих значений счетчика

инвентарного номера

SELECT

@ТЕК

- 1

SELECT

@INV

0INV

+ 1

End

конец цикла

ввода

данных

экземпляре

книги*/

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

могут

вызывать одна другую. Создадим хранимую

проце-

дур^

которая возвращает

номер

читательского

билета

для

конкретного читателя.

exists (select

from

sysobjects

where

objectJdCdbo.

CK_READER')

and

sysstat

& Oxf

если объект

существует,

то

сначала

его

удалим

из

системного каталога

drop

procedure

dbo.CK_READER

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

если

читатель есть

и 0 в

противном

случае.

качестве

параметров

передаем

фамилию

дату

рождения

CREATE

PROCEDURE

CK_READER

C@FIRST.NAME

varchar(30).@BIRTH_DAY

varchar(12))

/*опишем

переменную,

которой будет храниться

номер

читательского

билета*/

DECLARE

GNUMREADER

INT

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

select

@NUM_READER

select

NUMJEADER

from

READERS

WHERE

FIRSTJAME

AND

convert(varchar(8),BIRTH_DAY,4)=(aBIRTH_DAY

RETURN

COALESCE(@NUM_READER,0)

Мы

здесь

использовали функцию преобразования типа данных

dataTime

в тип

данных varchar(8).

Это

было необходимо сделать

для

согласования типов дан-

ных

при

выполнении операции сравнения.

Действительно,

входная

переменная

@BIRTH_DAY

имеет символьный

тип

(varchar),

поле базы данных

BIRTH.DAY

имеет

тип

SmallDateTime,

Хранимые

процедуры допускают

наличие

нескольких выходных параметров.

Для

этого каждый выходной параметр

должен

после

задания

своего типа дан-

ных

иметь

дополнительное

ключевое слово

OUTPUT.

Рассмотрим пример

храни-

мой

процедуры

несколькими выходными параметрами.

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

267

Создадим процедуру

ввода

нового

читателя,

при

этом внутри процедуры выпол-

ним

проверку

наличия

нашей картотеке данного читателя, чтобы

не

назначать

ему

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

При

этом выходными параметрами про-

цедуры

будут

номер

читательского

билета,

признак того,

был ли

ранее

записан

читатель

данными характеристиками

нашей

библиотеке,

если

он был

запи-

сан,

то

сколько

книг

за ним

числится.

проверка

наличия

данной процедуры

нашей

БД*/

exists (select

from

sysobjects

where

object_id(N'[dbo].[NEW_READER]'>

and

OBJECTPROPERTYtid,

N'IsProcedure

)

drop

procedure

[dbo].[NEWSREADER]

процедура проверки существования читателя

заданными

значениями

вводимых

параметров

Процедура

возвращает

новый

номер

читательского билета, если такого читателя

не

было сообщает старый номер

количество

книг,

которое должен

читатель

противном

случае

CREATE

PROCEDURE

NEW

READER

(PNAME

READER

varchar(30).@ADRES

varchar(4G),№)M_PHONE

char(9).@UORK_PHONE

char(9).

GBIRTHJJAY

varchar(8).

@NUM_READER

1nt

OUTPUT.

выходной

параметр,

определяющий

номер

читательского

билета*/

@Y_N

int

OUTPUT.

выходной

параметр,

определяющий

был ли

читатель

ранее записан

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

KOUNTsBOOKS

int

OUTPUT

выходной

параметр,

определяющий

количество книг, которое числится

за

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

переменная,

которой будет храниться

номер

читательского

билета,

если

читатель

уже был

записан

библиотеку

DECLARE

@N_R

int

определение

наличия

читателя

EXEC

@NJ

CK_READER

@NAMEJEAOER.(aBIRTH_DAY

@N_R=

0 Or

@N_R

Null

если читатель

заданными характеристиками

не

найден,

т. е,

переменной

@N_R

присвоено значение нуль

или ее

значение

неопределено,

перейдем

назначению

для

нового читателя нового номера читательского билета

BEGIN

/* так как

мы

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

как

инкренентное

поле,

то в

операторе ввода

мы

его

не

указываем система сана назначит

новому

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

268

Глава

12.

встроенный

SQL

INSERT

INTO

READER

NAM£_READER.

ADRES,

HOOM_PHONE.

WORK

JTOE.

BIRTHJJAY)

VALUES

(@NAME_REAOER.@ADRES.@HOOH

PHONE,0WORK

PHONE,Convertfsmalldatetlme,

@BIRTH_DAY,4)

)

/* в

операторе INSERT

мы

должны преобразовать

символьную

переменную

@BIRTHJ)AY

тип

данных

smalldatetime.

который определен

для

поля дата рождения

BIRTHJJAY.

Это

преобразование

мы

сделаем

помощью

встроенной

функции

Transact

SQL

Convert

теперь

определим

назначенный

номер

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

select

@NUM_READER

NUM_READER FROM

READER

WHERE NAME_READER

@NAME_READER

AND

convert(varchar(S).BIRTH_DAY.4)-@BIRTH_DAY

здесь

мы

снова используем функцию преобразования типа,

но в

этом случае

нам

необходимо

преобразовать

поле

BIRTHJ3AY

из

типа

sfflalldatetime

типу

varchar(S),

котором задан входной параметр

@B1RTH_DAY

Select

@Y_N

присваиваем выходному параметру

@Y_N

значение

(ноль),

что

соответствует

тому,что

данный читатель ранее

нашей

библиотеке

не

был

записан

Select

@COUNT_BQOKS

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

книг,

числящихся

за

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

Return

END

else

если значение переменной

@N__R

не

равно нулю,

jro

читатель

заданными

характеристиками

был

ранее

записан

нашей библиотеке

BEGIN

определение количества

книг

читателя

найденным

номером

читательского

билета

select

@COUNT

BOOKS

COUNTCINV

NUMBER)

FROM EXEMPLAR WHERE

NUMJEADER~=

@N_R

select

ecountjooks

COALESCE(

(aCOUNT_BOOKS,0)

присваиваем выходному

параметру

(aCQUNTJOQKS

значение, равное количеству

книг,

которые числятся

за

нашим читателем, если

предыдущем запросе

0COUNTJOOKS

было

присвоено неопределенное

значение,

то мы

заменим

его

на

нолГ,

используя

для

этого

встроенную функцию

COALESCE«acOUNT_BOOKS.Q),

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

из

списка

значений,

заданных

качестве

ее

параметров

Select

@YJ1

Присваиваем

выходному

параметру

@YJ

значение

1. что

соответствует

тому,

что

данный

читатель ранее

нашей библиотеке

был

записан

Хранимые

процедуры

269

Select

ONUMJEADER

@N_R

присваиваем

выходному

параметру

@NUM_READER

определенный ранее Номер

читательского

билета

return

end

Теперь

посмотрим,

как

работает наша

новая

процедура,

для

этого

режиме

ин-

терактивного

выполнения запросов

(то

есть

Query

Analyzer

MS SQL

Server

7.0)

запишем

следующую

последовательность команд:

пример

использования

выходных

параметров

при

вызове процедуры

-- new

reader

зададим необходимые

нам

переменные

Declare

int.

int

exec

NEW_READER

'Пушкин

В.В.','Литовский

22-90',

'333-55-99','444-66-88'.'01.Об.83'.ШМ

READER

=@K

OUTPUT.

0YJ

OUTPUT.@COUNT_BQOKS

OUTPUT

-•

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

определенные

нами

переменные

Select

'номер

билета

.@К,'дакнет'Ж'кол-во

книг'.@В

Мы

получим результат;

Номер

билета да-нет

кол-во

книг

18 О О

Если

же

мы

слова

запустим

нашу

процедуру

теми

же

параметрами,

то

есть

повторим

выполнение

подготовленных

выше

операторов,

то

получим

уже

иной

ответ;

Номер

билета

да-нет

кол-во

книг

18 1 О

это

означает,

что

господин

Пушкин

В. В. уже

записан

нашей

библиотеке,

но

он

не

успел взять

ни

одной

книги,

поэтому

за

ним

числится

(ноль)

книг.

Теперь

обратимся

оценке

эффективности

применения

хранимых

процедур.

Если

рассмотреть этапы

выполнения

одинакового

тексга

части

приложения,

со-

держащего SQL-операторы,

самостоятельно

на

клиенте

и в

качестве

хранимой

процедуры,

то

можно

отметить,

что

на

клиенте

выполняются

все 5

этапов

вы-

полнения

SQL-операторов,

хранимая

процедура

может храниться

в БД в уже

скомпилированном

виде,

и ее

исполнение

займет

гораздо

меньше

времени

(см,

рис.

12.2).

Кроме

того,

хранимые

процедуры,

как уже

упоминалось,

могут быть использо-

ваны

несколькими

приложениями,

встроенные операторы

SQL

должны быть

включены

каждое

приложение

повторно.

Хранимые

процедуры

также играют ключевую

роль

повышении быстродейст-

вия

при

работе

сети

архитектурой

«клиент—сервер».

На

рис. 12.3 показан пример выполнения последовательности операторов

SQL

на

клиенте,

а на

рис. 12.4 показан

пример

выполнения

той же

последовательно-

сти

операторов

SQL,

оформленных

виде

хранимой

процедуры.

этом

случае

клиент обращается

серверу только

для

выполнения

команды запуска храни-

мой

процедуры. Сама хранимая процедура

выполняется

на

сервере. Объем

пе-

ресылаемой

по

сети информации резко

сокращается

во

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