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

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

Разновидности хранимых процедур:

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

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

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

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

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

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

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

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

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

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

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

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

SELECT MAX_KOLVO

FROM FIND_MAX_KOLVO(«МОЛОКО»)

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

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

ALTER PROCEDURE <имя процедуры>

[(<входной параметр > <тип данных>

[,<входной параметр > <тип данных> …])]

[RETURNS

[(<выходной параметр > <тип данных>

[,<выходной параметр > <тип данных> …])]

AS

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

Принципы построения оператора аналогичны принципам построения оператора CREATE PROCEDURE.

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

DROP PROCEDURE <имя процедуры>