Вызов хранимых процедур в Firebird и InterBase

kdv, iBase.ru, 04.03.2009
последнее обновление 24.03.2013, 03.10.2014, 07.06.2016

статья для начинающих

в статье примеры вызовов процедур из программ на Delphi приведены для компонент IBX (закладка InterBase), и могут быть легко перенесены на любые другие компоненты доступа.

Хранимые процедуры (и триггеры) поддерживаются в InterBase очень давно, минимум 16 лет. И нынешняя поддержка процедур в InterBase и Firebird такая же, только содержит больше расширений и возможностей.

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

для InterBase - в документации Language Reference, раздел Procedures and Triggers
для Firebird - «Руководство по языку SQL СУБД Firebird 2.5», в формате pdf, на русском языке.

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

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 <имя процедуры>

Например:

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

примечание: частая ошибка начинающих - для запросов, не возвращающих данные, вызывать метод Open вместо ExecQuery (ExecSQL, ExecProc). Метод Open используется для выполнения запросов select. IBSQL.ExecQuery (или IBQuery.ExecSQL) используется для запросов 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 
returns (s varchar(30))
as 
begin
  s='aaa'||'bbb';
end

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

s='aaa'||'bbb';

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

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

IBSQL1.SQL.Clear;
IBSQL1.SQL.Add('EXECUTE PROCEDURE TEST');  
IBSQL1.ExecQuery;
ShowMessage(IBSQL1.Current[0].asString);
или
mystringvar:=IBSQL1.Current[0].asString;

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

...:=IBSQL1.Current.ByName('s').asString;

Этот же код можно выполнить при помощи IBStoredProc, тогда вместо ExecQuery будет ExecProc, а вместо Current будет Fields, вместо Current.ByName - FiedlByName и т.д. При помощи IBQuery выполнить процедуру таким образом можно, но код IBQuery.ExecSQL в этом случае не запишет в Fields значения, возвращаемые процедурой.

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

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

Например

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

В переменной s2 мы получим значение, возвращенное из процедуры test.

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

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

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

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

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, или другим значением, по которому вы можете определить, что запрос не вернул ничего - например такое значение, которого в этом столбце в этой таблице не может быть никогда.

примечание: в Firebird 2.0 и выше переменная ROW_COUNT позволяет после выполнения оператора DML определить, сколько записей он обработал (в версии 1.5 row_count для select всегда возвращал 0) . Если select из предыдущего примера не вернул ни одной записи, то ROW_COUNT будет равно 0. Таким образом, обработку "пустых" запросов можно упростить следующим образом:

select currency  from country  where country = 'USA'  into :s;
if (ROW_COUNT = 0) then -- запрос не вернул ни одной записи 
...

Разумеется, ROW_COUNT можно использовать и для запросов UPDATE, DELETE (для INSERT нет смысла, т.к. он или вставит 1 запись, или вернет ошибку).

Если же запрос вернет 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;
      suspend;
    end
end

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

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

В Firebird 2.5 и выше попытка select из процедуры, которая не содержит SUSPEND, выдаст ошибку
Procedure ... is not selectable (it does not contain a SUSPEND statement).

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

EXECUTE PROCEDURE TEST

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

select * from test

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

Вот тут, как видите мы уже должны использовать Open (а не ExecSQL), чтобы открыть набор записей как обычный запрос. Дальше можно перебирать записи по 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;
      suspend;
    end
  fn='';
  ln='';
  s=:sm;
  suspend;
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;

Вызов процедур из процедур или триггеров

Ничего сложного, просто несколько правил

если процедура ничего не возвращает, то она так и вызывается:
execute procedure proc (params);
если процедура возвращает один набор значений, то вызывается с опцией RETURNING_VALUES
execute procedure proc (params) returning values (values);
если процедура селективная, то она вызывается точно так же, как и запросом для выборки данных
select ... from proc

Пример: далее по тексту - рекурсивный вызов процедуры factorial.

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

Пример взят из 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).

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

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

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

Планы процедур и производительность

Планы процедур

У процедур, как таковых, нет "планов" выполнения, аналогичных планам запросов. То есть, процедуры, это всего-лишь процедурный код (PSQL), который в том числе позволяет выполнять и запросы.
Однако, если мы выполним запрос с процедурой (как select, так и execute), первоначально выполнится операция prepare, по которой сервер выдаст "план запроса" (многие интерактивные инструменты позволяют просматривать такие планы).

Например, если мы выполним запрос к процедуре TEST (первая в разделе Селективные процедуры)

select * from test

мы получим план

PLAN (EMPLOYEE NATURAL)

но этот план на самом деле принадлежит запросу

select last_name, first_name from employee

который находится внутри процедуры.

Если же в нашей процедуре есть несколько запросов, например это процедура DEPT_BUDGET из БД примеров employee, то при выполнении запроса

select * from DEPT_BUDGET('aaa')

мы получим план

PLAN (DEPARTMENT INDEX (RDB$PRIMARY5))(DEPARTMENT INDEX (RDB$FOREIGN6))(DEPARTMENT INDEX (RDB$FOREIGN6))

неприятность в том, что здесь три отдельных плана склеились в один - запросов в процедуре три:
вывод "суммарного" плана процедуры может содержать и отдельные строки PLAN для каждого запроса

1. SELECT budget FROM department WHERE dept_no = :dno
   PLAN (DEPARTMENT INDEX (RDB$PRIMARY5))

2. SELECT count(budget) FROM department WHERE head_dept = :dno
   PLAN (DEPARTMENT INDEX (RDB$FOREIGN6))

3. SELECT dept_no   FROM department   WHERE head_dept = :dno
   PLAN (DEPARTMENT INDEX (RDB$FOREIGN6))

кроме того, в процедуре есть условие

IF (cnt = 0) THEN SUSPEND;

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

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

В Firebird 3.0 вместо плана запросов, входящих в процедуру, выдается план NATURAL, как будто бы записи из процедуры выбираются в натуральном порядке (так и есть). Например, запрос
select * from org_chart
выдаст план
PLAN (ORG_CHART NATURAL)

Производительность

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

Однако, достаточно часто возникает вопрос - почему один и тот же запрос, если его выполнить отдельно, выполняется быстро, а если выполнить в процедуре, то медленно?

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

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

самый типичный пример такого запроса - select * from table - где в таблице несколько миллионов записей. Такой запрос "выполнится" практически мгновенно, и мы увидим первую порцию данных, которая попала к нам на экран.

Но если мы "в гриде" нажмем Ctrl-End, т.е. дадим команду считать все записи с сервера, то дальнейшее будет происходить уже не так быстро.

Процедуры же, редко пишутся с той целью, чтобы просто через себя протаскивать данные из таблиц, без изменений. Обычно это какая-то обработка, и даже если запрос не содержит group by, order by или других "замедляющих" выполнение запроса операций, то в процедуре будут перебраны все записи, которые выдал бы запрос. То есть, это примерно эквивалентно упомянутому выше нажатию Ctrl-End.

Пример. Пишем процедуру, которая выдает сумму всех зарплат всех сотрудников:

create or alter procedure SUM_SALARY
   returns (s numeric(15,2))
as
 declare variable I numeric(15,2);
begin
  s=0; i=0;
  for select salary from employee
     into :i
     do
       begin
       s=:s + :i;
     end
  suspend;
end

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

А это значит, что для оценки производительности запроса, включенного в процедуру таким образом, при тестировании запроса нам нужно было бы не просто выполнить этот запрос, а выполнить его с операцией FetchAll (выбрать все записи). И тогда счетчики производительности инструмента (или получаемые от сервера) выдадут уже совершенно другой результат.

Собственно, производительность запросов вне процедур или в процедурах (или в триггерах) ничем не отличается, т.к. во всех случаях для одного запроса выполняется один и тот же скомпилированный BLR-код (binary language representation).

Обработка ошибок в процедурах

На эту тему читайте статью

Точки сохранения (savepoints) и механизм целостности в СУБД Firebird

дополнительно примеры обработки ошибок (и вызова ошибок) есть в процедурах базы employee.fdb, входящей в дистрибутив.

Документация

Руководство по языку SQL СУБД Firebird (Language Reference по Firebird 2.5)
Data Definition Guide и Language Reference для InterBase 7.x, 2007, 2009 входит в дистрибутив
Х. Борри. "Firebird: руководство разработчика баз данных"
Этот загадочный TIBStoredProc
Точки сохранения (savepoints) и механизм целостности в СУБД Firebird

Благодарности

TJ001 с sql.ru за указание на IBQuery.ExecSQL, который не возвращает данные при выполнении процедур по execute procedure
dimitr за напоминание о ROW_COUNT