Подробно синтаксис процедур описан

• для InterBase - в документации Language Reference, раздел Procedures and Triggers
• для Firebird - базовое описание соответствует синтаксису InterBase 6, расширения описаны в Release Notes соответствующей версии Firebird.

Процедуры выглядят следующим образом:
(квадратными скобками [] выделены необязательные элементы)

create procedure <имя_процедуры>
 [(входные_параметры)]
 [returns =(выходные_параметры)]
as
 [список_локальных_переменных]
begin
 <код_процедуры>
end

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

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

Дальше в статье будут изложены примитивные примеры. Если вы хотите посмотреть больше примеров, откройте базу данных employee.gdb/fdb, которая входит в состав любого дистрибутива Firebird или InterBase.

Процедуры без параметров

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

create procedure test
as
begin
  insert into testtable (field1) values (1);
end

Каждый оператор в процедуре должен быть завершен символом ;

В такой процедуре могут быть объявлены локальные переменные (declare variable), для организации каких-либо вычислений. Например

create procedure test
as
declare variable i int;
begin
  i=1;
  insert into testtable (field1) values (:i);
end

Такие процедуры вызываются оператором

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

Например:

IBQuery1.SQL.Clear;
IBQuery1.SQL.Add('execute procedure test');
IBQuery1.ExecQuery;

примечание: частая ошибка начинающих - попытка вызвать IBQuery1.Open вместо IBQuery1.ExecQuery. Метод IBQuery1.Open используется для выполнения запросов select. IBQuery1.ExecQuery используется для запросов insert, update, delete, и execute procedure.

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

IBStoredProc1.StoredProcName:='test';
IBStoredProc1.ExecProc;

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

Пример

create procedure test (i int)
as
begin
  insert into testtable (field1) values (:i);
end

вызывается следующим образом:

EXECUTE PROCEDURE <имя_процедуры> (список_параметров_через_запятую)

обрамление списка параметров круглыми скобками необязательно, но желательно.

IBQuery1.SQL.Clear; 
IBQuery1.SQL.Add('execute procedure test (:param1)');
IBQuery1.ParamByName('param1').asInteger:=5;
IBQuery1.ExecQuery;

IBStoredProc1.StoredProcName:='test';
IBStoredProc1.ParamByName('param1').asInteger:=5;
IBStoredProc1.ExecProc;

Использование входных параметров и локальных переменных

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

select field1 from table
where field2 > :param1
into :param2;

Двоеточие не указывается только в том случае, когда переменной присваивают какое-либо значение через =

i=:i+1;

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

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

create procedure test 
retuns (s varchar(30))
as 
begin   
  select currency 
  from country
  where country = 'England'
  into :s;
  s=:s || '---';
end

Вы можете создать эту процедуру в базе employee, и при выполнении процедура выдаст результат выполнения запроса - строку 'Pound ---'. Команда

s=:s || '---';

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

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

IBQuery1.SQL.Clear;  
IBQuery1.ExecQuery;
ShowMessage(IBQuery1.Fields[0].asString);
или
mystringvar:=IBQuery1.Fields[0].asString;

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

...:=IBQuery1.FieldByName('s').asString;

Если необходимо выполнить такую процедуру в другой процедуре, то это можно сделать

EXECUTE PROCEDURE <имя_процедуры> RETURNING_VALUES <список_переменных>

Например

create procedure test2
as
declare variable s2 varchar(30);
beigin
  execute procedure test returning_values :s2;
end

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

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

Обработка данных в процедурах

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

select currency    
from country   
where country = 'England'   
into :s;

После выполнения запроса в переменной s окажется значение выбранного столбца currency. Если запрос не вернул ни одной записи, то в переменной s окажется то значение, которое было раньше!

s='none';
select currency 
from country 
where country = 'Russia' 
into :s;

Поскольку записи о валюте России в таблице country базы employee по умолчанию нет, то в переменной s останется строка 'none'. Помните об этом. Если такая ситуация может возникнуть, обязательно инициализируйте переменные или значением null, или другим значением, по которому вы можете определить, что запрос не вернул ничего - например такое значение, которого в этом столбце в этой таблице не может быть никогда.

Если такой запрос вернет 2 или более записи, то вы получите сообщение

multiple rows in singleton select

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

Для обработки запросов, возвращающих множество записей, используется конструкция FOR SELECT

create procedure calcsalarysum
returns (sm numeric(15,2))
as
declare variable s numeric(15,2);
begin
  sm=0;
  for select salary
      from employee
      into :s
  do
    sm=:sm+:s;
end;

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

select sum(salary) from employee

Однако, этот пример показывает возможность обработки данных каждой записи, возвращаемой запросом. Блок do может быть сколь угодно сложным, и состоять из нескольких операторов в блоке begin end

for select ...
do 
begin
 ...
 ...
end

где может быть еще один вызов for select, и т.п.

Селективные процедуры

Как вы уже видели в предыдущих разделах, процедуры могут возвращать данные. Если использовать execute procedure, то можно получить всегда только одно значение, или как бы "одну строку", в виде набора значений, но не "несколько строк".

Процедуры в InterBase и Firebird могут выдавать данные таким образом, что их можно вызывать через select. Отсюда и название - "селективные" процедуры.

Пример:

create procedure test
returns (n varchar(35))
as
declare variable ln varchar(20);
declare variable fn varchar(15);
begin
  for select last_name, first_name
      from employee
      into :ln, :fn
  do
    begin
      n=:fn ||' '|| :ln;
     <b> suspend;</b>
    end
end

Процедура перебирает все записи таблицы employee и возвращает нам "склеенные" имя и фамилию сотрудников. Размер возвращаемой переменной n выбран как сумма размеров столбцов first_name и last_name таблицы empoyee и переменных ln и fn, чтобы во время обработки не возникло переполнения.

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

Если мы вызовем эту процедуру как

EXECUTE PROCEDURE TEST

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

select * from test

IBQuery1.SQL.Clear;
IBQuery1.SQL.Add('select * from test');
IBQuery1.Open;

Вот тут, как видите, вместо ExecQuery мы уже должны использовать Open, чтобы открыть набор записей как обычный запрос. Дальше можно перебирать записи по IBQuery1.Next, или подключить к IBQuery1 DataSource и DBGrid, и т.д.

IBStoredProc в этом случае использовать невозможно, т.к. он не умеет вызывать процедуры никаким образом кроме EXECUTE PROCEDURE (т.е. не является DataSet-ом, не смотря на то что унаследован от CustomDataSet).

SUSPEND можно использовать не только с for select, но и вообще, если вы хотите выдать данные как "запись". Возьмем процедуру с подсчетом суммы зарплат, и расширим ее

create procedure calcsalarysum
returns (fn varchar(15), ln varchar(20), sm numeric(15,2))
as
declare variable s numeric(15,2);
begin
  sm=0;
  for select first_name, last_name, salary
      from employee
      into :fn, ln, :s
  do
    begin
      sm=:sm+:s;
<b>      suspend;</b>
    end
  fn='';
  ln='';
  s=:sm;
<b>  suspend;</b>
end;

Теперь в процедуре два suspend (выделены жирным шрифтом). Первый "выталкивает" имя, фамилию и зарплату в выходные переменные. Второй - выдает последней "записью" пустые имя и фамилию и итоговую сумму, которая предварительно накоплена пременной sm.

Вот еще один вариант селективной процедуры, который вообще не содержит обращений к данным

create procedure test
returns (i int)
as
begin
  i=5;
  suspend;
  i=3;
  suspend;
  i=9;
  suspend;
 
end

Если выполнить такую процедуру через select * from test, то она вернет 3 записи со значениями 5, 3 и 9.

Если процедура не имеет выходных переменных, то suspend использовать нельзя (как минимум, в этом нет смысла).

Вызывать селективные процедуры через execute procedure можно, если требуется получить только одно, первое значение. Точно так же можно вызвать селективную процедуру через select * from myproc, и после выборки первой записи закрыть датасет (IBQuery1.Close;

Рекурсивные процедуры

Пример взят из DataDef.pdf (InterBase 6.0), страница 139 (глава 9 стр 15)

CREATE PROCEDURE FACTORIAL (NUM INT)
   RETURNS (N_FACTORIAL DOUBLE PRECISION)
AS
DECLARE VARIABLE NUM_LESS_ONE INT;
BEGIN
  IF (NUM = 1) THEN
  BEGIN /**** BASE CASE: 1 FACTORIAL IS 1 ****/
    N_FACTORIAL = 1;
    SUSPEND;
  END
  ELSE
  BEGIN /**** RECURSION: NUM FACTORIAL = NUM * (NUM-1) FACTORIAL ****/
    NUM_LESS_ONE = NUM - 1;
    EXECUTE PROCEDURE FACTORIAL NUM_LESS_ONE
    RETURNING_VALUES N_FACTORIAL;
    N_FACTORIAL = N_FACTORIAL * NUM;
    SUSPEND;
  END
END;

В данном случае вычисление факториала - всего-лишь пример. Другой пример - имитация реестра в БД (процедура get_registry_subkeys).

Особенность вложенных вызовов процедур и рекурсивных процедур

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

Поэтому, модифицировать вызываемые процедуры нужно следующим образом:

Допустим, у нас есть процедура A, у которой был параметр ID INT. И она вызывается из процедур B и C. Вы хотите добавить процедуре A дополнительный параметр, N VARCHAR(10).

1. вначале вызов процедуры (A), у которой вы планируете изменить параметры (количество и тип), нужно закомментировать во всех процедурах и триггерах, которые ее вызывают (B и C).
   обычно инструменты разработчика позволяют показывать зависимости, т.е. например какие процедуры зависят от изменяемой. Некоторые инструменты позволяют даже перекомпилировать зависимые процедуры или вообще все с автоматическим контролем изменяемых параметров.
2. затем вы меняете количество параметров у процедуры (A), и проверяете ее работоспособность (и меняя код с учетом изменения параметров) .
3. затем вы по очереди раскомментируете вызов процедуры (A) в вызывавших ее процедурах (B и C), проверяя их работоспособность (и меняя код с учетом изменения параметров)

примечание: изменение типа параметров, без изменения количества параметров, не обязательно сразу приведет к ошибкам при вызове такой процедуры из других процедур или триггеров. Все будет зависеть от конвертируемости данных между старым типом параметра и новым. То есть, три варианта - 1) ошибка есть сразу из-за неконвертируемости данных между типами; 2) ошибки нет, т.к. типы конвертируемые; 3) ошибка может возникнуть на определенных данных (буквы при varchar -> int).

примечание: быстро проверить, не возникнет-ли проблема с вызывающими процедурами после модификации процедур, можно сделав бэкап только метаданных (gbak -m ...) и затем их восстановление (restore). Этот процесс не делает резервное копирование данных, и таким образом намного быстрее. Подробнее см. документацию по утилите gbak.