19. Хранимые процедуры (`PROCEDURE`)

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

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

Модульность - приложения, работающие с одной и той же базой данных, могут использовать одну и ту же хранимую процедуру, тем самым уменьшив размер кода приложения и устранив дублирование кода;
Упрощение поддержки приложений - при изменении хранимой процедуры, изменения отражаются сразу во всех приложениях, перекомпиляции;
Увеличение производительности - поскольку хранимые процедуры выполняются на стороне сервера, а не клиента, то это уменьшает сетевой трафик, что повышает производительностьь.

Существует два вида хранимых процедур — выполняемые хранимые процедуры (executable stored procedures) и селективные процедуры (selectable stored procedures).

Выполняемые хранимые процедуры, осуществляют обработку данных, находящихся в базе данных. Эти процедуры могут получать входные параметры и возвращать одиночный набор выходных (RETURNS) параметров. Такие процедуры выполняются с помощью оператора EXECUTE PROCEDURE.

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

Такие процедуры позволяют получать довольно сложные наборы данных, которые зачастую невозможно или весьма затруднительно получить с помощью обычных DSQL SELECT запросов. Обычно такие процедуры выполняют циклический процесс извлечения данных, возможно преобразуя их, прежде чем заполнить выходные переменные (параметры) новыми данными на каждой итерации цикла. Оператор SUSPEND, обычно расположенный в конце каждой итерации, заполняет буфер и ожидает пока вызывающая сторона не выберет (fetch) строку.

Селективные процедуры могут иметь входные параметры и выходное множество, заданное в предложении RETURNS в заголовке процедуры.

Обращение к селективной хранимой процедуре осуществляется при помощи оператора SELECT.

19.1. Создание хранимой процедуры

Синтаксис создания выполняемых хранимых процедур и селективных процедур ничем не отличается. Разница заключается в логике программного кода. Для создания хранимой процедуры используется оператор CREATE PROCEDURE, синтаксис которого представлен в листинге .

Листинг 19.1 Синтаксис оператора создания хранимой процедуры CREATE PROCEDURE

CREATE PROCEDURE <имя хранимой процедуры>
[AUTHID {OWNER | CALLER}]
   [(<входной параметр> [, <входной параметр> ...])]
[RETURNS (<выходной параметр> [, <выходной параметр> ...])]
[SQL SECURITY {DEFINER | INVOKER}]
{ EXTERNAL NAME '<внешний модуль>' ENGINE <имя движка> [AS <тело внешней проц.>] }
|
{
    {AS '<BLR код>'}
    |
    {AS
      [<объявление> [<объявление> ...] ]
    BEGIN
      <блок операторов>
    END }
}

<входной параметр> ::= <описание параметра> [{=|DEFAULT} <значение по умолчанию>]

<выходной параметр> ::= <описание параметра>

<описание параметра> ::= <имя параметра> <тип> [NOT NULL] [COLLATE <порядок сортировки>]

<тип> ::= { <тип данных SQL>
          | [TYPE OF] <имя домена>
          | TYPE OF COLUMN <имя таблицы/представления>.<имя столбца> }

<значение по умолчанию>::= {<литерал> | NULL | <контекстная переменная>}

<внешний модуль> ::= '<имя внешнего модуля>!<имя функции в модуле>[! <информация>]'

<объявление> ::= <объявление локальной переменной>;
               | <объявление курсора>;
               | <объявление процедуры/функции>
               | <реализация процедуры/функции>

Таблица 19.1 Параметры оператора CREATE PROCEDURE
Параметр	Описание
Имя хранимой процедуры	Имя хранимой процедуры. Может содержать до 63 символов.
Входной параметр	Описание входного параметра.
Выходной параметр	Описание выходного параметра.
Литерал	Литерал, совместимый по типу с параметром.
Контекстная переменная	Любая контекстная переменная, тип которой совместим с типом параметра.
Имя параметра	Имя входного или выходного параметра процедуры. Может содержать до 63 символов. Имя параметра должно быть уникальным среди входных и выходных параметров процедуры, а также её локальных переменных.
Тело внешней процедуры	Тело внешней процедуры. Строковый литерал который может использоваться UDR для различных целей.
Имя внешнего модуля	Имя внешнего модуля, в котором расположена функция.
Имя функции в модуле	Внутреннее имя функции внутри внешнего модуля.
Информация	Определяемая пользователем информация для передачи в функцию внешнего модуля.
Имя движка	Имя движка для использования внешних функций. Обычно указывается имя UDR.

Хранимую процедуру может создать администратор и пользователь с привилегией CREATE PROCEDURE. Пользователь, создавший хранимую процедуру, становится её владельцем.

Оператор CREATE PROCEDURE создаёт новую хранимую процедуру. Имя хранимой процедуры должно быть уникальным среди имён всех хранимых процедур, таблиц и представлений базы данных.

Примечание

Желательно также, чтобы имя хранимой процедуры было уникальным и среди имён процедур расположенных в PSQL пакетах, хотя это и допустимо. Дело в том, что в настоящее время вы не сможете вызвать функцию/процедуру из глобального пространства имён внутри пакета, если в пакете объявлена одноименная функция/процедура. В этом случае всегда будет вызвана процедура/функция пакета.

CREATE PROCEDURE является составным оператором, состоящим из заголовка и тела.

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

Тело процедуры состоит из необязательных объявлений локальных переменных, подпрограмм и именованных курсоров, и одного или нескольких операторов, или блоков операторов, заключённых во внешнем блоке, который начинается с ключевого слова BEGIN, и завершается ключевым словом END. Объявления локальных переменных и именованных курсоров, а также внутренние операторы должны завершаться точкой с запятой (;).

Привилегии выполнения

Чтобы указать в контексте какого пользователя будет выполняться процедура используются необязательные предложения AUTHID или SQL SECURITY. Совместное их использование недопустимо. Причем предложение AUTHID считается устаревшим и не будет поддерживаться начиная с РЕД Базы Данных 6.

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

с правами вызывающего ее пользователя (значение по умолчанию):

CREATE PROCEDURE MyProc AUTHID CALLER (...)
RETURNS (...)
AS BEGIN
...
END!

или

CREATE PROCEDURE MyProc (...)
RETURNS (...)
SQL SECURITY INVOKER
AS BEGIN
...
END!

с правами ее владельца (создателя):

CREATE PROCEDURE MyProc AUTHID OWNER (...)
RETURNS (...)
AS BEGIN
...
END!

CREATE PROCEDURE MyProc (...)
RETURNS (...)
SQL SECURITY DEFINER
AS BEGIN
...
END!

Значение по умолчанию на уровне всей базы данных можно изменить оператором ALTER DATABASE SET DEFAULT SQL SECURITY.

Параметры

У каждого параметра указывается тип данных. Кроме того, для параметра можно указать ограничение NOT NULL, тем самым запретив передавать в него значение NULL.

Для параметра строкового типа существует возможность задать порядок сортировки с помощью предложения COLLATE.

Входные параметры

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

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

Выходные параметры

Необязательное предложение RETURNS позволяет задать список выходных параметров хранимой процедуры.

Использование доменов при объявлении параметров

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

Использование доменов при объявлении параметров

В качестве типа параметра можно указать имя домена. В этом случае параметр будет наследовать все характеристики домена.

Если перед названием домена дополнительно используется предложение TYPE OF, то используется только тип данных домена - не проверяются его ограничения NOT NULL и CHECK (если они есть) и не используется значение по умолчанию. Если домен текстового типа, то всегда используется его набор символов и порядок сортировки.

Использование типа столбца при объявлении параметров

Входные и выходные параметры можно объявлять, используя тип данных столбцов существующих таблиц и представлений. Для этого используется предложение TYPE OF COLUMN, после которого указывается имя таблицы или представления и через точку имя столбца.

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

Тело хранимой процедуры

После ключевого слова AS следует тело хранимой процедуры.

Объявление локальных переменных, курсоров и подпрограмм

В необязательной секции <объявление> описаны локальные переменные процедуры, подпрограммы и именованные курсоры. В отношении спецификации типа данных локальные переменные подчиняются тем же правилам, что и входные и выходные параметры процедуры.

После необязательной секции деклараций обязательно следует составной оператор. Составной оператор состоит из одного или нескольких PSQL операторов, заключенных между ключевыми словами BEGIN и END. Составной оператор может содержать один или несколько других составных операторов. Вложенность ограничена 512 уровнями. Любой из BEGIN ... END блоков может быть пустым, в том числе и главный блок.

Внешние хранимые процедуры

Хранимая процедура может быть расположена во внешнем модуле. В этом случае вместо тела процедуры указывается место её расположения во внешнем модуле с помощью предложения EXTERNAL NAME. Аргументом этого предложения является строка, в которой через разделитель указано имя внешнего модуля, имя процедуры внутри модуля и определённая пользователем информация.

В предложении ENGINE указывается имя движка для обработки подключения внешних модулей. В РЕД Базе Данных для работы с внешними модулями используется движок UDR. После ключевого слова AS может быть указан строковый литерал - тело внешней процедуры, оно может быть использовано внешним модулем для различных целей.

Привилегии выполнения

Чтобы указать в контексте какого пользователя будет выполняться процедура используется необязательное предложение SQL SECURITY. Если выбрана опция INVOKER, то хранимая процедура выполняется с привилегиями вызывающего пользователя. Если выбрана опция DEFINER, то хранимая процедура выполняется с привилегиями определяющего пользователя (владельца процедуры). Эти привилегии будут дополнены привилегиями выданные самой хранимой процедуре с помощью оператора GRANT. По умолчанию хранимая процедура выполняется с привилегиями вызывающего пользователя

Значение по умолчанию на уровне всей базы данных можно изменить оператором ALTER DATABASE SET DEFAULT SQL SECURITY.

Использование BLR вместо SQL кода

В РЕД Базе Данных 5.0 появилась возможность создать процедуру с телом в виде двоичного BLR представления, закодированного в Base64, вместо обычного исходного кода на языке SQL. В этом случае исходные SQL коды процедуры будут недоступны. Оператор создания такой процедуры будет выглядеть так:

CREATE PROCEDURE <имя> (<входные параметры>)
RETURNS (<выходные параметры>)
[SQL SECURITY {DEFINER | INVOKER}]
AS '<BLR код>';

Здесь <BLR код> - это BLR в его исходном двоичном виде, закодированный в Base64. Его можно получить, например, из запроса:

select BASE64_ENCODE(RDB$PROCEDURE_BLR)
from RDB$PROCEDURES
where RDB$PROCEDURE_NAME = '<имя>';

19.2. Изменение хранимой процедуры

Для изменения существующей хранимой процедуры используется оператор ALTER PROCEDURE. Синтаксис оператора представлен в листинге .

Листинг 19.2 Синтаксис оператора изменения хранимой процедуры ALTER PROCEDURE

ALTER PROCEDURE <имя хранимой процедуры>
[AUTHID {OWNER | CALLER}]
   [(<входной пар-р>[,<входной пар-р>...])]
[RETURNS (<выходной параметр> [, <выходной параметр> ...])]
[SQL SECURITY {DEFINER | INVOKER}]
{ EXTERNAL NAME '<внешний модуль>' ENGINE <имя движка> [AS <тело внешней проц.>]} |
{
   {AS '<BLR код>'}
   |
   {AS
      [<объявление> [<объявление> ...] ]
   BEGIN
      <блок операторов>
   END }
}

Оператор позволяет изменять:

состав и характеристики входных параметров;
состав и характеристики выходных параметров;
в контексте какого пользователя будет выполняться процедура;
список локальных переменных и именованных курсоров;
тело хранимой процедуры на языке SQL;
исходный SQL код процедуры на двоичный BLR код;
для внешних процедур (UDR) — точку входа и имя движка.

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

Выполняемые изменения в хранимой процедуре не оказывают никакого влияния на ее зависимости и привилегии.

Примечание

Изменять хранимую процедуру может ее создатель и администратор (пользователь с ролью RDB$ADMIN) и пользователь с привилегией ALTER ANY PROCEDURE.

Предупреждение

Будьте осторожны при изменении количества и типов входных и выходных параметров хранимых процедур. Существующий код приложения может стать неработоспособным из-за того, что формат вызова процедуры несовместим с новым описанием параметров. Кроме того, PSQL модули, использующие изменённую хранимую процедуру, могут стать некорректными. Информация о том, как это обнаружить, находится в разделе 31.46.

Изменить значение SQL SECURITY можно без указания тела процедуры:

ALTER PROCEDURE <имя хранимой процедуры>
  SQL SECURITY {DEFINER | INVOKER}
| DROP SQL SECURITY

19.3. Создание новой или изменение существующей хранимой процедуры

Оператор CREATE OR ALTER PROCEDURE создаёт новую или изменяет существующую хранимую процедуру. Если хранимая процедура не существует, то она будет создана с использованием предложения CREATE PROCEDURE. Если она уже существует, то она будет изменена и откомпилирована, при этом существующие привилегии и зависимости сохраняются. Синтаксис оператора представлен в листинге .

Листинг 19.3 Синтаксис оператора создания новой или изменения существующей хранимой процедуры CREATE OR ALTER PROCEDURE

CREATE OR ALTER PROCEDURE <имя хранимой процедуры>
[AUTHID {OWNER | CALLER}]
   [(<входной параметр> [, <входной параметр> ...])]
[RETURNS (<выходной параметр> [, <выходной параметр> ...])]
[SQL SECURITY {DEFINER | INVOKER}]
{ EXTERNAL NAME '<внешний модуль>' ENGINE <имя движка> [AS <тело внешней проц.>]} |
{
   {AS '<BLR код>'}
   |
   {AS
      [<объявление> [<объявление> ...] ]
   BEGIN
      <блок операторов>
   END }
}

Семантика операторов и предложений в этом операторе полностью соответствует оператору CREATE PROCEDURE.

19.4. Удаление хранимой процедуры

Для удаления существующей хранимой процедуры используется оператор DROP PROCEDURE. Синтаксис оператора представлен в листинге .

Листинг 19.4 Синтаксис оператора удаления хранимой процедуры DROP PROCEDURE

DROP PROCEDURE <имя хранимой процедуры>

Если от хранимой процедуры существуют зависимости, то при попытке удаления такой процедуру будет выдана соответствующая ошибка. Также нельзя удалить хранимую процедуру, которая выполняется в настоящий момент.

Примечание

Удалить хранимую процедуру может ее создатель и администратор (пользователь с ролью RDB$ADMIN) и пользователь с привилегией DROP ANY PROCEDURE.

19.5. Создание новой или пересоздание существующей хранимой процедуры

Оператор RECREATE PROCEDURE создает новую или пересоздает существующую хранимую процедуру. Если процедура с таким именем уже существует, то оператор попытается удалить ее и создать новую процедуру, при этом привилегии на выполнение хранимой процедуры и привилегии самой хранимой процедуры не сохраняются.

Листинг 19.5 Синтаксис оператора пересоздания хранимой процедуры RECREATE PROCEDURE

RECREATE PROCEDURE <имя хранимой процедуры>
[AUTHID {OWNER | CALLER}]
   [(<входной параметр> [, <входной параметр> ...])]
[RETURNS (<выходной параметр> [, <выходной параметр> ...])]
[SQL SECURITY {DEFINER | INVOKER}]
{ EXTERNAL NAME '<внешний модуль>' ENGINE <имя движка> [AS <тело внешней проц.>]} |
{
   {AS '<BLR код>'}
   |
   {AS
      [<объявление> [<объявление> ...] ]
   BEGIN
      <блок операторов>
   END }
}

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

19.6. Примеры хранимых процедур

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

Выполняемые хранимые процедуры могут получать входные параметры и возвращать вызвавшей программе выходные параметры. Обращение к выполняемой хранимой процедуре осуществляется при помощи оператора EXECUTE PROCEDURE.

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

Далее в этой главе рассматриваются простые примеры хранимых процедур.

Получение значения искусственного первичного ключа

Для получения значения искусственного первичного ключа из генератора клиентской программой для таблицы PEOPLE можно использовать выполняемую хранимую процедуру:

SET TERM ^;
CREATE PROCEDURE PROC_PEOPLE
RETURNS (COD INTEGER)
AS
BEGIN
   COD = NEXT VALUE FOR GEN_PEOPLE;
   SUSPEND;
END ^

Сравните этот текст с примером, где описано создание триггера, выполняющего эти же функции. Использование триггера для получения значения искусственного ключа имеет то единственное преимущество, что триггер вызывается автоматически перед помещением новой строки в таблицу. От разработчика клиентской программы не требуется никаких специальных действий для этого. Однако в случае использования триггера клиентская программа не знает полученного числового значения. В некоторых ситуациях это может затруднить дальнейшую работу программы.

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

Вычисление факториала числа

В главе 12 был приведен пример оператора EXECUTE BLOCK, который позволял вычислить факториал заданного числа в декларативной части SQL.

В следующем примере приведены операторы создания хранимой процедуры, выполняющей вычисление факториала целого числа. В процедуре также присутствует оператор WHEN-DO, который обрабатывает ошибочную ситуацию — арифметическое переполнение, когда результат превышает значение, которое может быть помещено в переменную с типом данных BIGINT.

CREATE OR ALTER PROCEDURE PROC_FACTORIAL (N BIGINT)
RETURNS (RESULT BIGINT, TEXT VARCHAR(50))
AS
   DECLARE VARIABLE I BIGINT;
BEGIN
   RESULT = 1; I = 1;
   TEXT = 'That''s OK';
   WHILE (I <= N) DO
   BEGIN
      RESULT = RESULT * I; I = I + 1;
      WHEN ANY DO
      BEGIN
         TEXT = 'Overflow';
         RESULT = NULL;
         LEAVE;
      END
   END
   SUSPEND;
END ^

Здесь в цикле WHILE-DO осуществляются необходимые вычисления. Если не произошло арифметического переполнения, процедура возвращает в первом выходном параметре полученное число, а во втором символьном параметре текст "That's OK". В случае переполнения ошибочную ситуацию перехватывает оператор WHEN-DO. В нем формируется пустое значение результата, в символьный выходной параметр помещается текст "Overflow" и осуществляется выход из цикла при помощи оператора LEAVE.

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

SELECT * FROM PROC_FACTORIAL (20);

Результатом будет число 2432902008176640000 и текст "That's OK".

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

В следующем примере создается рекурсивная хранимая процедура, вычисляющая факториал.

CREATE PROCEDURE RECFACTORIAL (NUM INTEGER)
RETURNS (N_FACTORIAL DOUBLE PRECISION)
AS
   DECLARE VARIABLE NUM_LESS_ONE INT;
BEGIN
   IF (NUM = 1) THEN BEGIN        /**** Простейший случай: 1! = 1 ****/
      N_FACTORIAL = 1;
      SUSPEND;
   END
   ELSE BEGIN                     /**** Рекурсия: NUM! = (NUM * (NUM-1))! ****/
      NUM_LESS_ONE = NUM - 1;
      EXECUTE PROCEDURE RECFACTORIAL (NUM_LESS_ONE)
      RETURNING_VALUES N_FACTORIAL;
      N_FACTORIAL = N_FACTORIAL * NUM;
      SUSPEND;
   END
END ^

Количество возможных рекурсий устанавливается не более 1000 во избежание зацикливания. В реальности, в зависимости от доступных ресурсов серверной машины, это число может быть меньшим.

Примечание

В последнем примере в качестве типа данных для выходного параметра был выбран тип данных DOUBLE PRECISION. Это является более разумным решением в том случае, если получаемое значение превышает максимально допустимое для типа данных BIGINT. В первом примере вычисления факториала выбран другой тип данных только для того, чтобы проиллюстрировать средства обработки ошибок в PSQL. Это касается и многих других примеров данной главы.

В главе 18 был приведен пример использования операторов PSQL для получения чисел Фибоначчи. В следующем примере задается полный текст реальной хранимой процедуры, вычисляющей последнее значение числа Фибоначчи в заданном ряду. Первые два элемента в ряду 1 и 2. Каждый последующий элемент является суммой двух предшествующих.

CREATE PROCEDURE FIBONACCI_LAST (LAST_NUM INT)
RETURNS (RESULT BIGINT)
AS
   DECLARE VARIABLE I INTEGER;             -- Параметр цикла
   DECLARE VARIABLE PREV_ITEM BIGINT;      -- Предыдущий элемент
   DECLARE VARIABLE NEXT_ITEM BIGINT;      -- Следующий элемент
   DECLARE VARIABLE INTERMEDIATE BIGINT;   -- Временный элемент
BEGIN
   PREV_ITEM = 1;
   NEXT_ITEM = 2;
   I = 2;
   INTERMEDIATE = NEXT_ITEM;
   WHILE (I <= LAST_NUM) DO BEGIN
      INTERMEDIATE = NEXT_ITEM;
      NEXT_ITEM = PREV_ITEM + NEXT_ITEM;
      PREV_ITEM = INTERMEDIATE;
      I = I + 1;
   END
   RESULT = NEXT_ITEM;
   SUSPEND;
END ^

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

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

CREATE PROCEDURE FIBONACCI_SEQ (LAST_NUM INT)
RETURNS (RESULT BIGINT, QUOTIENT DOUBLE PRECISION)
AS
   DECLARE VARIABLE I INTEGER;              -- Параметр цикла
   DECLARE VARIABLE PREV_ITEM BIGINT;       -- Предыдущий элемент
   DECLARE VARIABLE INTERMEDIATE BIGINT;    -- Временный элемент
BEGIN
   RESULT = 1;
   QUOTIENT = 1;
   SUSPEND;
   PREV_ITEM = 1;
   RESULT = 2;
   QUOTIENT = 2;
   SUSPEND;
   I = 2;
   WHILE (I <= LAST_NUM) DO BEGIN
      INTERMEDIATE = RESULT;
      RESULT = PREV_ITEM + RESULT;
      PREV_ITEM = INTERMEDIATE;
      QUOTIENT = RESULT / CAST(PREV_ITEM AS DOUBLE PRECISION);
      I = I + 1;
      SUSPEND;
   END
END ^

Обратите внимание, что при выполнении деления для получения частного (выходной параметр QUOTIENT) один из целочисленных операндов явно при помощи функции CAST преобразуется к типу данных DOUBLE PRECISION. Это делается для того, чтобы в результате деления не были потеряны дробные знаки. Подробнее об арифметических операциях и о преобразовании данных см. в главе 4.

Каждый раз, когда встречается оператор SUSPEND, вызвавшей программе передаются очередные значения выходных параметров.

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

SELECT RESULT, CAST (QUOTIENT AS DECIMAL(18, 16))
FROM FIBONACCI_SEQ(10);

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

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

CREATE PROCEDURE PROC_SELECT_REGION2 (CODCOUNTRY CHAR(3))
RETURNS (CODREGION CHAR(4), NAMEREG CHAR(30), CENTER CHAR(15))
AS
BEGIN
   FOR SELECT CODREGION,
              NAMEREG,
              CENTER
   FROM REGION
   WHERE CODCOUNTRY = :CODCOUNTRY
   INTO :CODREGION, :NAMEREG, :CENTER
   DO SUSPEND;
END ^

Выборка данных осуществляется в операторе FOR SELECT-DO. Условие выборки задается в предложении WHERE, где требуется равенство кода страны значению, полученному из входного параметра процедуры. Значения столбцов очередной записи помещаются в выходные параметры. Оператор SUSPEND временно приостанавливает выполнение процедуры и передает значения выходных параметров вызвавшей программе.

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

SELECT
   CODREGION AS "Код региона",
   NAMEREG AS "Название региона",
   CENTER AS "Центр региона"
FROM PROC_SELECT_REGION2 ('USA');

Здесь будут выбраны все штаты США.

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

SELECT
   CODREGION AS "Код региона",
   NAMEREG AS "Название региона",
   CENTER AS "Центр региона"
FROM PROC_SELECT_REGION2 ('USA')
ORDER BY CODREGION;

В предложении ORDER BY можно задать не только имя столбца, но и его псевдоним. Предыдущий запрос можно записать в следующем виде:

SELECT
   CODREGION AS "Код региона",
   NAMEREG AS "Название региона",
   CENTER AS "Центр региона"
FROM PROC_SELECT_REGION2 ('USA')
ORDER BY "Код региона";

Значение входного параметра (код страны) можно получить и при помощи более сложных конструкций, а не только указав строковый литерал. Следующий оператор позволяет выбрать регионы России:

SELECT
   CODREGION AS "Код региона",
   NAMEREG AS "Название региона",
   CENTER AS "Центр региона"
FROM PROC_SELECT_REGION2 ((SELECT CODCOUNTRY
                          FROM COUNTRY
                          WHERE NAME = 'Россия'))
ORDER BY "Код региона";

Здесь код страны получается при помощи оператора SELECT, обращающегося к таблице стран.

Следующий оператор SELECT позволяет при обращении к хранимой процедуре задать дополнительные условия выборки. Окончательное условие для выборки строк таблицы будет сформировано конъюнкцией (логической операцией И) условия в хранимой процедуре и условия в операторе SELECT.

SELECT
   CODREGION AS "Код региона",
   NAMEREG AS "Название региона",
   CENTER AS "Центр региона"
FROM PROC_SELECT_REGION2 ((SELECT CODCOUNTRY
                          FROM COUNTRY
                          WHERE NAME = 'Россия'))
WHERE NAMEREG CONTAINING 'a'
ORDER BY "Название региона";

В этом операторе выбираются только те регионы России, в названии которых присутствует буква «а». Результирующие строки упорядочиваются по названию региона.

19.7. Объявление подпроцедуры

В хранимых функциях и хранимых процедурах можно объявлять подпроцедуры. Синтаксис оператора представлен в листинге .

Листинг 19.6 Синтаксис оператора объявления подпроцедуры DECLARE PROCEDURE

DECLARE PROCEDURE <имя подпроцедуры>
   [(<входной параметр> [, <входной параметр> ...])]
[RETURNS (<выходной параметр> [, <выходной параметр> ...])]
AS
   [<объявление лок.переменных/курсоров>[<объявление лок.переменных/курсоров>...] ]
BEGIN
   <блок операторов>
END

Подпроцедура не может быть вложена в другую подпрограмму. Они поддерживаются только в основном модуле (хранимой процедуре, хранимой функции и анонимном PSQL блоке).

В настоящее время подпроцедура не имеет прямого доступа для использования переменных, курсоров и других подпрограмм из основного модуля. Это может быть разрешено в будущем.

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

19. Хранимые процедуры (PROCEDURE)

19.1. Создание хранимой процедуры

Привилегии выполнения

Параметры

Входные параметры

Выходные параметры

Использование доменов при объявлении параметров

Использование доменов при объявлении параметров

Использование типа столбца при объявлении параметров

Тело хранимой процедуры

Внешние хранимые процедуры

Привилегии выполнения

Использование BLR вместо SQL кода

19.2. Изменение хранимой процедуры

19.3. Создание новой или изменение существующей хранимой процедуры

19.4. Удаление хранимой процедуры

19.5. Создание новой или пересоздание существующей хранимой процедуры

19.6. Примеры хранимых процедур

Получение значения искусственного первичного ключа

Вычисление факториала числа

19.7. Объявление подпроцедуры

19. Хранимые процедуры (`PROCEDURE`)