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

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

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

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

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

• выполняемые хранимые процедуры (executable stored procedures) и
• хранимые процедуры выбора или селективные (selectable stored procedures).

Выполняемые хранимые процедуры осуществляют обработку данных, находящихся в базе данных, или вовсе не связанных с базой данных. Эти процедуры могут получать входные параметры и возвращать выходные параметры. Обращение к выполняемым хранимым процедурам осуществляется при выполнении оператора SQL EXECUTE PROCEDURE или CALL.

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

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

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

CREATE PROCEDURE <имя хранимой процедуры>
   [(<входной параметр> [, <входной параметр> ...])]
   [RETURNS (<выходной параметр> [, <выходной параметр> ...])]
AS
   [<объявление> [<объявление> ...] ]
BEGIN
   <блок операторов>
END

CREATE PROCEDURE является составным оператором, состоящим из заголовка и тела. Заголовок определяет имя хранимой процедуры и объявляет входные и выходные параметры, если они должны быть возвращены процедурой. Тело процедуры состоит из необязательных объявлений локальных переменных, подпрограмм и именованных курсоров, и одного или нескольких операторов, или блоков операторов, заключённых во внешнем блоке, который начинается с ключевого слова BEGIN, и завершается ключевым словом END. Объявления локальных переменных и именованных курсоров, а также внутренние операторы должны завершаться точкой с запятой (;).

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

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

Для изменения существующей хранимой процедуры используется оператор ALTER PROCEDURE.

ALTER PROCEDURE <имя хранимой процедуры> [(<входной пар-р>[,<входной пар-р>...])]
[RETURNS (<выходной параметр> [, <выходной параметр> ...])]
AS
   [<объявление> [<объявление> ...] ]
BEGIN
   <блок операторов>
END 

Возможно одновременное применение оператора CREATE OR ALTER PROCEDURE.

CREATE OR ALTER PROCEDURE <имя хранимой процедуры> [(<входной пар-р>[,<входной пар-р>...])]
[RETURNS (<выходной параметр> [, <выходной параметр> ...])]
AS
   [<объявление> [<объявление> ...] ]
BEGIN
   <блок операторов>
END 

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

Для удаления существующей хранимой процедуры используется оператор DROP PROCEDURE.

DROP PROCEDURE <имя хранимой процедуры>

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

CREATE OR ALTER PROCEDURE R(N INT) RETURNS (I INT)
AS
BEGIN
    WHILE (N > 0) DO
    BEGIN
        N = N - 1;
        I = RAND() * 100;
        SUSPEND;
    END
END;

SELECT * FROM R(5);

Контрольные вопросы

1. Какие виды хранимых процедур вы знаете?
2. Перечислите преимущества применения хранимых процедур?
3. Кто может вызывать хранимую процедуру?
4. Сколько строк может вернуть селективная хранимая процедура?
5. Каким образом вызывается селективная хранимая процедура?