DECLARE [VARIABLE] имя локальной переменной <тип данных> [{ = DEFAULT} <значение>];

DECLARE [VARIABLE] имя локальной переменной <тип данных> [{ = DEFAULT} <значение>]; .

Для изменения определения процедуры используется запрос, имеющий следующий формат:

{ ALTER RECREATE CREATE OR ALTER } PROCEDURE имя процедуры

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

[,входной параметр2 <тип данных> <значение>] ])] [RETURNS (выходной параметр1 <тип данных> [,выходной параметр2 <тип данных>

AS <тело процедуры> [разделитель .

Если используется запрос ALTER PROCEDURE, то имя процедуры должно быть именем существующей процедуры. Это мягкий способ изменения кода процедуры, потому что если у нее есть зависимости (т. е. процедура используется другими объектами БД, например, другой ХП), на которые логически не влияют изменения, то они будут сохранены.

Запрос RECREATE PROCEDURE идентичен запросу CREATE

PROCEDURE, за исключением того, что для существующей процедуры с тем же именем он сначала выполняет ее удаление перед созданием нового объекта. Следует учесть, что запрос RECREATE PROCEDURE не будет выполнен, если процедура используется другими объектами БД.

Запрос CREATE OR ALTER PROCEDURE создает процедуру, если она не существует, иначе изменяет определение существующей процедуры и перекомпилирует ее. При этом имеющиеся зависимости и привилегии сохраняются.

Удаление хранимых процедур осуществляется запросом DROP PROCEDURE, который имеет следующий формат:

DROP PROCEDURE имя процедуры; .

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

Хранимые процедуры Firebird условно подразделяются на два типа: процедуры выбора (select procedure) и выполняемые процедуры (executable procedure). Каждая из этих процедур имеет разное назначение. Создание ХП обоих типов формально не отличается, но отличается их вызов. Также отличаются операторы, которые влияют на ход выполнения ХП.

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

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

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

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

<метка>: <оператор цикла> LEAVE [<метка>]

где <оператор цикла> представляет собой оператор WHILE, неявный курсор

или оператор EXECUTE STATEMENT с циклом FOR;

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

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

FOR SELECT RequestCD, COALESCE(ExecutionDate, Дата неизвестна)

FROM Request

INTO :Code, :Exec Date

BEGIN

IF (Exec Date = Дата неизвестна) THEN LEAVE; --выход из цикла выборки курсора

ELSE

SUSPEND; END

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

ROWCOUNT типа INTEGER. Она должна использоваться в том же блоке, что и запрос, например внутри тела ХП следующим образом:

UPDATE Abonent SET Fio = :Fio WHERE AccountCD = :Account; IF (ROW COUNT = 0) THEN INSERT INTO Abonent (AccountCD, Fio) VALUES (:Account, :Fio);

В данном примере выполняется запрос на обновление в таблице Abonent поля Fio новым значением для абонента с заданным номером лицевого счета (номер лицевого счета :Account и ФИО :Fio - входные параметры). Если строки с заданным номером лицевого счета нет в таблице, то контекстная переменная ROW COUNT принимает значение ноль и далее выполняется вставка строки в таблицу с данными номером лицевого счета и фамилией.

Контекстная переменная ROWCOUNT также может применяться в цикле выборки (FETCH) при использовании явного курсора, как уже было показано ранее при изучении курсоров.

6.2.2. Процедуры выбора

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

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

Например, пусть стоит задача получения какой-то информации из таблиц БД и для этой цели используется запрос с группировкой по какому-то полю внешнего ключа, представляющего собой код, значение которого расшифровывается в другой таблице. В запросе с группировкой не удастся включить в список возвращаемых элементов расшифровку значения кода внешнего ключа из другой таблицы (например, название улицы по внешнему ключу в таблице абонентов учебной БД). Если использовать в хранимой процедуре два последовательно выполняемых запроса - один на группировку, другой на расшифровку кода, а затем возвращать полученные результаты в виде одного набора данных, то, во-первых, можно упростить получение какой-либо аналитической информации (в данном примере), и, во-вторых, оптимизировать этот процесс за счет использования хранимой процедуры.

Для выполнения процедуры выбора используется запрос SELECT, в предложении FROM которого указывается имя ХП. Синтаксис запроса SELECT в этом случае имеет следующий вид:

SELECT <список возвращаемых элементов>

FROM имя процедуры ([ входной параметр1 [,входной параметр2 ]])

[WHERE <условие поиска>]

ORDER BY <элемент сортировки1> [, <элемент сортировки2>] ];.