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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Если выполнить такую процедуру через 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)

В данном случае вычисление факториала - всего-лишь пример. Другой пример - имитация реестра в БД (процедура 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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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