CREATE PROCEDURE MyProc
(invar1 INTEGER, invar2 DATE)
RETURNS (outvar1 INTEGER, outvar2 VARCHAR(20), outvar3 DOUBLE PRECISION)
* имя каждого аргумента должно быть уникальным в процедуре. Тип данных может быть любым стандартным типом данных SQL за исключением массива типов данных;
* ключевое слово AS, которое обязательно:
CREATE PROCEDURE MyProc
(invar1INTEGER, invar2 DATE)
RETURNS(outvar1 INTEGER, outvar2 VARCFAR(20), outvar3 DOUBLE
PRECISION)
AS
Элементы тела
Синтаксис:
< тело-процедуры> = [<список-объявлений-переменных>] <составной-оператор>
Если вам нужно объявить локальные переменные, то это следует сделать далее. Каждое объявление завершается точкой с запятой. В версии 1.5 переменные при их объявлении могут инициализироваться. Синтаксис:
<список-объявлений-переменных> =
DECLARE [VARIABLE] переменная тип-данных [{'=' | DEFAULT} значение];
[DECLARE [VARIABLE] переменная тип-данных; . . .]
Пример:
CREATE PROCEDURE MyProc (
invar1 INTEGER,
invar2 DATE)
RETURNS (
outvar1 INTEGER,
outvar2 VARCHAR(20),
outvar3 DOUBLE PRECISION)
AS
DECLARE VARIABLE localvar integer DEFAULT 0;
DECLARE VARIABLE anothervar DOUBLE PRECISION = 0.00;
! ! !
ПРИМЕЧАНИЕ. Ключевое слово VARIABLE необязательно в версии 1.5 и выше.
. ! .
Следом идет главный блок кода, обозначенный в описании синтаксиса как <составной-оператор>. Он начинается ключевым словом BEGIN и заканчивается ключевым словом END.
Синтаксис:
<составной-оператор> =
BEGIN
<составной-оператор>
[<составной-оператор> ...]
END <терминатор>
Все структуры <составной-оператор> состоят из одного оператора и/или других структур <составной-оператор>, которые могут включать другие вложенные структуры, например:
CREATE PROCEDURE MyProc (
invar1 INTEGER,
invar2 DATE)
RETURNS (
outvar1 INTEGER,
outvar2 VARCHAR(20),
outvar3 DOUBLE PRECISION)
AS
DECLARE VARIABLE localvar integer DEFAULT 0;
DECLARE VARIABLE anothervar DOUBLE PRECISION = 0.00;
BEGIN
< составной-оператор>
END &
Элементами в <составной-оператор> могут быть: любой одиночный оператор, блок операторов и вложенные блоки операторов, заключенные в операторные скобки BEGIN и END. Блоки могут включать:
* операторы присваивания, устанавливающие значения локальным переменным и входным/выходным параметрам;
* операторы SELECT для помещения значений столбцов в переменные. Операторы SELECT должны иметь предложение INTO в качестве последнего предложения и объявления соответствующих локальных переменных или выходных аргументов для каждого выбранного столбца;
* структуры циклов, такие как FOR SELECT ... DO и WHILE ... DO для выполнения условных или циклических задач;
* структуры ветвления с использованием IF ... THEN ... [ELSE];
* операторы EXECUTE PROCEDURE для вызова других процедур с необязательным предложением RETURNING_VALUES для получения значений переменных. Допустима рекурсия;
* операторы SUSPEND и EXIT, возвращающие управление и, возможно, значения вызвавшему приложению или модулю PSQL;
* комментарии для аннотирования кода процедуры;
* операторы EXCEPTION для возврата приложениям пользовательских сообщений об ошибках или для задания условий для обработчиков исключений;
* операторы WHEN для обработки особых или общих условий ошибок.
* операторы POST_EVENT для добавления в стек сообщений о событиях. Пример:
BEGIN
FOR SELECT COL1, COL2, COL3, C0L4
FROM TABLEA INTO :COL1, :COL2, :COL3 DO
BEGIN
<операторы>
END
<операторы>
END &
SET TERM ; &
COMMIT;
Обратите внимание на завершение всего объявления процедуры символом терминатора, ранее определенного оператором SET TERM. После текста тела процедуры символ терминатора устанавливается в значение по умолчанию точка с запятой. Так поступать следует не всегда. В скриптах DDL, где вы объявляете несколько модулей PSQL, вы можете сохранять альтернативный оператор текущим. Некоторые люди на практике используют альтернативный терминатор во всех своих скриптах, таким образом резервируя точку с запятой только для завершения оператора PSQL. Здесь дело в личных предпочтениях.
Выполняемые процедуры
Когда вы работаете с языком хранимых процедур Firebird и структурами программных модулей, необходимо различать процедуры, которые выполняются с целью изменения данных, и процедуры, которые собираются возвращать виртуальную таблицу вызвавшему приложению, как и оператор SELECT. Первый вид больше всего знаком тем, кто использовал в работе другие системы управления базами данных, - это выполняемые процедуры.
Сложная обработка
Одним из наиболее очевидных и общих способов использования выполняемых процедур является выполнение сложных вычислений над входными данными и выполнение изменений одной или более таблиц. Сложные бизнес-правила и подпрограммы размещаются на сервере. Любое клиентское приложение с соответствующими разрешениями может вызывать эти подпрограммы и получать результаты, независимо от включающего языка программирования. Не говоря об экономии многих часов на программирование и тестирование, выполнение на стороне сервера снижает риск нарушения целостности, который возникает за счет повторения одних и тех же сложных операций в различных клиентских языковых средах.
Поддержка "живых" клиентских наборов
Многие клиентские интерфейсы реализуют классы наборов данных или наборов записей, которые получают выходные наборы от операторов SELECT. Такие клиентские классы обычно предоставляют методы DML, которые выбирают одну строку из буфера, хранящего выход курсора серверной стороны. Строка выбирается пользователем, и объект класса использует уникальный ключ строки для моделирования позиционированного изменения или удаления из таблицы базы данных. Для добавления объект "открывает пустую строку", вводит список столбцов того же типа, что и в буфере, и принимает ключ и другие значения в качестве входных данных для столбцов.
Одиночный оператор UPDATE, DELETE или INSERT в SQL может оперировать только с одной таблицей. Когда набор данных (набор записей) выбирается из обычной таблицы и содержит уникальный ключ таблицы, он может рассматриваться как "живой", потому что его методы могут передавать операторы UPDATE, DELETE или INSERT. Обычным термином для такого типа набора является естественно изменяемый. Набор, являющийся соединением нескольких таблиц, не будет естественно изменяемым. Выполняемые хранимые процедуры могут быть созданы с входными аргументами, которые принимают ключи и значения для множества таблиц и выполняют требуемые операции над каждой таблицей. Такая техника позволяет клиентским приложениям трактовать соединенные наборы, как если бы они были "живыми".
Операции в выполняемых процедурах
Практически любое манипулирование данными в SQL доступно в выполняемой хранимой процедуре. Все действия выполняются в контексте транзакции вызвавшей процедуру программы и подтверждаются, когда подтверждается эта транзакция. Для строк, измененных операциями в процедуре, создаются версии точно таким же способом, как если бы они были отправлены запросами DML с клиента.
Процедуры могут вызывать другие процедуры, передавая переменные в качестве входных аргументов и получая возвращаемые значения в переменные, используя предложение RETURNING_VALUES. Они могут добавлять одну или множество строк, изменять отдельные строки, формировать курсоры для серии строк для позиционирования изменений и удалений и выполнять поисковые изменения и удаления.