Создание хранимой процедуры
Хранимая процедура создается оператором:
CREATE PROCEDURE ИмяПроцедур
[ ( входной_параметр тип_данных
[, входной_параметр тип_данных … ] ) ]
[ RETURNS
( выходной_параметр тип_данных
[ , выходной_параметр тип_данных … ] ) ]
AS
[< объявление локальных переменных процедуры>]
BEGIN
< оператор >
[ < оператор > … ]
END ;
Пример:
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 ;
Входные параметры служат для передачи в процедуру значений из вызывающего приложения. Выходные параметры служат для возврата результирующих значений. Значения выходных параметров устанавливаются в теле процедуры и после окончания ее работы передаются в вызывающее приложение.
Алгоритмический язык хранимых процедур
Формат объявления локальных переменных:
DECLARE VARIABLE <имя переменной> < тип данных > ;
Пример:
CREATE PROCEDURE FULL_ADR (TOVAR VARCHAR(20) )
RETURNS GOROD_ADRES VARCHAR(40)) AS
DECLARE VARIABLE NAIDEN_POKUPATEL VARCHAR(20);
DECLARE VARIABLE MAX_KOLVO INTEGER;
BEGIN
….
END ;
Операторные скобки :
Используются для указания границ составного оператора
BEGIN
...
END ;
Оператор присваивания:
Имя_переменной = выражение ;
Пример.
OUT_TOVAR = UPPER(TOVAR);
Оператор условия :
IF ( < условие> ) THEN < оператор 1>
[ELSE < оператор 2 > ]:
Оператор SELECT
Оператор SELECT используется в ХП для выдачи единичной строки. К обычному формату SELECT в процедурный оператор добавлено предложение:
INTO :переменная [, переменная … ]
Пример:
…
SELECT AVG(KOLVO), SUM(KOLVO)
FROM RASHOD
WHERE TOVAR = :IN_TOVAR
INTO :AVG_KOLVO, :SUM_KOLVO;
Оператор FOR SELECT … DO
FOR < оператор SELECT >
DO < оператор > ;
Оператор SELECT используется в ХП в расширенном синтаксисе с INTO. Для каждой строки полученного результирующего НД выполняется оператор, следующий за словом DO. Этим оператором часто бывает SUSPEND, который приводит к возврату выходных параметров в вызывающее приложение.
Пример:
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
Оператор SUSPEND
Оператор SUSPEND передает в вызывающее приложение значения результирующих параметров (перечисленных после слова RETURNS в описании функции), имеющие место на момент выполнения SUSPEND. Когда от оператора SELECT вызывающего приложения приходит запрос на следующее значение выходных параметров , выполнение хранимой процедуры возобновляется.
Пример:
CREATE PROCEDURE POK_LIST (IN_TOVAR VARCHAR(20) )
RETURNS (POK VARCHAR(20) ) 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 :POK
DO
BEGIN
IF (:POK IS NULL) THEN
POK = “Покупатель не указан”;
SUSPEND;
END
END
Оператор WHILE … DO
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;
TM = TPM + 1;
END
END
Оператор EXIT
Оператор EXIT инициирует прекращение выполнения процедуры и выход в вызывающее приложение.
Пример:
CREATE PROCEDURE MAX_VALUE ( А INTEGER, B INTEGER )
RETURNS ( M_V INTEGER ) AS
DECLARE VARIABLE TMP INTEGER;
BEGIN
IF ( :A IS NULL OR :B IS NULL ) THEN EXIT;
IF ( :A > :B ) THEN M_V = :A ;
ELSE M_V = :B ;
END