28. Работа с хранимыми процедурами

 

28.1. Понятие хранимой процедуры *

28.2. Создание хранимой процедуры *

28.3. Алгоритмический язык хранимых процедур *

28.3.1. Объявление локальных переменных *

28.3.2. Операторные скобки BEGIN... END *

28.3.3. Оператор присваивания *

28.3.4. Оператор IF... THEN ... ELSE *

28.3.5. Оператор SELECT *

28.3.6. Оператор FOR SELECT... DO *

28.3.7. Оператор SUSPEND *

28.3.8. Оператор WHILE... DO *

28.3.9. Оператор EXIT *

28.3.10. Оператор EXECUTE PROCEDURE *

28.3.11. Оператор POST_EVENT *

28.4. Вызов процедур выбора в приложении клиента *

28.5. Обращение к процедурам действия. *

28.6. Изменение и удаление хранимых процедур *

28. Работа с хранимыми процедурами

28.1. Понятие хранимой процедуры

Хранимая процедура - это модуль, написанный на процедурном языке InterBase и хранящийся в базе данных как метаданные (то есть как данные о данных). Хранимую процедуру можно вызывать из приложения.

Существует две разновидности хранимых процедур: процедуры выбора и процедуры действия.

Процедуры выбора могут возвращать более одного значения. В приложении имя хранимой процедуры выбора подставляется в оператор SELECT вместо имени таблицы или обзора.

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

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

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

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

• разгрузка приложений клиента путем переноса части кода на сервер и вследствие этого - упрощение клиентских приложений;

• при изменении хранимой процедуры на сервере все изменения немедленно становятся доступны для клиентских приложений; при внесении же изменений в приложение клиента требуется повторное распространение новой версии клиентского приложения между пользователями;

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

28.2. Создание хранимой процедуры

Хранимая процедура создается оператором

CREATE PROCEDURE ИмяПроцедуры

[ (входной_параметр тип_данньк [, входной_параметр тип_данных...])]

[RETURNS

(выходной_параметр тип_данньк [, выходной_параметр тип_данньк ...])] AS

<тело процедуры>;

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

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

И входные, и выходные параметры могут быть опущены, если в них нет необходимости.

Тело процедуры имеет формат

[<объявление локальных переменных процедуры>]

BEGIN

< оператор>

[<оператор> ...]

END

Пример. Хранимая процедура FIND_MAX_KOLVO возвращает в выходном параметре MAX_KOLVO максимальное количество отгруженного со склада товара, наименование которого передается во входном параметре IN_TOVAR:

CREATE PROCEDURE FIND_MAX_KOLVO (IN_TOVAR VARCHAR(20))

RETURNS(MAX_KOLVO INTEGER) AS

BEGIN

SELECT MAX(KOLVO) FROM RASHOD WHERE TOVAR = : IN_TOVAR

INTO : MAX_KOLVO;

SUSPEND;

END

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

28.3. Алгоритмический язык хранимых процедур

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

Рассмотрим конструкции алгоритмического языка хранимых процедур и триггеров.

28.3.1. Объявление локальных переменных

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

Формат объявления локальных переменных:

DECLARE VARIABLE <имя переменной > <тип данных>;

Пример объявления:

CREATE PROCEDURE FULL_ADR (TOVARCHIK VARCHAR(20))

RETURNS (GOROD_ADRES VARCHAR(40)) AS

DECLARE VARIABLE NAIDEN_POKUPATEL VARCHAR(20);

DECLARE VARIABLE MAX_KOLVO INTEGERS;

BEGIN

END

28.3.2. Операторные скобки BEGIN... END

Операторные скобки BEGIN ... END, во-первых, ограничивают тело процедуры, а во-вторых, могут использоваться для указания границ составного оператора.

Под простым оператором понимается единичное разрешенное действие, например:

РОК = "Покупатель не указан";

Под составным оператором понимается группа простых или составных операторов, заключенная в операторные скобки BEGIN ... END.

28.3.3. Оператор присваивания

Оператор присваивания служит для занесения значений в переменные. Его формат:

Имя переменной = выражение;

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

OUT_TOVAR = UРРЕR(TOVAR);

28.3.4. Оператор IF... THEN ... ELSE

Условный оператор IF ... THEN ... ELSE имеет формат

IF (<условие>) THEN

< оператор 1>

[ELSE

< оператор 2>]

В случае, если условие истинно, выполняется оператор 1, если ложно -оператор 2.

28.3.5. Оператор SELECT

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

INTO :переменная |, переменная...]

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

Пример. Приводимый ниже оператор SELECT возвращает среднее и сумму по столбцу KOLVO и записывает их соответственно в AVG_KOLVO и SUM_KOLVO, которые могут быть как локальными переменными, так и выходными параметрами процедуры. Расчет среднего и суммы по столбцу KOLVO производится только для записей, у которых значение столбца TOVAR совпадает с содержимым IN_TOVAR (входной параметр или локальная переменная).

SELECT AVG(KOLVO), SUM(KOLVO)

FROM RASHOD WHERE TOVAR = :IN TOVAR

INTO : AVG_KOLVO, :SUM_KOLVO;

28.3.6. Оператор FOR SELECT... DO

Оператор FOR SELECT ... DO имеет следующий формат:

FOR

<оператор SELECT>

DO

< оператор>;

Оператор SELECT представляется в расширенном синтаксисе оператора SELECT для алгоритмического языка хранимых процедур и триггеров, то есть в нем может присутствовать предложение INTO.

Алгоритм работа оператора FOR SELECT... DO заключается в следующем. Выполняется оператор SELECT, и для каждой строки результирующего набора данных, возвращаемого данным SELECT, выполняется оператор, следующий за словом DO. Этим оператором часто бывает SUSPEND (см. ниже), который приводит к возврату выходных параметров в вызывающее приложение.

Пример. Процедура RASHOD_TOVARA выдает все расходы товара для конкретного товара, определяемого содержимым входного параметра IN_TOVAR. Рассмотрим логику работы оператора FOR SELECT . . . DO данной процедуры.

CREATE PROCEDURE RASHOD_TOVARA(IN_TOVAR VARCHAR(20))

RETURNS (OUT_DAT DATE, OUT_POKUP VARCHAR(20), OUT_KOLVO INTEGER) AS

BEGIN

FOR SELECT DAT_RASH, POKUP, KOLVO FROM RASHOD WHERE TOVAR = : IN_TOVAR

INTO :OUT DAT, :OUT_POKUP, :OUT_KOLVO

DO SUSPEND;

END

Выполняется оператор SELECT, который возвращает дату расхода, наименование покупателя и количество расхода товара для каждой записи, у которой столбец TOVAR содержит значение, идентичное значению во входном параметре IN_TOVAR. Указанные значения записываются в выходные параметры (соответственно OUT_DAT, OUT_POKUP, OUT_KOLVO). Имени параметра в этом случае предшествует двоеточие. После выдачи каждой записи результирующего НД выполняется оператор, следующий за словом DO. В данном случае это оператор SUSPEND. Он возвращает значения выходных параметров вызвавшему приложению и приостанавливает выполнение процедуры до запроса следующей порции выходных параметров от вызывающего приложения.

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

SELECT MAX_KOLVO FROM FIND_MAX_KOLVO("Сахар")

28.3.7. Оператор SUSPEND

Оператор SUSPEND передает в вызывающее приложение значения результирующих параметров (перечисленных после слова RETURNS в описании функции), имеющие место на момент выполнения SUSPEND. После этого выполнение хранимой процедуры приостанавливается. Когда от оператора SELECT вызывающего приложения приходит запрос на следующее значение выходных параметров, выполнение хранимой процедуры возобновляется.

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

CREATE PROCEDURE POK_LIST (IN_TOVAR VARCHAR(20))

RETURNS(РОК VARCHARf20)) AS

DECLARE VARIABLE AVG_KOLVO INTEGER;

BEGIN

SELECT AVG(KOLVO) FROM RASHOD WHERE TOVAR = :IN_TOVAR INTO : AVG_KOLVO;

FOR SELECT POKUP FROM RASHOD WHERE KOLVO > : AVG_KOLVO INTO : РОК

DO

BEGIN

IF (:POK IS NULL) THEN РОК = "Покупатель не указан";

SUSPEND;

END

END

28.3.8. Оператор WHILE... DO

Оператор имеет формат

WHILE (<условие>) DO < оператор>

Алгоритм выполнения оператора- в цикле проверяется выполнение условия, если оно истинно, выполняется оператор. Цикл продолжается до тех пор, пока условие не перестанет выполняться.

Пример. Рассмотрим процедуру SUM_0_N, которая подсчитывает сумму всех чисел от 0 до числа, определяемого входным параметром N Вычисление суммы реализовано в цикле с использованием оператора WHILE .. DO.

CREATE PROCEDURE SUM_0_N (N INTEGER)

RETURNS(S INTEGER) AS

DECLARE VARIABLE TMP INTEGER;

BEGIN

S = 0;

TMP = 1;

WHILE (TMP <= N) DO

BEGIN

S = S + TMP;

TMP = TMP + 1;

END

END

28.3.9. Оператор EXIT

Оператор EXIT инициирует прекращение выполнения процедуры и выход в вызывающее приложение.

Пример. Процедура MAX_VALUE возвращает максимум из двух чисел, передаваемых как входные параметры, в случае, если одно из чисел имеет значение NULL, процедура завершается (в этом случае выходной параметр содержит значение NULL)'

CREATE PROCEDURE MAX_VALUE(A INTEGER, В INTEGER)

RETURNS(M_V INTEGER) AS

BEGIN

IF ((:A IS NULL) OR (:B IS NULL )) THEN EXIT;

IF (:A > :B) THEN M_V = : A;

ELSE M_V = :B;

END

28.3.10. Оператор EXECUTE PROCEDURE

Оператор

EXECUTE PROCEDURE имя [параметр (, параметр...]];

[RETURNING_VALUES параметр [, параметр...]];

Выполняет другую хранимую процедуру из тела данной хранимой процедуры. При этом после слова PROCEDURE перечисляются входные параметры, если они есть, а после RETURNING_VALUES перечисляются выходные параметры.

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

CREATE PROCEDURE AVG_KOLVO (TVR VARCHAR(20)

RETURNS(OUT_AVG_KOLVO INTEGER)

AS

BEGIN

SELECT AVG(KOLVO) FROM RASHOD WHERE TOVAR = :TVR INTO :OUT_AVG_KOLVO;

SUSPEND;

END

CREATE PROCEDURE POK_LIST1 (IN_TOVAR VARCHAR(20))

RETURNS(РОК VARCHAR(20)) AS

DECLARE VARIABLE AVG_KOLVO INTEGER;

BEGIN

EXECUTE PROCEDURE AVG_KOLVO(:IN_TOVAR)

RETURNING_VALUES : AVG_KOLVO;

FOR SELECT POKUP FROM RASHOD WHERE KOLVO > : AVG_KOLVO INTO : РОК

DO

BEGIN

IF (:POK IS NULL) THEN РОК = "Покупатель не указан";

SUSPEND;

END

END

28.3.11. Оператор POST_EVENT

Оператор POST_EVENT "Имя события";

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

28.4. Вызов процедур выбора в приложении клиента

Процедуры выбора могут возвращать несколько значений одного и того же выходного параметра или группы выходных параметров. Возврат текущего значения выходных параметров соответствует возврату строки. Таким образом, процедура выбора возвращает НД, состоящий в общем случае из нескольких строк. Для выдачи каждого значения выходных параметров в вызывающее приложение в хранимой процедуре выбора применяется оператор SUSPEND. Формирование строки результирующего НД (то есть текущих значений выходных параметров) производится операторами SELECT...INTO или FOR...SELECT.

Для обращения к хранимой процедуре выбора в приложении клиента используется компонент TQuery. Вызов хранимой процедуры производится в операторе SELECT, в предложении FROM, с указанием входных параметров процедуры. Выходные параметры процедуры (все или часть) указываются в качестве возвращаемых значений оператора SELECT.

ЗАМЕЧАНИЕ. Для обращения к хранимой процедуре выбора может использоваться и компонент TStoredProc, открываемый при помощи метода Open. Однако в общем случае возможность обращения к процедуре выбора из компонента TStoredProc определяется особенностями SQL-сервера. К хранимым процедурам выбора, определенным в Borland InterBase, рекомендуется обращаться из оператора SELECT, который помещается в свойство SQL компонента TQuery.

Пример. Выше приводилась процедура выбора RASHOD_TOVARA, которая выдает все расходы товара для конкретного товара, определяемого содержимым входного параметра IN_TOVAR:

CREATE PROCEDURE RASHOD_TOVARA(IN_TOVAR VARCHAR(20))

RETURNS (OUT_DAT DATE, OUT_POKUP VARCHAR(20), OUT_KOLVO

INTEGER) AS

BEGIN

FOR SELECT DAT_RASH, POKUP, KOLVO FROM RASHOD WHERE TOVAR = : IN_TOVAR

INTO :OUT_DAT, :OUT_POKUP, :OUT_KOLVO

DO

SUSPEND;

END

Для того чтобы обратиться к данной процедуре из клиентского приложения, разработанного на Delphi, создадим форму (рис. 28.1) разместив в ней:

1. компонент TDatabase, осуществляющий управление соединением с удаленной БД;

2. компонент TTable, ассоциированный с таблицей TOVARY, для выбора названия товара для передачи в хранимую процедуру как входного параметра;

3. компонент TQuery;

4. компонент TButton для инициации доступа к процедуре.

В свойстве SQL компонента TQuery необходимо определить SQL-запрос на обращение к процедуре:

SELECT *

FROM RASHOD_TOVARA(:PARAM1)

а в свойстве Params компонента TQuery необходимо указать тип параметра PARAM1 как String. В обработчике нажатия кнопки происходит присваивание параметру значения текущего названия товара из компонента TTable, после чего компонент TQuery активизируется:

procedure TForm1.ButtonlClick(Sender: TObject);

begin

Query1.Close;

Query1.ParamByName('param1').Value :=

Table1.FieldByName('TOVAR').Value;

Query1.Open; end;

Результаты работы приложения показаны на рисунке 28.2.

28.5. Обращение к процедурам действия.

Компонент TStoredProc

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

В утилите WISQL InterBase хранимую процедуру действия можно вызвать при помощи оператора

EXECUTE PROCEDURE имя [параметр [, параметр ...|];

Например, вызов процедуры STOIM с указанием двух входных параметров приведет к выдаче значения выходного параметра в окне результатов WISQL (рис. 28.3).

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

Из приложения клиента процедуру действия вызывают при помощи компонента TStoredProc. Для этого в его свойство DatabaseName помещают псевдоним удаленной БД, в которой расположена хранимая процедура. Затем выбирают имя соответствующей хранимой процедуры из выпадающего списка в свойстве StoredProcNawe компонента TStoredProc. При этом происходит считывание имен входных и выходных параметров и их типов. Их можно увидеть, нажав кнопку (...) в свойстве Params или вызвав редактор определения параметров компонента TStoredProc. Для этого необходимо сделать компонент текущим, нажать правую кнопку мыши и в появившемся меню выбрать опцию Define Parameters. Вид представления параметров в редакторе определения параметров компонента TStoredProc показан на рисунке 28.4.

В окне Parameter Name указываются имена параметров. Поле Parameter Type устанавливает тип параметра (Input, Output). Поле Data Type определяет тип данных параметра. Поле Value устанавливает значение параметра по умолчанию.

ЗАМЕЧАНИЕ. При считывании характеристик параметров непосредственно из хранимой процедуры Delphi блокирует кнопки Add, Delete, Clear, предназначенные для добавления, удаления, очистки характеристик параметра. Для других случаев параметры можно определять явно. При этом необходимо заботиться о соответствии типов данных параметров процедуры и компонента TStoredProc. Имена параметров и порядок их следования могут не совпадать с именами и порядком следования параметров в хранимой процедуре на сервере. В этом случае указать соответствие параметров компонента и процедуры на сервере можно, используя свойство компонента TStoredProc

property ParamBindMode: TParamBindMode;

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

pbByName - (по умолчанию) соответствие параметров ведется по именам (имена одинаковых параметров в компоненте и процедуре на сервере должны совпадать);

pbByNumber - соответствие параметров производится по их порядковым номерам: производится попытка первый по порядку параметр компонента соотнести с первым по порядку параметром процедуры на сервере, второй - со вторым и т.д.

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

function ParamByName(const Value: string): TParam;

Например,

// входной параметр StoredProc2.ParamByName('IN_TOVAR').Value :=

Table1.FieldByName('TOVAR').Value;

// выходной параметр

Label5.Caption := IntToStr(StoredProc2.ParamByName('MAX_KOLVO').Value) ;

Перед выполнением процедуры нужно произвести связывание параметров компонента TStoredProc и параметров хранимой процедуры при помощи метода

procedure Prepare;

Вызов хранимой процедуры действия осуществляется методом procedure ExecProc;

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

Процедура FIND_MAX_KOLVO возвращает в выходном параметре MAX_KOLVO максимальную партию отгруженного товара, наименование которого передается во входном параметре IN_TOVAR:

CREATE PROCEDURE FIND_MAX_KOLVO (IN_TOVAR VARCHAR(20))

RETURNS(MAX_KOLVO INTEGER) AS

BEGIN

SELECT MAX(KOLVO) FROM RASHOD WHERE TOVAR = : IN_TOVAR INTO : MAX_KOLVO;

SUSPEND;

END

Процедура STOIM возвращает стоимость количества товара. Параметр IN_KOLVO определяет число единиц товара; IN_TOVAR определяет название товара.

CREATE PROCEDURE STOIM (IN_TOVAR VARCHAR (20) , IN_KOLVO INTEGER)

RETURNS(OUT_STOIM INTEGER) AS

DECLARE VARIABLE ZENA_ED INTEGER;

BEGIN

SELECT ZENA FROM TOVARY WHERE TOVAR = :IN_TOVAR INTO : ZENA_ED;

OUT_STOIM = ZENA_ED * IN_KOLVO;

END

Расположим в форме:

1. компонент TTable, ассоциированный с таблицей TOVARY, для выбора названия товара для его передачи в хранимую процедуру как входного параметра;

2. компонент TStoredProc (имя StoredProc!) для выполнения процедуры STOIM;

3. компонент TStoredProc (имя StoredProc2) для выполнения процедуры FIND_MAX_KOLVO;

4. компонент TButton для инициации выполнения хранимых процедур;

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

Напишем обработчик нажатия кнопки, в котором происходит присваивание значений входных параметров и получение значений выходных параметров процедур:

procedure TForm1.Button1Click(Sender: TObject);

begin

StoredProc2.UnPrepare;

StoredProc1.UnPrepare;

StoredProc1.ParamByName('IN_TOVAR').Value := Table1.FieldByName('TOVAR').Value;

StoredProc1.ParamByName('IN_KOLVO').Value := StoredProc2.ParamByName('MAX_KOLVO').Value;

StoredProc1.Prepare;

StoredProc1.ExecProc;

Label3.Caption :=IntToStr(StoredProc!.ParamByName('OUT_STOIM').Value) ;

StoredProc2.ParamByName('IN_TOVAR').Value := Table1.FieldByName('TOVAR').Value;

StoredProc2.Prepare;

StoredProc2.ExecProc;

Label5.Caption := IntToStr(StoredProc2.ParamByName('MAX_KOLVO').Value) ;

end;

Результат работы приложения приведен на рисунке 28.5.

Рис. 28.5. Значения максимального отпуска товара (количество и стоимость) возвращаются хранимой процедурой выбора как выходные параметры

28.6. Изменение и удаление хранимых процедур

Изменение хранимой процедуры производится оператором

ALTER PROCEDURE ИмяПроцедуры

[ (входной_параметр тип_данньк [, входной_параметр тип_данных... ]) ]

[RETURNS

(входной параметр тип данных [.входной параметр тип данных ...])]

AS

<тело процедуры>;

Принципы построения оператора аналогичны изложенным выше принципам построения оператора CREATE PROCEDURE. После выполнения оператора ALTER PROCEDURE предыдущее определение процедуры заменяется на данное определение параметров, переменных и тела процедуры.

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

DROP PROCEDURE ИмяПроцедуры;