Руководство По Языку SQL СУБД Firebird 5.0 | Операторы Процедурного SQL (PSQL)

Операторы обработки данных (DML)Встроенные скалярные функции

Procedural SQL (PSQL) — процедурное расширение языка SQL.Это подмножество языка используется для написания хранимых процедур, хранимых функций, пакетов, триггеров и PSQL блоков.

Это расширение содержит все основные конструкции классических языков программирования.Кроме того, в него входят немного модифицированные DML операторы (SELECT, INSERT, UPDATE, DELETE и др.).

Элементы PSQL

Процедурное расширение может содержать объявления локальных переменных и курсоров, операторы присваивания, условные операторы, операторы циклов, выброса пользовательского исключений, средства для обработки ошибок, отправки сообщений (событий) клиентским программам.Кроме того, в триггерах доступны специфичные контекстные переменные, такие как NEW и OLD.

В PSQL не допустимы операторы модификации метаданных (DDL операторы).

DML операторы с параметрами

В DML (SELECT, INSERT, UPDATE, DELETE и др.) операторах допустимы только именованные параметры.Если DML операторы содержат именованные параметры, то они должны быть предварительно объявлены как локальные переменные в операторе DECLARE [VARIABLE] заголовка модуля или доступны во входных или выходных параметрах PSQL модуля.

При использовании именованных параметров в DML операторах необходим префикс двоеточия “:”, однако в предложении INTO символ двоеточия не обязателен.Префикс двоеточия является необязательным в операторах специфичных для PSQL, таких, как операторы ветвления или присваивания.Префикс двоеточия не требуется также при вызове хранимой процедуры с помощью оператора EXECUTE PROCEDURE из другого PSQL модуля.

Транзакции

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

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

Структура модуля

В синтаксисе PSQL модулей можно выделить заголовок и тело.DDL операторы для их объявления являются сложными операторами, т.е.состоят из единственного оператора, который включает в себя блоки нескольких операторов.Такие операторы начинаются с глагола (CREATE, ALTER, DROP, RECREATE, CREATE OR ALTER) и завершаются последним оператором END тела модуля.

Заголовок модуля

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

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

Привилегии выполнения PSQL кода

PSQL код может выполняться в одном из следующих режимов:

С привилегиями вызывающего пользователя (привилегии CURRENT_USER);
С привилегиями определяющего пользователя (владельца объекта метаданных).

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

Анонимные PSQL блоки (EXECUTE BLOCK) всегда выполняются с правами вызывающего пользователя.

Тело модуля

Тело модуля может быть написано на языке PSQL или быть телом внешнего модуля.

Синтаксис тела модуля

<routine-body> ::=
    <psql-routine-spec>
  | <external-routine-spec>

<psql-routine-spec> ::=
  [<rights-clause>] <psql-routine-body>


<rights-clause> ::=
  SQL SECURITY {DEFINER | INVOKER}


<psql-routine-body> ::=
  AS
    [<declarations>]
  BEGIN
    [<PSQL_statements>]
  END

<declarations> ::=
  <declare-item> [<declare-item> ...]

<declare-item> ::=
    <declare-var>;
  | <declare-cursor>;
  | <subroutine-declaration>;
  | <subroutine-implementation>

<subroutine-declaration> ::= <subfunc-decl> | <subproc-decl>

<subroutine-implementation> ::= <subfunc-impl> | <subproc-impl>

<external-routine-spec> ::=
  <external-routine-reference>
  [AS <extbody>]

<external-routine-reference> ::= EXTERNAL NAME <extname> ENGINE <engine>

<extname> ::=
  '<module-name>!<routine-name>[!<misc-info>]'

Table 1. Параметры тела модуля
Параметр	Описание
declare-var	Объявление локальной переменной.
declare-cursor	Объявление именованного курсора.
subfunc-decl	Объявление подпрограммы – функции.
subproc-decl	Объявление подпрограммы – процедуры.
subfunc-impl	Реализация подпрограммы – функции.
subproc-impl	Реализация подпрограммы – процедуры.
extbody	Тело внешней процедуры.Строковый литерал который может использоваться UDR для различных целей.
module-name	Имя внешнего модуля, в котором расположена функция.
routine-name	Внутреннее имя функции внутри внешнего модуля.
misc-info	Определяемая пользователем информация для передачи в функцию внешнего модуля.
engine	Имя движка для использования внешних функций.Обычно указывается имя UDR.

Тело PSQL модуля

Тело PSQL начинается с необязательного раздела, в котором объявляются переменные, курсоры и подпрограммы.Далее следует блок операторов, которые выполняются в логической последовательности как программа.Блок операторов — или составной оператор — заключен в ключевые слова BEGIN и END и выполняется как единый блок кода.Основной блок BEGIN … END может содержать любое количество других блоков BEGIN … END, как встроенных, так и последовательных.Максимальная вложенность блоков составляет 512 уровней.Все операторы, кроме BEGIN и END, заканчиваются точкой с запятой (“;”).Никакой другой символ не может использоваться в качестве терминатора для операторов PSQL.

Изменение терминатора в isql

Здесь мы немного отвлечёмся для того, чтобы объяснить как переключить терминатор в утилите isql.Это необходимо, чтобы иметь возможность определять в ней PSQL модули, не конфликтуя с самим isql, который использует тот же самый символ, точку с запятой (;), как разделитель операторов.

isql команда `SET TERM`

Назначение

Изменение символа(ов) терминатора, чтобы избежать конфликта с терминатором в PSQL операторах.

Доступно в

ISQL.

Синтаксис

SET TERM new_terminator old_terminator

Table 1. Параметры оператора SET TERM
Параметр	Описание
new_terminator	Новый терминатор.
old_terminator	Старый терминатор.

При написании триггеров и хранимых процедур в текстах скриптов, создающих требуемые программные объекты базы данных, во избежание двусмысленности относительно использования символа завершения операторов (по нормам SQL это точка с запятой) применяется оператор SET TERM, который, строго говоря, не является оператором SQL, а является командой интерактивного инструмента isql.При помощи этого оператора перед началом создания триггера или хранимой процедуры задаётся символ или строка символов, являющийся завершающим в конце текста триггера или хранимой процедуры.После описания текста соответствующего программного объекта при помощи того же оператора SET TERM значение терминатора возвращается к обычному варианту — точка с запятой.

Альтернативный терминатор может быть любой произвольной строкой символов за исключением точки с запятой, пробела и апострофа.Если вы используете буквенный символ, то он будет чувствителен к регистру.

Example 1. Задание альтернативного терминатора

SET TERM ^;

CREATE OR ALTER PROCEDURE SHIP_ORDER (
    PO_NUM CHAR(8))
AS
BEGIN
  /* Тело хранимой процедуры */
END^

/* Другие хранимые процедуры и триггеры */

SET TERM ;^

/* Другие операторы DDL */

Тело внешнего модуля

Тело внешнего модуля определяет механизм UDR, используемый для выполнения внешнего модуля, и дополнительно указываетимя вызываемой процедуры UDR (<extname>) и/или строку (<extbody>) с семантикой, специфичной для UDR.

Конфигурация внешних модулей и механизмов UDR не рассматривается далее в этом справочнике по языку.За подробностями обращайтесь к документации по конкретному движку UDR.

Хранимые процедуры

Хранимая процедура является программой, хранящейся в области метаданных базы данных и выполняющейся на стороне сервера.К хранимой процедуре могут обращаться хранимые процедуры (в том числе и сама к себе), триггеры и клиентские программы.Если хранимая процедура вызывает саму себя, то такая хранимая процедура называется рекурсивной.

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

Хранимые процедуры имеют следующие преимущества:

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

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

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

Выполняемые хранимые процедуры

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

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

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

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

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

Обращение к селективной хранимой процедуре осуществляется при помощи оператора SELECT(см. Выборка из селективной хранимой процедуры).См. пример создания селективной хранимой процедурыв конце раздела CREATE PROCEDURE главы “Операторы определения данных DDL”.

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

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

Для получения информации о создание хранимых процедур см. CREATE PROCEDURE в главе “Операторы определения данных DDL”.

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

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

Для получения информации об изменении существующих хранимых процедур см. ALTER PROCEDURE,CREATE OR ALTER PROCEDURE,RECREATE PROCEDURE в главе “Операторы определения данных DDL”.

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

Для получения информации об удалении хранимых процедур см. DROP PROCEDURE в главе “Операторы определения данных DDL”.

Хранимые функции

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

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

Создание хранимой функции

Для получения информации о создание хранимых функций см. CREATE FUNCTION в главе “Операторы определения данных DDL”.

Изменение хранимой функции

Для получения информации об изменении существующих хранимых функций см.ALTER FUNCTION,CREATE OR ALTER FUNCTION,RECREATE FUNCTION в главе “Операторы определения данных DDL”.

Удаление хранимой функции

Для получения информации об удалении хранимых функций см. DROP FUNCTION в главе “Операторы определения данных DDL”.

PSQL блоки

Для выполнения из декларативного SQL (DSQL) некоторых императивных действий используются анонимные (безымянные) PSQL блоки.Заголовок анонимного PSQL блока опциально может содержать входные и выходные параметры.Тело анонимного PSQL блока может содержать объявление локальных переменных, курсоров, подпрограмм и блок PSQL операторов.

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

Как и хранимые процедуры анонимные PSQL блоки могут использоваться для обработки данных или для осуществления выборки из базы данных.

Синтаксис (полный):

EXECUTE BLOCK
  [(<inparam> = ? [, <inparam> = ? ...])]
  [RETURNS (<outparam> [, <outparam> ...])]
  <psql-routine-body>

<psql-routine-body> ::=
  См. Синтаксис тела модуля

Table 1. Параметры оператора EXECUTE BLOCK
Параметр	Описание
inparam	Описание входного параметра.
outparam	Описание выходного параметра.

См. также:

EXECUTE BLOCK.

Пакеты

Пакет — группа процедур и функций, которая представляет собой единый объект базы данных.

Пакеты Firebird состоят из двух частей: заголовка (ключевое слово PACKAGE) и тела (ключевые слова PACKAGE BODY). Такое разделение очень сильно напоминает модули Delphi, заголовок соответствует интерфейсной части, а тело — части реализации.

Преимущества пакетов

Пакеты обладают следующими преимуществами:

Модульность: Блоки взаимозависимого кода выделены в логические модули, как это сделано в других языках программирования.

В программировании существует множество способов для группировки кода, например с помощью пространств имен (namespaces), модулей (units) и классов.Со стандартными процедурами и функциями базы данных это не возможно.
Упрощение отслеживания зависимостей: Пакеты упрощают механизм отслеживания зависимостей между набором связанных процедур, а также между этим набором и другими процедурами, как упакованными, так и неупакованными.

Каждый раз, когда упакованная подпрограмма определяет, что используется некоторый объект базы данных, информации о зависимости от этого объекта регистрируется в системных таблицах Firebird.После этого, для того чтобы удалить или изменить этот объект, вы сначала должны удалить, то что зависит от него.Поскольку зависимости от других объектов существуют только для тела пакета, это тело пакета может быть легко удалено, даже если какой-нибудь другой объект зависит от этого пакета.Когда тело удаляется, заголовок остаётся, что позволяет пересоздать это тело после того, как сделаны изменения связанные с удалённым объектом.
Упрощение управления разрешениями: Поскольку Firebird выполняет подпрограммы с полномочиями вызывающей стороны, то каждой вызывающей подпрограмме необходимо предоставить полномочия на использования ресурсов, если эти ресурсы не являются непосредственно доступными вызывающей стороне.Использование каждой подпрограммы требует предоставления привилегий на её выполнение для пользователей и/или ролей.

У упакованных подпрограмм нет отдельных привилегий.Привилегии действуют на пакет в целом.Привилегии, предоставленные пакетам, действительны для всех подпрограмм тела пакета, в том числе частных, и сохраняются для заголовка пакета.
Частные области видимости: Некоторые процедуры и функции могут быть частными (private), а именно их использование разрешено только внутри определения пакета.

Все языки программирования имеют понятие области видимости подпрограмм, которое невозможно без какой-либо формы группировки.Пакеты Firebird в этом отношении подобны модулям Delphi.Если подпрограмма не объявлена в заголовке пакета (interface), но реализована в теле (implementation), то такая подпрограмма становится частной (private). Частную подпрограмму возможно вызвать только из её пакета.

Создание пакета

Для получения информации о создании пакетов см.CREATE PACKAGE, CREATE PACKAGE BODY.

Модификация пакета

Для получения информации об изменении существующего заголовка или тела пакетасм. ALTER PACKAGE,CREATE OR ALTER PACKAGE,RECREATE PACKAGE,RECREATE PACKAGE BODY.

Удаление пакета

Для получения информации об удалении пакета см. DROP PACKAGE, DROP PACKAGE BODY.

Триггеры

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

Триггер, вызываемый при наступлении события таблицы, связан с одной таблицей или представлением, с одним или более событиями для этой таблицы или представления (INSERT, UPDATE, DELETE) и ровно с одной фазой такого события (BEFORE или AFTER).

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

Порядок срабатывания

Для каждой комбинации фаза-событие может быть определено более одного триггера.Порядок, в котором они выполняются, может быть указан явно с помощью дополнительного аргумента POSITION в определении триггера.Максимальная позиция равна 32767.Триггеры с меньшей позицией вызываются первыми.

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

DML триггеры

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

Варианты триггеров

Существует шесть основных вариантов соотношения событие-фаза для таблицы (представления):

до добавления новой строки

(BEFORE INSERT)

после добавления новой строки

(AFTER INSERT)

до изменения строки

(BEFORE UPDATE)

после изменения строки

(AFTER UPDATE)

до удаления строки

(BEFORE DELETE)

после удаления строки

(AFTER DELETE)

Помимо базовых форм с единственной фазой и событием Firebird поддерживает также формы с одной фазой и множеством событий, например BEFORE INSERT OR UPDATE OR DELETE или AFTER UPDATE OR DELETE или любая другая комбинация на ваш выбор.

Note	Триггеры с несколькими фазами, такие как `BEFORE OR AFTER …` не поддерживаются.

Контекстные переменные INSERTING, UPDATING и DELETING логического типа могут быть использованы в теле триггера для определения события, которое вызвало срабатывание триггера.

Контекстные переменные NEW и OLD

В DML триггерах Firebird обеспечивает доступ к множеству контекстных переменных NEW и OLD.Каждое множество является массивом всей строки: OLD.* — значение строки до изменения данных и NEW.* — требуемое ("новое") значение строки.Операторы могут ссылаться на них использую следующие формы NEW.columname и OLD.columnname.columnname может быть любым столбцом определённым в таблице(представлении), а не только тем что был изменён.

Контекстные переменные NEW и OLD подчиняются следующим правилам:

Во всех триггерах контекстные переменные OLD доступны только для чтения;
В триггерах BEFORE UPDATE и BEFORE INSERT переменные NEW доступны для чтения и записи, за исключением COMPUTED BY столбцов;
В INSERT триггерах ссылка на переменные OLD не допускается и вызовет исключение;
В DELETE триггерах ссылка на переменные NEW не допускается и вызовет исключение;
Во всех AFTER триггерах переменные NEW доступны только для чтения.

Триггеры на события базы данных

Триггер, связанный с событиями базы данных, может вызываться при следующих событиях:

После соединения с базой данных, или сбросасессионного окружения

ON CONNECT

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

До отсоединения от базы данных или сбросомсессионного окружения

ON DISCONNECT

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

После старта транзакции

ON TRANSACTION START

Триггер выполняется в контексте текущейтранзакции

Перед подтверждением транзакции

ON TRANSACTION COMMIT

Триггер выполняется в контексте текущейтранзакции

Перед отменой транзакции

ON TRANSACTION ROLLBACK

Триггер выполняется в контексте текущейтранзакции

Контекстная переменная RESETTING может использоваться в триггерах на события ON CONNECT и ON DISCONNECT для того, чтобы отличить сброс сеанса от подключения/отключения от базы данных.

DDL триггеры

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

Переменные доступные в пространстве имён DDL_TRIGGER

Во время работы DDL триггера доступно пространство имён DDL_TRIGGER для использования в функции RDB$GET_CONTEXT.Его использование также допустимо в хранимых процедурах и функциях, вызванных DDL триггерами.

Контекст DDL_TRIGGER работает как стек.Перед возбуждением DDL триггера, значения, относящиеся к выполняемой команде, помещаются в этот стек.После завершения работы триггера значения выталкиваются.Таким образом, в случае каскадных DDL операторов, когда каждая пользовательская DDL команда возбуждает DDL триггер, и этот триггер запускает другие DDL команды, с помощью EXECUTE STATEMENT, значения переменных в пространстве имен DDL_TRIGGER будут соответствовать команде, которая вызвала последний DDL триггер в стеке вызовов.

Переменные доступные в пространстве имён DDL_TRIGGER:* EVENT_TYPE — тип события (CREATE, ALTER, DROP)

OBJECT_TYPE — тип объекта (TABLE, VIEW и д.р.)
DDL_EVENT — имя события (<ddl event item>),

где <ddl event item> = EVENT_TYPE || ' ' || OBJECT_TYPE
OBJECT_NAME — имя объекта метаданных
SQL_TEXT — текст SQL запроса

Создание триггера

Для получения информации о создании триггеров см.CREATE TRIGGER,CREATE OR ALTER TRIGGER,RECREATE TRIGGER в главе “Операторы определения данных DDL”.

Изменение триггера

Для получения информации об изменении триггеров см. ALTER TRIGGER,CREATE OR ALTER TRIGGER,RECREATE TRIGGER в главе “Операторы определения данных DDL”.

Удаление триггера

Для получения информации об удалении триггеров см.DROP TRIGGER в главе “Операторы определения данных DDL”.

Написание кода тела модуля

В этом разделе подробно рассматривается процедурные конструкции языка SQL и операторы доступные в теле хранимых процедур, триггеров и анонимных PSQL блоков.

Маркер двоеточия (‘:’)

Маркер двоеточия (‘:’) используется в PSQL, чтобы пометить ссылку на переменную в DML операторе.В остальных случаях маркер двоеточия необязателен перед именами переменных.

Никогда не задавайте префикс двоеточия для контекстных переменных.

Оператор присваивания

Назначение

Присваивание переменной значения.

Доступно в

PSQL

Синтаксис

varname = <value_expr>;

Table 1. Параметры оператора присваивания
Параметр	Описание
varname	Имя локальной переменной или параметра процедуры (функции).
value_expr	Выражение, константа или переменная совместимая по типу данных с varname.

PSQL использует символ равенства (‘=’) в качестве своего оператора присваивания.Оператор присваивания устанавливает переменной слева от оператора значение SQL выражения справа.Выражением может быть любое правильное выражение SQL.Оно может содержать литералы, имена внутренних переменных, арифметические, логические и строковые операции, обращения к встроенным функциям и к функциям, определённым пользователем.

Example 1. Использование оператора присваивания

CREATE PROCEDURE MYPROC (
    a INTEGER,
    b INTEGER,
    name VARCHAR (30)
)
RETURNS (
    c INTEGER,
    str VARCHAR(100))
AS
BEGIN
  -- присваиваем константу
  c = 0;
  str = '';
  SUSPEND;
  -- присваиваем значения выражений
  c = a + b;
  str = name || CAST(b AS VARCHAR(10));
  SUSPEND;
  -- присваиваем значение выражения
  -- построенного с использованием запроса
  c = (SELECT 1 FROM rdb$database);
  -- присваиваем значение из контекстной переменной
  str = CURRENT_USER;
  SUSPEND;
END

См. также:

DECLARE VARIABLE.

`LEAVE`

Назначение

Выход из цикла.

Доступно в

PSQL

Синтаксис

[label:]
<loop_stmt>
BEGIN
  ...
  LEAVE [label];
  ...
END

<loop_stmt> ::=
    FOR <select_stmt> INTO <var_list>  DO
  | FOR EXECUTE STATEMENT ... INTO <var_list> DO
  | WHILE (<condition>) DO

Table 1. Параметры оператора LEAVE
Параметр	Описание
label	Метка.
select_stmt	Оператор SELECT.
condition	Логическое условие возвращающее TRUE, FALSE или UNKNOWN.

Оператор LEAVE немедленно прекращает работу внутреннего цикла операторов WHILE или FOR.С использованием необязательного параметра label, LEAVE также может выйти и из внешнего цикла, то есть цикла помеченного меткой `.Код продолжает выполняться с первого оператора после завершенного блока цикла.

Примеры `LEAVE`

Example 1. Использование оператора LEAVE

В этом примере выход из цикла произойдёт при возникновении ошибки вставки в таблицу NUMBERS.Код продолжит своё выполнение с оператора C = 0.

...
WHILE (B < 10) DO
BEGIN
    INSERT INTO NUMBERS(B)
    VALUES (:B);
    B = B + 1;
    WHEN ANY DO
    BEGIN
        EXECUTE PROCEDURE LOG_ERROR (
             CURRENT_TIMESTAMP,
             'ERROR IN B LOOP');
        LEAVE;
    END
END
C = 0;
...

Example 2. Использование оператора LEAVE с меткой

В этом примере оператор LEAVE LOOPA завершает внешний цикл, а LEAVE LOOPB — внутренний.

Обратите внимание: простого оператора LEAVE также было бы достаточно, чтобы завершить внутренний цикл.

...
STMT1 = 'SELECT NAME FROM FARMS';
LOOPA:
FOR EXECUTE STATEMENT :STMT1
INTO :FARM DO
BEGIN
  STMT2 = 'SELECT NAME ' || 'FROM ANIMALS WHERE FARM = ''';
  LOOPB:
  FOR EXECUTE STATEMENT :STMT2 || :FARM || ''''
  INTO :ANIMAL DO
  BEGIN
    IF (ANIMAL = 'FLUFFY') THEN
      LEAVE LOOPB;
    ELSE IF (ANIMAL = FARM) THEN
      LEAVE LOOPA;
    ELSE
      SUSPEND;
  END
END
...

См. также:

BREAK, EXIT, CONTINUE.

`CONTINUE`

Назначение

Досрочное начало новой итерации цикла.

Доступно в

PSQL

Синтаксис

[label:]
<loop_stmt>
BEGIN
  ...
  CONTINUE [label];
  ...
END

<loop_stmt> ::=
    FOR <select_stmt> INTO <var_list>  DO
  | FOR EXECUTE STATEMENT ... INTO <var_list> DO
  | WHILE (<condition>) DO

Table 1. Параметры оператора `CONTINUE`
Параметр	Описание
label	Метка.
select_stmt	Оператор SELECT.
condition	Логическое условие возвращающее TRUE, FALSE или UNKNOWN.

Оператор CONTINUE пропускает оставшуюся часть текущего блока цикла и запускает следующую итерацию текущего цикла WHILE или FOR.С использованием необязательного параметра label, CONTINUE также может начинать следующую итерацию для внешнего цикла, то есть цикла, помеченного меткой label.

Примеры `CONTINUE`

Example 1. Использование оператора CONTINUE

FOR
  SELECT A, D FROM ATABLE INTO :achar, :ddate
DO BEGIN
  IF (ddate < current_data - 30) THEN
    CONTINUE;
  ELSE
    /* do stuff */
  ...
END

См. также:

LEAVE, BREAK.

`EXIT`

Назначение

Завершение работы процедуры, функции или триггера.

Доступно в

PSQL

Синтаксис

EXIT;

Оператор EXIT, вызванный из любой точки выполняющегося PSQL модуля, переходит на последний оператор END, таким образом завершая выполнение программы.

Вызов EXIT в функции приведет к тому, что функция вернет NULL.

Примеры `EXIT`

Example 1. Использование оператора EXIT в селективной хранимой процедуре.

CREATE PROCEDURE GEN_100
RETURNS (
  I INTEGER
)
AS
BEGIN
  I = 1;
  WHILE (1=1) DO
  BEGIN
    SUSPEND;
    IF (I=100) THEN
      EXIT;
    I = I + 1;
  END
END

См. также:

LEAVE, BREAK, CONTINUE,SUSPEND.

`SUSPEND`

Назначение

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

Доступно в

PSQL

Синтаксис

SUSPEND;

Оператор SUSPEND передаёт значения выходных параметров в буфер и приостанавливает выполнение хранимой процедуры (PSQL блока). Выполнение остаётся приостановленным до тех пор, пока вызывающая сторона не получит содержимое буфера.Выполнение возобновляется с оператора, следующего непосредственно после оператора SUSPEND.Чаще всего это будет новой итерацией циклического процесса.

Note

Оператор SUSPEND может встречаться только в хранимых процедурах или подпроцедурах, а также в анонимных блоках EXECUTE BLOCK.
Наличие ключевого слова SUSPEND определяет хранимую процедуру как выбираемую (selectable) процедуру.
Приложения, использующие API интерфейсы, обычно делают выборку из хранимых процедур прозрачно.
Если выбираемая (selectable) процедура выполняется с использованием EXECUTE PROCEDURE, она ведет себя как исполняемая процедура. Когда в такой хранимой процедуре выполняется инструкция SUSPEND, это то же самое, что выполнение инструкции EXIT, что приводит к немедленному завершению процедуры.
Оператор SUSPEND “нарушает” атомарность блока, внутри которого он находится. В случае возникновения ошибки в селективной процедуре, операторы, выполненные после последнего оператора SUSPEND, будут откачены. Операторы, выполненные до последнего оператора SUSPEND, не будут откачены, если не будет выполнен откат транзакции.

Примеры `SUSPEND`

Example 1. Использование оператора SUSPEND в селективной хранимой процедуре.

CREATE PROCEDURE GEN_100
RETURNS (
  I INTEGER
)
AS
BEGIN
  I = 1;
  WHILE (1=1) DO
  BEGIN
    SUSPEND;
    IF (I=100) THEN
      EXIT;
    I = I + 1;
  END
END

См. также:

EXIT.

`EXECUTE STATEMENT`

Назначение

Выполнение динамически созданных SQL операторов.

Доступно в

PSQL

Синтаксис

<execute_statement> ::=
  EXECUTE STATEMENT <argument>
    [<option> ...]
    [INTO <variables>]

<argument> ::=
    <paramless_stmt>
  | (<paramless_stmt>)
  | (<stmt_with_params>) (<param_values>)

<param_values> ::= <named_values> | <positional_values>

<named_values> ::=
  [EXCESS] paramname := <value_expr>
  [, [EXCESS] paramname := <value_expr> ...]

<positional_values> ::= <value_expr> [, <value_expr> ...]

<option> ::=
    WITH {AUTONOMOUS | COMMON} TRANSACTION
  | WITH CALLER PRIVILEGES
  | AS USER user
  | PASSWORD password
  | ROLE role
  | ON EXTERNAL [DATA SOURCE] <connect_string>

<connection_string> ::=
  См. <filespec> в Синтаксис CREATE DATABASE !!

<variables> ::= [:]varname [, [:]varname ...]

Table 1. Параметры оператора EXECUTE STATEMENT
Параметр	Описание
paramless_stmt	Строковый литерал или переменная, содержащая не параметризованный SQL запрос.
stmt_with_params	Строковый литерал или переменная, содержащая параметризованный SQL запрос.
paramname	Имя параметра SQL запроса.
value_expr	Выражение для получения значения параметра запроса.
user	Имя пользователя.Может быть строкой, `CURRENT_USER` или переменной.
password	Пароль.Может быть строкой или переменной.
role	Роль.Может быть строкой, `CURRENT_ROLE` или переменной.
connection_string	Строка соединения с удалённой БДМожет быть строкой или переменной.
varname	Переменная.

Оператор EXECUTE STATEMENT принимает строковый параметр и выполняет его, как будто это оператор DSQL.Если оператор возвращает данные, то с помощью предложения INTO их можно передать в локальные переменные.

Параметризованные операторы

В DSQL операторе можно использовать параметры.Параметры могут быть именованными и позиционными (безымянные). Значение должно быть присвоено каждому параметру.

Особенности использования параметризованных операторов

Одновременное использование именованных и позиционных параметров в одном запросе запрещено;
Если у оператора есть параметры, они должны быть помещены в круглые скобки при вызове EXECUTE STATEMENT, независимо от вида их представления: непосредственно в виде строки, как имя переменной или как выражение;
Именованным параметрам должно предшествовать двоеточие (‘:’) в самом операторе, но не при присвоении значения параметру;
Передача значений безымянным параметрам должна происходить в том же порядке, в каком они встречаются в тексте запроса;
Присвоение значений параметров должно осуществляться при помощи специального оператора “:=”, аналогичного оператору присваивания языка Pascal;
Каждый именованный параметр может использоваться в операторе несколько раз, но только один раз при присвоении значения;
Для позиционных параметров число подставляемых значений должно точно равняться числу параметров (вопросительных знаков) в операторе;
Необязательное ключевое слово EXCESS обозначает, что данный именованный параметр необязательно должен упоминаться в тексте запроса. Обратите внимание, что все не EXCESS параметры должны присутствовать в запросе.

Примеры `EXECUTE STATEMENT` с параметрами

Example 1. Использование EXECUTE STATEMENT с именованными параметрами:

...
DECLARE license_num VARCHAR(15);
DECLARE connect_string VARCHAR (100);
DECLARE stmt VARCHAR (100) =
  'SELECT license
   FROM cars
   WHERE driver = :driver AND location = :loc';
BEGIN
  ...
  SELECT connstr
  FROM databases
  WHERE cust_id = :id
  INTO connect_string;
  ...
  FOR
    SELECT id
    FROM drivers
    INTO current_driver
   DO
   BEGIN
     FOR
       SELECT location
       FROM driver_locations
       WHERE driver_id = :current_driver
       INTO current_location
     DO
     BEGIN
       ...
       EXECUTE STATEMENT (stmt)
       (driver := current_driver,
        loc := current_location)
       ON EXTERNAL connect_string
       INTO license_num;
       ...

Example 2. Использование EXECUTE STATEMENT с позиционными параметрами:

DECLARE license_num VARCHAR (15);
DECLARE connect_string VARCHAR (100);
DECLARE stmt VARCHAR (100) =
  'SELECT license
   FROM cars
   WHERE driver = ? AND location = ?';
BEGIN
  ...
  SELECT connstr
  FROM databases
  WHERE cust_id = :id
  INTO connect_string;
  ...
  FOR SELECT id
      FROM drivers
      INTO current_driver
  DO
  BEGIN
    FOR
      SELECT location
      FROM driver_locations
      WHERE driver_id = :current_driver
      INTO current_location
    DO
    BEGIN
      ...
      EXECUTE STATEMENT (stmt)
      (current_driver, current_location)
      ON EXTERNAL connect_string
      INTO license_num;
      ...

Example 3. Использование EXECUTE STATEMENT с избыточными (EXCESS) параметрами:

CREATE PROCEDURE P_EXCESS (A_ID INT, A_TRAN INT = NULL, A_CONN INT = NULL)
  RETURNS (ID INT, TRAN INT, CONN INT)
AS
DECLARE S VARCHAR(255);
DECLARE W VARCHAR(255) = '';
BEGIN
  S = 'SELECT * FROM TTT WHERE ID = :ID';

  IF (A_TRAN IS NOT NULL)
  THEN W = W || ' AND TRAN = :a';

  IF (A_CONN IS NOT NULL)
  THEN W = W || ' AND CONN = :b';

  IF (W <> '')
  THEN S = S || W;

  -- could raise error if TRAN or CONN is null
  -- FOR EXECUTE STATEMENT (:S) (a := :A_TRAN, b := A_CONN, id := A_ID)

  -- OK in all cases
  FOR EXECUTE STATEMENT (:S) (EXCESS a := :A_TRAN, EXCESS b := A_CONN, id := A_ID)
      INTO :ID, :TRAN, :CONN
      DO SUSPEND;
END

`WITH {AUTONOMOUS | COMMON} TRANSACTION`

По умолчанию оператор выполняется в контексте текущей транзакции.При использовании предложения WITH AUTONOMOUS TRANSACTION запускается новая транзакция с такими же параметрами, как и текущая.Она будет подтверждена, если оператор выполнился без ошибок и отменена (откачена) в противном случае.С предложением WITH COMMON TRANSACTION по возможности используется текущая транзакция.

Если оператор должен работать в отдельном соединении, то используется уже запущенная в этом соединении транзакция (если таковая транзакция имеется). В противном случае стартует новая транзакция с параметрами текущей транзакции.Любые новые транзакции, запущенные в режиме “COMMON”, подтверждаются или откатываются вместе с текущей транзакцией.

`WITH CALLER PRIVILEGES`

По умолчанию операторы SQL выполняются с правами текущего пользователя.Спецификация WITH CALLER PRIVILEGES добавляет к ним привилегии для вызова хранимой процедуры или триггера, так же как если бы оператор выполнялся непосредственно подпрограммой.WITH CALLER PRIVILEGES не имеет никакого эффекта, если также присутствует предложение ON EXTERNAL.

`ON EXTERNAL [DATA SOURCE]`

С предложением ON EXTERNAL DATA SOURCE оператор выполняется в отдельном соединении с той же или другой базой данных, возможно даже на другом сервере.Если строка подключения имеет значение NULL или '' (пустая строка), предложение ON EXTERNAL считается отсутствующим и оператор выполняется для текущей базы данных.Строка подключения подробно описана в операторе CREATE DATABASEсм. Создание БД на удалённом сервере.

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

Пул внешних подключений (External connection pool)

Чтобы избежать задержек при частом использовании внешних соединений, подсистема внешних источников данных (EDS) использует пул внешних подключений.Пул сохраняет неиспользуемые внешние соединения в течении некоторого времени, что позволяет избежать затрат на подключение/отключение для часто используемых строк подключения.

Как работает пул соединений:

каждое внешнее соединение связывается с пулом при создании;
пул имеет два списка: неиспользуемых соединений и активных соединений;

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

Note	Если внешний источник данных не поддерживает оператор `ALTER SESSION RESET`, то это не считается ошибкой, и такое соединение будет помещено в пул.

если пул достиг максимального размера, то самое старое бездействующее соединение закрывается;
когда Firebird просит создать новое внешнее соединение, то пул сначала ищет кандидата в списке простаивающих соединений. Поиск основан на 4 параметрах:---
- строка подключения;
- имя пользователя;
- пароль;
- роль.
Поиск чувствителен к регистру;
если подходящее соединение найдено, то проверятся живое ли оно;
если соединение не прошло проверку, то оно удаляется и поиск повторяется (ошибка не возвращается пользователю);
найденное (и живое) соединение перемещается из списка простаивающих соединение в список активных соединений и возвращается вызывающему;
если имеется несколько подходящих соединений, то будет выбрано наиболее часто используемое;
если нет подходящего соединения, то создаётся новое и помещается в список активных соединений;
когда время жизни простаивающего соединения истекло, то оно удаляется из пула и закрывается.

Основные характеристики:

отсутствие “вечных” внешних соединений;
ограниченное количество неактивных (простаивающих) внешних соединений в пуле;
поддерживает быстрый поиск среди соединений (по 4 параметрам указанным выше);
пул является общим для всех внешних баз данных;
пул является общим для всех локальных соединений, обрабатываемых данным процессом Firebird.

Параметры пула внешних соединений:

время жизни соединения: временной интервал с момента последнего использования соединения, после истечения которого он будет принудительно закрыт. Параметр ExtConnPoolLifeTime в firebird.conf. По умолчанию равен 7200 секунд;
размер пула: максимально допустимое количество незанятых соединений в пуле. Параметр ExtConnPoolSize в firebird.conf. По умолчанию равен 0, т.е. пул внешних соединений отключен.

Пулом внешних соединений, а также его параметрами можно управлять с помощью специальных операторов.Подробнее см. ALTER EXTERNAL CONNECTIONS POOL.

Состояние пула внешних подключений можно запросить с использованием контекстных переменных в пространстве имен SYSTEM:

Table 1. Переменные пространства имён SYSTEM для контроля пула внешних соединений
Переменная	Описание
EXT_CONN_POOL_SIZE	Размер пула.
EXT_CONN_POOL_LIFETIME	Время жизни неактивных соединений.
EXT_CONN_POOL_IDLE_COUNT	Текущее количество неактивных соединений в пуле.
EXT_CONN_POOL_ACTIVE_COUNT	Текущее количество активных соединений в пуле.

Особенности внешних подключений

Внешние соединения используют по умолчанию предложение WITH COMMON TRANSACTION и остаются открытыми до закрытия текущей транзакции. Они могут быть снова использованы при последующих вызовах оператора EXECUTE STATEMENT, но только если строка подключения точно такая же. Если включен пул внешних соединений, то вместо закрытия соединения, такие соединения будут попадать в список неактивных (простаивающих) соединений;
Внешние соединения, созданные с использованием предложения WITH AUTONOMOUS TRANSACTION, закрываются после выполнения оператора или попадают в список неактивных соединений пула (если он включен);
Операторы WITH AUTONOMOUS TRANSACTION могут использовать соединения, которые ранее были открыты операторами WITH COMMON TRANSACTION. В этом случае использованное соединение остаётся открытым и после выполнения оператора, т.к. у этого соединения есть, по крайней мере, одна не закрытая транзакция. Если включен пул внешних соединений, то вместо закрытия соединения, такие соединения будут попадать в список неактивных (простаивающих) соединений;
Если локальная транзакция запущена в режиме изолированности READ COMMITTED READ CONSISTENCY и внешний источник данных не поддерживает данный режим изолированности, то внешняя транзакция будет запущена в режиме изолированности SNAPSHOT (CONCURRENCY).

Особенности пула транзакций (Transaction pooling)

При использовании предложения WITH COMMON TRANSACTION транзакции будут снова использованы как можно дольше. Они будут подтверждаться или откатываться вместе с текущей транзакцией;
При использовании предложения WITH AUTONOMOUS TRANSACTION всегда запускается новая транзакция. Она будет подтверждена или отменена сразу же после выполнения оператора;

Особенности обработки исключений

При использовании предложения ON EXTERNAL дополнительное соединение всегда делается через так называемого внешнего провайдера, даже если это соединение к текущей базе данных.Одним из последствий этого является то, что вы не можете обработать исключение привычными способами.Каждое исключение, вызванное оператором, возвращает eds_connection или eds_statement ошибки.Для обработки исключений в коде PSQL вы должны использовать WHEN GDSCODE eds_connection, WHEN GDSCODE eds_statement или WHEN ANY.

Note	Если предложение `ON EXTERNAL` не используется, то исключения перехватываются в обычном порядке, даже если это дополнительное соединение с текущей базой данных.

Другие замечания

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

AS USER, PASSWORD и ROLE

Необязательные предложения AS USER, PASSWORD и ROLE позволяют указывать от имени какого пользователя, и с какой ролью будет выполняться SQL оператор.То, как авторизуется пользователь и открыто ли отдельное соединение, зависит от присутствия и значений параметров ON EXTERNAL [DATA SOURCE], AS USER, PASSWORD и ROLE.

При использовании предложения ON EXTERNAL открывается новое соединение и:
- Если присутствует, по крайней мере, один из параметров AS USER, PASSWORD и ROLE, то будет предпринята попытка нативной аутентификации с указанными значениями параметров (в зависимости от строки соединения — локально или удалённо). Для недостающих параметров не используются никаких значений по умолчанию;
- Если все три параметра отсутствуют, и строка подключения не содержит имени сервера (или IP адреса), то новое соединение устанавливается к локальному серверу с пользователем и ролью текущего соединения. Термин 'локальный' означает 'компьютер, где установлен сервер Firebird'. Это совсем не обязательно компьютер клиента;
- Если все три параметра отсутствуют, но строка подключения содержит имя сервера (или IP адреса), то будет предпринята попытка доверенной (trusted) авторизации к удалённому серверу. Если авторизация прошла, то удалённая операционная система назначит пользователю имя — обычно это учётная запись, под которой работает сервер Firebird.
Если предложение ON EXTERNAL отсутствует:
- Если присутствует, по крайней мере, один из параметров AS USER, PASSWORD и ROLE, то будет открыто соединение к текущей базе данных с указанными значениями параметров. Для недостающих параметров не используются никаких значений по умолчанию;
- Если все три параметра отсутствуют, то оператор выполняется в текущем соединении.

Important

Если значение параметра NULL или '', то весь параметр считается отсутствующим.Кроме того, если параметр считается отсутствующим, то AS USER принимает значение CURRENT_USER, а ROLE — CURRENT_ROLE.Сравнение при авторизации сделано чувствительным к регистру: в большинстве случаев это означает, что имена пользователя и роли должны быть написаны в верхнем регистре.

Предостережения

Не существует способа проверить синтаксис выполняемого SQL оператора;
Нет никаких проверок зависимостей для обнаружения удалённых столбцов в таблице или самой таблицы;
Выполнение оператора с помощью оператора EXECUTE STATEMENT значительно медленнее, чем при непосредственном выполнении;
Возвращаемые значения строго проверяются на тип данных во избежание непредсказуемых исключений преобразования типа. Например, строка '1234' преобразуется в целое число 1234, а строка 'abc' вызовет ошибку преобразования.

В целом эта функция должна использоваться очень осторожно, а вышеупомянутые факторы всегда должны приниматься во внимание.Если такого же результата можно достичь с использованием PSQL и/или DSQL, то это всегда предпочтительнее.

См. также:

FOR EXECUTE STATEMENT.

`FOR SELECT`

Назначение

Цикл по строкам результата выполнения оператора SELECT.

Доступно в

PSQL

Синтаксис

[label:]
FOR
  <select_stmt>
  [INTO <variables>]
  [AS CURSOR cursorname]
DO <compound_statement>

<variables> ::= [:{endsb}varname [, [:{endsb}varname ...]

Table 1. Параметры оператора `FOR SELECT`
Параметр	Описание
label	Необязательная метка для `LEAVE` и/или `CONTINUE`.Должна следовать правилам для идентикаторов.
select_stmt	Оператор `SELECT`.
cursorname	Имя курсора.Должно быть уникальным среди имён переменных и курсоров PSQL модуля.
varname	Имя локальной переменной или входного/выходного параметра.
compound_statement	Составной оператор (оператор или блок операторов).

Оператор FOR SELECT выбирает очередную строку из таблицы (представления, селективной хранимой процедуры), после чего выполняется составной оператор.В каждой итерации цикла значения полей текущей строки копируются в локальные переменные.Добавление предложения AS CURSOR делает возможным позиционное удаление и обновление данных.Операторы FOR SELECT могут быть вложенными.

Оператор FOR SELECT может содержать именованные параметры, которые должны быть предварительно объявлены в операторе DECLARE VARIABLE, или во входных (выходных) параметрах процедуры (PSQL блока).

Оператор FOR SELECT должен содержать предложение INTO, которое располагается в конце этого оператора, или предложение AS CURSOR.На каждой итерации цикла в список переменных указанных в предложении INTO копируются значения полей текущей строки запроса.Цикл повторяется, пока не будут прочитаны все строки.После этого происходит выход из цикла.Цикл также может быть завершён до прочтения всех строк при использовании оператора LEAVE.

Необъявленный курсор

Необязательное предложение AS CURSOR создаёт именованный курсор, на который можно ссылаться (с использованием предложения WHERE CURRENT OF) внутри составного оператора следующего после предложения DO, для того чтобы удалить или модифицировать текущую строку.

Разрешается использовать имя курсора как переменную типа запись (аналогично OLD и NEW в триггерах), что позволяет получить доступ к столбцам результирующего набора (т.е. cursor_name . columnname).Использование предложение AS CURSOR делает предложение INTO необязательным.

Правила для курсорных переменных:

Для разрешения неоднозначности при доступе к переменной курсора перед именем курсора необходим префикс двоеточие;
К переменной курсора можно получить доступ без префикса двоеточия, но в этом случае, в зависимости от области видимости контекстов, существующих в запросе, имя может разрешиться как контекст запроса вместо курсора;
Переменные курсора доступны только для чтения;
В операторе FOR SELECT без предложения AS CURSOR необходимо использовать предложение INTO. Если указано предложение AS CURSOR, предложение INTO не требуется, но разрешено;
Чтение из переменной курсора возвращает текущие значения полей. Это означает, что оператор UPDATE (с предложением WHERE CURRENT OF) обновит также и значения полей в переменной курсора для последующих чтений. Выполнение оператора DELETE (с предложением WHERE CURRENT OF) установит NULL для значений полей переменной курсора для последующих чтений.

Note

Над курсором, объявленным с помощью предложения AS CURSOR нельзя выполнять операторы OPEN, FETCH и CLOSE;
Убедитесь, что имя курсора, определённое здесь, не совпадает ни с какими именами, созданными ранее оператором DECLARE VARIABLE;
Предложение FOR UPDATE, разрешённое для использования в операторе SELECT, не является обязательным для успешного выполнения позиционного обновления или удаления.

Примеры использования `FOR SELECT`

Example 1. Использование оператора FOR SELECT

CREATE PROCEDURE SHOWNUMS
RETURNS (
  AA INTEGER,
  BB INTEGER,
  SM INTEGER,
  DF INTEGER)
AS
BEGIN
  FOR SELECT DISTINCT A, B
      FROM NUMBERS
    ORDER BY A, B
    INTO AA, BB
  DO
  BEGIN
    SM = AA + BB;
    DF = AA - BB;
    SUSPEND;
  END
END

Example 2. Вложенный FOR SELECT

CREATE PROCEDURE RELFIELDS
RETURNS (
  RELATION CHAR(32),
  POS INTEGER,
  FIELD CHAR(32))
AS
BEGIN
  FOR SELECT RDB$RELATION_NAME
      FROM RDB$RELATIONS
      ORDER BY 1
      INTO :RELATION
  DO
  BEGIN
    FOR SELECT
          RDB$FIELD_POSITION + 1,
          RDB$FIELD_NAME
        FROM RDB$RELATION_FIELDS
        WHERE
          RDB$RELATION_NAME = :RELATION
        ORDER BY RDB$FIELD_POSITION
        INTO :POS, :FIELD
    DO
    BEGIN
      IF (POS = 2) THEN
        RELATION = ' "';
      -- Для исключения повтора имён таблиц и представлений
      SUSPEND;
    END
  END
END

Example 3. Использование предложения AS CURSOR для позиционного удаления записи

CREATE PROCEDURE DELTOWN (
  TOWNTODELETE VARCHAR(24))
RETURNS (
  TOWN VARCHAR(24),
  POP INTEGER)
AS
BEGIN
  FOR SELECT TOWN, POP
      FROM TOWNS
      INTO :TOWN, :POP
      AS CURSOR TCUR
  DO
  BEGIN
    IF (:TOWN = :TOWNTODELETE) THEN
      -- Позиционное удаление записи
      DELETE FROM TOWNS
      WHERE CURRENT OF TCUR;
    ELSE
      SUSPEND;
  END
END

Example 4. Использование неявно объявленного курсора как курсорной переменной

EXECUTE BLOCK
RETURNS (
    o CHAR(63))
AS
BEGIN
  FOR
      SELECT
          rdb$relation_name AS name
      FROM
          rdb$relations AS CURSOR c
  DO
  BEGIN
    o = c.name;
    SUSPEND;
  END
END

Example 5. Разрешение неоднозначностей курсорной переменной внутри запросов

EXECUTE BLOCK
RETURNS (
    o1 CHAR(63),
    o2 CHAR(63))
AS
BEGIN
  FOR
      SELECT
          rdb$relation_name
      FROM
          rdb$relations
      WHERE
          rdb$relation_name = 'RDB$RELATIONS' AS CURSOR c
  DO
  BEGIN
    FOR
        SELECT
            -- с префиксом разрешается как курсор
            :c.rdb$relation_name x1,
            -- без префикса как псевдоним таблицы rdb$relations
            c.rdb$relation_name x2
        FROM
            rdb$relations c
        WHERE
            rdb$relation_name = 'RDB$DATABASE' AS CURSOR d
    DO
    BEGIN
      o1 = d.x1;
      o2 = d.x2;
      SUSPEND;
    END
  END
END

См. также:

SELECT,DECLARE …CURSOR,OPEN,CLOSE,FETCH.

`FOR EXECUTE STATEMENT`

Назначение

Выполнение динамически созданных SQL операторов с возвратом нескольких строк данных.

Доступно в

PSQL

Синтаксис

[label:]
FOR <execute_statement> DO <compound_statement>

Table 1. Параметры оператора `FOR EXECUTE STATEMENT`
Параметр	Описание
label	Необязательная метка для `LEAVE` и/или `CONTINUE`.Должна соответствовать правилам для идентификаторов.
execute_statement	Оператор `EXECUTE STATEMENT`.
compound_statement	Составной оператор (оператор или блок операторов).

Оператор FOR EXECUTE STATEMENT используется (по аналогии с конструкцией FOR SELECT) для операторов SELECT или EXECUTE BLOCK, возвращающих более одной строки.

Примеры `FOR EXECUTE STATEMENT

Example 1. Использование оператора FOR EXECUTE STATEMENT

CREATE PROCEDURE DynamicSampleThree (
   Q_FIELD_NAME VARCHAR(100),
   Q_TABLE_NAME VARCHAR(100)
) RETURNS(
  LINE VARCHAR(32000)
)
AS
  DECLARE VARIABLE P_ONE_LINE VARCHAR(100);
BEGIN
  LINE = '';
  FOR
    EXECUTE STATEMENT
      'SELECT T1.' || :Q_FIELD_NAME || ' FROM ' || :Q_TABLE_NAME || ' T1 '
    INTO :P_ONE_LINE
  DO
    IF (:P_ONE_LINE IS NOT NULL) THEN
      LINE = :LINE || :P_ONE_LINE || ' ';
  SUSPEND;
END

См. также:

EXECUTE STATEMENT.

`OPEN`

Назначение

Открытие курсора.

Доступно в

PSQL

Синтаксис

OPEN cursor_name;

Table 1. Параметры оператора OPEN
Параметр	Описание
cursor_name	Имя курсора.Курсор с таким именем должен быть предварительно объявлен с помощью оператора `DECLARE … CURSOR`.

Оператор OPEN открывает ранее объявленный курсор, выполняет объявленный в нем оператор SELECT и получает записи из результирующего набора данных.Оператор OPEN применим только к курсорам, объявленным в операторе DECLARE … CURSOR.

Note	Если в операторе `SELECT` курсора имеются параметры, то они должны быть объявлены как локальные переменные или входные (выходные) параметры до того как объявлен курсор.При открытии курсора параметру присваивается текущее значение переменной.

Примеры `OPEN`

Example 1. Использование оператора OPEN

SET TERM ^;

CREATE OR ALTER PROCEDURE GET_RELATIONS_NAMES
RETURNS (
  RNAME CHAR(31)
)
AS
  DECLARE C CURSOR FOR (
    SELECT RDB$RELATION_NAME
    FROM RDB$RELATIONS);
BEGIN
  OPEN C;
  WHILE (1 = 1) DO
  BEGIN
    FETCH C INTO :RNAME;
    IF (ROW_COUNT = 0) THEN
      LEAVE;
    SUSPEND;
  END
  CLOSE C;
END^

SET TERM ;^

Example 2. Использование оператора OPEN с параметрами

Данный пример возвращает набор скриптов для создания представлений с использованием блока PSQL с именованными курсорами.

EXECUTE BLOCK
RETURNS (
  SCRIPT BLOB SUB_TYPE TEXT)
AS
  DECLARE VARIABLE FIELDS VARCHAR(8191);
  DECLARE VARIABLE FIELD_NAME TYPE OF RDB$FIELD_NAME;
  DECLARE VARIABLE RELATION RDB$RELATION_NAME;
  DECLARE VARIABLE SOURCE TYPE OF COLUMN RDB$RELATIONS.RDB$VIEW_SOURCE;
  -- именованный курсор
  DECLARE VARIABLE CUR_R CURSOR FOR (
    SELECT
      RDB$RELATION_NAME,
      RDB$VIEW_SOURCE
    FROM
      RDB$RELATIONS
    WHERE
      RDB$VIEW_SOURCE IS NOT NULL);
  -- Именованный курсор
  DECLARE CUR_F CURSOR FOR (
    SELECT
      RDB$FIELD_NAME
    FROM
      RDB$RELATION_FIELDS
    WHERE
      -- Важно! Переменная должна быть объявлена ранее
      RDB$RELATION_NAME = :RELATION);
BEGIN
  OPEN CUR_R;
  WHILE (1 = 1) DO
  BEGIN
    FETCH CUR_R
      INTO :RELATION, :SOURCE;
    IF (ROW_COUNT = 0) THEN
      LEAVE;

    FIELDS = NULL;
    -- Курсор CUR_F использует
    -- значение переменной RELATION инициализированной ранее
    OPEN CUR_F;
    WHILE (1 = 1) DO
    BEGIN
      FETCH CUR_F
        INTO :FIELD_NAME;
      IF (ROW_COUNT = 0) THEN
        LEAVE;
      IF (FIELDS IS NULL) THEN
        FIELDS = TRIM(FIELD_NAME);
      ELSE
        FIELDS = FIELDS || ', ' || TRIM(FIELD_NAME);
    END
    CLOSE CUR_F;

    SCRIPT = 'CREATE VIEW ' || RELATION;

    IF (FIELDS IS NOT NULL) THEN
      SCRIPT = SCRIPT || ' (' || FIELDS || ')';

    SCRIPT = SCRIPT || ' AS ' || ASCII_CHAR(13);
    SCRIPT = SCRIPT || SOURCE;

    SUSPEND;
  END
  CLOSE CUR_R;
END

См. также:

FETCH,CLOSE,DECLARE … CURSOR.

`FETCH`

Назначение

Чтение записи из набора данных, связанного с курсором.

Доступно в

PSQL

Синтаксис

FETCH [<fetch_scroll> FROM] cursor_name
  [INTO [:]varname [, [:]varname ...]];

<fetch_scroll> ::=
    NEXT | PRIOR | FIRST | LAST
  | RELATIVE n
  | ABSOLUTE n

Table 1. Параметры оператора FETCH
Параметр	Описание
cursor_name	Имя курсора.Курсор с таким именем должен быть предварительно объявлен с помощью оператора `DECLARE … CURSOR`.
var_name	PSQL переменная.
n	Целое число.

Оператор FETCH выбирает следующую строку данных из результирующего набора данных курсора и присваивает значения столбцов в переменные PSQL.Оператор FETCH применим только к курсорам, объявленным в операторе DECLARE … CURSOR.

Оператор FETCH может указывать в каком направлении и на сколько записей продвинется позиция курсора.Предложение NEXT допустимо использовать как с прокручиваемыми, там и не прокручиваемыми курсорами.

Остальные предложения допустимо использовать только с прокручиваемыми курсорами.

Опции прокручиваемого курсора

NEXT: перемещает указатель курсора на 1 запись вперёд. Это действие по умолчанию.
PRIOR: перемещает указатель курсора на 1 запись назад.
FIRST: перемещает указатель курсора на первую запись.
LAST: перемещает указатель курсора на последнюю запись.
ABSOLTE n: перемещает указатель курсора на указанную запись;n — целочисленное выражение, где 1 обозначает первую строку.Для отрицательных значений абсолютная позиция берется с конца набора результатов, поэтому -1 указывает последнюю строку,-2 - предпоследнюю строку и т. д.Нулевое значение (0) будет располагаться перед первой строкой.
RELATIVE n: перемещает курсор на n строк из текущей позиции;положительные числа перемещают указатель вперед, а отрицательные числа — назад;использование нуля (0) не приведет к перемещению курсора, а ROW_COUNT будет установлено в ноль, поскольку новая строка не была выбрана.

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

Правила использования курсорных переменных

Для разрешения неоднозначности при доступе к переменной курсора перед именем курсора необходим префикс двоеточие;
К переменной курсора можно получить доступ без префикса двоеточия, но в этом случае, в зависимости от области видимости контекстов, существующих в запросе, имя может разрешиться как контекст запроса вместо курсора;
Переменные курсора доступны только для чтения;
Чтение из переменной курсора возвращает текущие значения полей. Это означает, что оператор UPDATE (с предложением WHERE CURRENT OF) обновит также и значения полей переменной курсора для последующих чтений. Выполнение оператора DELETE (с предложением WHERE CURRENT OF) установит NULL для значений полей переменной курсора для последующих чтений.

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

Примеры `FETCH`

Example 1. Использования оператора FETCH

SET TERM ^;

CREATE OR ALTER PROCEDURE GET_RELATIONS_NAMES
RETURNS (
  RNAME CHAR(63)
)
AS
  DECLARE C CURSOR FOR (SELECT RDB$RELATION_NAME FROM RDB$RELATIONS);
BEGIN
  OPEN C;
  WHILE (1 = 1) DO
  BEGIN
    FETCH C INTO :RNAME;
    IF (ROW_COUNT = 0) THEN
      LEAVE;
    SUSPEND;
  END
  CLOSE C;
END^

SET TERM ;^

Example 2. Использования оператора FETCH со вложенными курсорами

EXECUTE BLOCK
RETURNS (
    SCRIPT BLOB SUB_TYPE TEXT)
AS
DECLARE VARIABLE FIELDS VARCHAR(8191);
DECLARE VARIABLE FIELD_NAME TYPE OF RDB$FIELD_NAME;
DECLARE VARIABLE RELATION RDB$RELATION_NAME;
DECLARE VARIABLE SRC   TYPE OF COLUMN RDB$RELATIONS.RDB$VIEW_SOURCE;
-- Объявление именованного курсора
DECLARE VARIABLE CUR_R      CURSOR FOR (
    SELECT
        RDB$RELATION_NAME,
        RDB$VIEW_SOURCE
    FROM
        RDB$RELATIONS
    WHERE
        RDB$VIEW_SOURCE IS NOT NULL);
-- Объявление именованного курсора, в котором
-- используется локальная переменная
DECLARE CUR_F      CURSOR FOR (
    SELECT
        RDB$FIELD_NAME
    FROM
        RDB$RELATION_FIELDS
    WHERE
        -- Важно переменная должна быть объявлена ранее
        RDB$RELATION_NAME = :RELATION);
BEGIN
  OPEN CUR_R;
  WHILE (1 = 1) DO
  BEGIN
    FETCH CUR_R
    INTO :RELATION, :SRC;
    IF (ROW_COUNT = 0) THEN
      LEAVE;

    FIELDS = NULL;
    -- Курсор CUR_F будет использовать значение
    -- переменной RELATION инициализированной выше
    OPEN CUR_F;
    WHILE (1 = 1) DO
    BEGIN
      FETCH CUR_F
      INTO :FIELD_NAME;
      IF (ROW_COUNT = 0) THEN
        LEAVE;
      IF (FIELDS IS NULL) THEN
        FIELDS = TRIM(FIELD_NAME);
      ELSE
        FIELDS = FIELDS || ', ' || TRIM(FIELD_NAME);
    END
    CLOSE CUR_F;

    SCRIPT = 'CREATE VIEW ' || RELATION;

    IF (FIELDS IS NOT NULL) THEN
      SCRIPT = SCRIPT || ' (' || FIELDS || ')';

    SCRIPT = SCRIPT || ' AS ' || ASCII_CHAR(13);
    SCRIPT = SCRIPT || SRC;

    SUSPEND;
  END
  CLOSE CUR_R;
END

Example 3. Пример использования оператора FETCH с прокручиваемым курсором

EXECUTE BLOCK
RETURNS (
  N INT,
  RNAME CHAR(63))
AS
  DECLARE C SCROLL CURSOR FOR (
    SELECT
      ROW_NUMBER() OVER(ORDER BY RDB$RELATION_NAME) AS N,
      RDB$RELATION_NAME
    FROM RDB$RELATIONS
    ORDER BY RDB$RELATION_NAME);
BEGIN
  OPEN C;
  -- перемещаемся на первую запись (N=1)
  FETCH FIRST FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- перемещаемся на 1 запись вперёд (N=2)
  FETCH NEXT FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- перемещаемся на пятую запись (N=5)
  FETCH ABSOLUTE 5 FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- перемещаемся на 1 запись назад (N=4)
  FETCH PRIOR FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- перемещаемся на 3 записи вперёд (N=7)
  FETCH RELATIVE 3 FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- перемещаемся на 5 записей назад (N=2)
  FETCH RELATIVE -5 FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- перемещаемся на первую запись (N=1)
  FETCH FIRST FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  -- перемещаемся на последнюю запись
  FETCH LAST FROM C;
  RNAME = C.RDB$RELATION_NAME;
  N = C.N;
  SUSPEND;
  CLOSE C;
END

См. также:

OPEN, CLOSE, DECLARE … CURSOR.

`CLOSE`

Назначение

Закрытие курсора.

Доступно в

PSQL

Синтаксис

CLOSE cursor_name;

Table 1. Параметры оператора `CLOSE`
Параметр	Описание
cursor_name	Имя открытого курсора.Курсор с таким именем должен быть предварительно объявлен с помощью оператора `DECLARE … CURSOR`.

Оператор CLOSE закрывает открытый курсор.Любые все ещё открытые курсоры будут автоматически закрыты после выполнения кода триггера, хранимой процедуры, функции или анонимного PSQL блока, в пределах кода которого он был открыт.Оператор CLOSE применим только к курсорам, объявленным в операторе DECLARE … CURSOR.

Примеры `CLOSE`

См. примеры в операторе FETCH.

См. также:

FETCH, OPEN, DECLARE … CURSOR.

`DECLARE VARIABLE`

Назначение

Объявление локальной переменной.

Доступно в

PSQL

Синтаксис

DECLARE [VARIABLE] varname
  <type> [NOT NULL] [COLLATE collation]
  [{= | DEFAULT} <initvalue>] }

<type> ::=
    <non_array_datatype>
  | [TYPE OF] domain
  | TYPE OF COLUMN rel.col

<non_array_datatype> ::=
    <scalar_datatype> | <blob_datatype>

<scalar_datatype> ::=  См. Синтаксис скалярных типов данных

<blob_datatype> ::= См. Синтаксис типа данных BLOB

<initvalue> ::= {<literal> | <context_var>}

Table 1. Параметры оператора `DECLARE VARIABLE`
Параметр	Описание
varname	Имя локальной переменной.
literal	Литерал.
context_var	Любая контекстная переменная, тип которой совместим с типом локальной переменной.
non_array_datatype	Тип данных SQL кроме массивов.
collation	Порядок сортировки.
domain	Домен.
rel	Имя таблицы или представления.
col	Имя столбца таблицы или представления.

Оператор DECLARE [VARIABLE] объявляет локальную переменную.Ключевое слово VARIABLE можно опустить.В одном операторе разрешено объявлять только одну переменную.В процедурах и триггерах можно объявить произвольное число локальных переменных, используя при этом каждый раз, новый оператор DECLARE VARIABLE.

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

Типы данных для переменных

В качестве типа данных локальной переменной может быть любой SQL тип, за исключением массивов.

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

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

Ограничение `NOT NULL`

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

Предложения `CHARACTER SET` и `COLLATE`

Если не указано иное, набор символов и последовательность сопоставления (сортировки) строковой переменной будут значениями по умолчанию для базы данных.

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

Допустимая последовательность сопоставления (предложение COLLATE) также может быть включена с CHARACTER SET или без него.

Инициализация переменной

Локальной переменной можно устанавливать инициализирующее (начальное) значение.Это значение устанавливается с помощью предложения DEFAULT или оператора “=”.В качестве значения по умолчанию может быть использовано значение NULL,литерал и любая контекстная переменная совместимая по типу данных.

Important

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

Примеры объявления локальных переменных

Example 1. Различные способы объявления локальных переменных

CREATE OR ALTER PROCEDURE SOME_PROC
AS
  -- Объявление переменной типа INT
  DECLARE I INT;
  -- Объявление переменной типа INT не допускающей значение NULL
  DECLARE VARIABLE J INT NOT NULL;
  -- Объявление переменной типа INT со значением по умолчанию 0
  DECLARE VARIABLE K INT DEFAULT 0;
  -- Объявление переменной типа INT со значением по умолчанию 1
  DECLARE VARIABLE L INT = 1;
  -- Объявление переменной на основе домена COUNTRYNAME
  DECLARE FARM_COUNTRY COUNTRYNAME;
  -- Объявление переменной с типом равным типу домена COUNTRYNAME
  DECLARE FROM_COUNTRY TYPE OF COUNTRYNAME;
  -- Объявление переменной с типом столбца CAPITAL таблицы COUNTRY
  DECLARE CAPITAL TYPE OF COLUMN COUNTRY.CAPITAL;
BEGIN
  /* Операторы PSQL */
END

См. также:

Типы и подтипы данных,Пользовательские типы данных — домены,CREATE DOMAIN

`IN AUTONOMOUS TRANSACTION`

Назначение

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

Доступно в

PSQL.

Синтаксис

IN AUTONOMOUS TRANSACTION DO <compound_statement>

Table 1. Параметры оператора IN AUTONOMOUS TRANSACTION
Параметр	Описание
compound_statement	Составной оператор (оператор или блок операторов).

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

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

Примеры `IN AUTONOMOUS TRANSACTION`

Example 1. Использование автономных транзакций

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

CREATE TRIGGER TR_CONNECT ON CONNECT
AS
BEGIN
  -- Все попытки соединения с БД сохраняем в журнал
  IN AUTONOMOUS TRANSACTION DO
    INSERT INTO LOG(MSG)
    VALUES ('USER ' || CURRENT_USER || ' CONNECTS.');
  IF (CURRENT_USER IN (SELECT
                           USERNAME
                       FROM
                           BLOCKED_USERS)) THEN
  BEGIN
    -- Сохраняем в журнал, что попытка соединения
    -- с БД оказалась неудачной
    -- и отправляем сообщение о событии
    IN AUTONOMOUS TRANSACTION DO
    BEGIN
      INSERT INTO LOG(MSG)
      VALUES ('USER ' || CURRENT_USER || ' REFUSED.');
      POST_EVENT 'CONNECTION ATTEMPT' || ' BY BLOCKED USER!';
    END
    -- теперь вызываем исключение
    EXCEPTION EX_BADUSER;
  END
END

См. также:

Управление транзакциями.

`POST_EVENT`

Назначение

Посылка события (сообщения) клиентским приложениям.

Доступно в

PSQL

Синтаксис

POST_EVENT event_name;

Table 1. Параметры оператора `POST_EVENT`
Параметр	Описание
event_name	Имя события, ограничено 127 байтами.

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

Имя события это своего рода код или короткое сообщение, выбор за вами, т.к.это просто строка длинной до 127 байт.

В качестве имени события может быть использован строковый литерал, переменная или любое правильное SQL выражение.

Примеры `POST_EVENT`

Example 1. Оповещение приложения о вставке записи в таблицу SALES

SET TERM ^;
CREATE TRIGGER POST_NEW_ORDER FOR SALES
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
  POST_EVENT 'new_order';
END^
SET TERM ;^

`RETURN`

Назначение

Возврат значения из хранимой функции

Доступно в

PSQL

Синтаксис

RETURN value;

Table 1. Параметры оператора `RETURN`
Параметр	Описание
value	Выражение для возврата значения из функции;Может быть любым выражением, совместимым с типом возвращаемого значения функции.

Оператор RETURN завершает выполнение функции и возвращает значение выражения value.

RETURN может использоваться только в PSQL функциях (хранимых и локальных функциях).

Примеры `RETURN`

См. Примеры CREATE FUNCTION

Обработка ошибок

В Firebird существуют PSQL операторы для обработки ошибок и исключений в модулях.Существует множество встроенных исключений, которые возникают в случае возникновения стандартных ошибок при работе с DML и DDL операторами.

Системные исключения

Исключение представляет собой сообщение, которое генерируется, когда возникает ошибка.

Все обрабатываемые Firebird исключения имеют заранее определённые числовые (символьные) значение для контекстных переменных и связанные с ними тексты сообщений.Сообщения об ошибке написаны по умолчанию на английском языке.Существуют и локализованные сборки СУБД, в которых сообщения об ошибках переведены на другие языки.

Полный список системных исключений вы можете найти в приложении "Обработка ошибок, коды и сообщения":

Коды ошибок SQLSTATE и их описание
"Коды ошибок GDSCODE их описание, и SQLCODE"

Пользовательские исключения

Пользовательские исключения могут быть объявлены в базе данных как постоянные объекты и вызваны из PSQL кода для сообщения об ошибке при нарушении некоторых бизнес правил.Текст пользовательского исключения ограничен 1021 байтом.Подробности см. CREATE EXCEPTION.

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

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

`EXCEPTION`

Назначение

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

Доступно в

PSQL

Синтаксис

EXCEPTION [
    exception_name
    [ custom_message | USING (<value_list>)]
  ]

<value_list> ::= <val> [, <val> ...]

Table 1. Параметры оператора EXCEPTION
Параметр	Описание
exception_name	Имя исключения.
custom_message	Альтернативный текст сообщения, выдаваемый при возникновении исключения.Максимальная длина текстового сообщения составляет 1021 байт.
val	Значения, которыми заменяются слоты в тексте сообщения исключения.

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

Текст сообщения исключения может содержать слоты для параметров, которые заполняются при возбуждении исключения.Для передачи значений параметров в исключение используется предложение USING.Параметры рассматриваются слева направо.Каждый параметр передаётся в оператор возбуждающий исключение как “N-ый”, N начинается с 1:

Если N-ый параметр не передан, его слот не заменяется;
Если передано значение NULL, слот будет заменён на строку “*** null ***”;
Если количество передаваемых параметров будет больше, чем содержится в сообщении исключения, то лишние будут проигнорированы;
Максимальный номер параметра равен 9;
Общая длина сообщения, включая значения параметров, ограничена 1053 байтами.

Note

Статус вектор генерируется, используя комбинацию кодов isc_except, <exception number>, isc_formatted_exception, <formatted exception message>, <exception parameters>.

Поскольку используется новый код ошибки (isc_formatted_exception), клиент должен быть версии 3.0 или по крайней мере использовать firebird.msg от версии 3.0 для того чтобы правильно преобразовать статус вектор в строку.

Warning

Если в тексте сообщения, встретится номер слота параметра больше 9, то второй и последующий символ будут восприняты как литералы.Например, @10 будет воспринято как @1 после которого следует литерал ‘0’.

CREATE EXCEPTION ex1
'something wrong in @1 @2 @3 @4 @5 @6 @7 @8 @9 @10 @11';

EXECUTE BLOCK AS
BEGIN
  EXCEPTION ex1 USING ('a','b','c','d','e','f','g','h','i');
END^

Statement failed, SQLSTATE = HY000
exception 1
-EX1
-something wrong in a b c d e f g h i a0 a1

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

В блоке обработки исключений (и только в нем), вы можете повторно вызвать пойманное исключение или ошибку, вызывая оператор EXCEPTION без параметров.Вне блока с исключением такой вызов не имеет никакого эффекта.

Note	Пользовательские исключения хранятся в системной таблице `RDB$EXCEPTIONS`.

Примеры `EXCEPTION`

Example 1. Вызов исключения

CREATE OR ALTER PROCEDURE SHIP_ORDER (
    PO_NUM CHAR(8))
AS
DECLARE VARIABLE ord_stat  CHAR(7);
DECLARE VARIABLE hold_stat CHAR(1);
DECLARE VARIABLE cust_no   INTEGER;
DECLARE VARIABLE any_po    CHAR(8);
BEGIN
  SELECT
      s.order_status,
      c.on_hold,
      c.cust_no
  FROM
      sales s, customer c
  WHERE
      po_number = :po_num AND
      s.cust_no = c.cust_no
  INTO :ord_stat,
       :hold_stat,
       :cust_no;

  /* Этот заказ уже отправлен на поставку. */
  IF (ord_stat = 'shipped') THEN
    EXCEPTION order_already_shipped;
  /* Другие операторы */
END

Example 2. Вызов исключения с заменой исходного сообщения альтернативным

CREATE OR ALTER PROCEDURE SHIP_ORDER (
    PO_NUM CHAR(8))
AS
DECLARE VARIABLE ord_stat  CHAR(7);
DECLARE VARIABLE hold_stat CHAR(1);
DECLARE VARIABLE cust_no   INTEGER;
DECLARE VARIABLE any_po    CHAR(8);
BEGIN
  SELECT
      s.order_status,
      c.on_hold,
      c.cust_no
  FROM
      sales s, customer c
  WHERE
      po_number = :po_num AND
      s.cust_no = c.cust_no
  INTO :ord_stat,
       :hold_stat,
       :cust_no;

  /* Этот заказ уже отправлен на поставку. */
  IF (ord_stat = 'shipped') THEN
    EXCEPTION order_already_shipped 'Order status is "' || ord_stat || '"';
  /* Другие операторы */
END

Example 3. Использование параметризованного исключения

CREATE EXCEPTION EX_BAD_SP_NAME
  'Name of procedures must start with ''@1'' : ''@2''';
...
CREATE TRIGGER TRG_SP_CREATE BEFORE CREATE PROCEDURE
AS
  DECLARE SP_NAME VARCHAR(255);
BEGIN
  SP_NAME = RDB$GET_CONTEXT('DDL_TRIGGER', 'OBJECT_NAME');
  IF (SP_NAME NOT STARTING 'SP_') THEN
    EXCEPTION EX_BAD_SP_NAME USING ('SP_', SP_NAME);
END^

См. также:

CREATE EXCEPTION, WHEN … DO.

`WHEN … DO`

Назначение

Обработка ошибок.

Доступно в

PSQL

Синтаксис

WHEN {<error> [, <error> ...] | ANY}
DO <compound_statement>

<error> ::= {
    EXCEPTION exception_name
  | SQLCODE number
  | GDSCODE errcode
  | SQLSTATE 'sqlstate_code'
}

Table 1. Параметры оператора `WHEN … DO`
Параметр	Описание
exception_name	Имя исключения.
number	Код ошибки SQLCODE.
errcode	Символическое имя ошибки GDSCODE.
sqlstate_code	Код ошибки SQLSTATE.
compound_statement	Оператор или блок операторов.

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

Оператор WHEN … DO должен находиться в самом конце блока операторов перед оператором END.

После ключевого слова DO следует составной оператор, в котором можно произвести обработку ошибки или исключения.Составной оператор — это оператор или блок операторов, заключённый в операторные скобки BEGIN и END.В этом операторе доступны контекстные переменные GDSCODE, SQLCODE, SQLSTATE.Для получения имени активного пользовательского исключения или текста интерпретированного сообщения об ошибке вы можете воспользоваться системной функцией RDB$ERROR.В этом же блоке доступен оператор повторного вызова ошибки или исключительной ситуации EXCEPTION (без параметров).

Important

После предложения WHEN GDSCODE вы должны использовать символьные имена — такие, как grant_obj_notfound и т.д.Но в составном операторе, после ключевого слова DO доступна контекстная переменная GDSCODE, которая содержит целое число.Для сравнения его с определённой ошибкой вы должны использовать числовое значение, например, 335544551 для grant_obj_notfound.

Оператор WHEN … DO вызывается только в том случае, если произошло одно из указанных в его условии событий.В случае выполнения оператора (даже если в нем фактически не было выполнено никаких действий) ошибка или пользовательское исключение не прерывает и не отменяет действий триггера или хранимой процедуры, где был выдан этот оператор, работа продолжается, как если бы никаких исключительных ситуаций не было.Однако в этом случае будет отменено действие DML оператора (SELECT, INSERT, UPDATE, DELETE, MERGE), который вызвал ошибку и все ниже находящиеся операторы в том же блоке операторов не будут выполнены.

Important

Если ошибка вызвана не одним из DML операторов (SELECT, INSERT, UPDATE, DELETE, MERGE), то будет отменен не только оператор вызвавший ошибку, а весь блок операторов.Кроме того, действия в операторе WHEN … DO так же будут откачены.Это относится также и к оператору выполнения хранимой процедуры EXECUTE PROCEDURE.Подробнее смотри в CORE-4483.

Область действия оператора `WHEN … DO`

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

Оператор WHEN … DO видит все изменения, произведённые до оператора вызвавшего ошибку.Однако если вы попытаетесь запротоколировать их в автономной транзакции, то эти изменения будут не доступны, поскольку на момент старта автономной транзакции, транзакция, в которой произошли эти изменения, не подтверждена.

Примеры использования `WHEN…DO`

Example 1. Замена стандартной ошибки своей.

CREATE EXCEPTION COUNTRY_EXIST '';
SET TERM ^;
CREATE PROCEDURE ADD_COUNTRY (
    ACountryName COUNTRYNAME,
    ACurrency VARCHAR(10) )
AS
BEGIN
  INSERT INTO country (country, currency)
  VALUES (:ACountryName, :ACurrency);

  WHEN SQLCODE -803 DO
    EXCEPTION COUNTRY_EXIST 'Такая страна уже добавлена!';
END^
SET TERM ^;

Example 2. Регистрация ошибке в журнале и повторное её возбуждение в блоке WHEN.

CREATE PROCEDURE ADD_COUNTRY (
    ACountryName COUNTRYNAME,
    ACurrency VARCHAR(10) )
AS
BEGIN
  INSERT INTO country (country,
                       currency)
  VALUES (:ACountryName,
          :ACurrency);
  WHEN ANY DO
  BEGIN
    -- Записываем ошибку в журнал
    IN AUTONOMOUS TRANSACTION DO
      INSERT INTO ERROR_LOG (PSQL_MODULE,
                             ERROR_TEXT,
                             EXCEPTION_NAME,
                             GDS_CODE,
                             SQL_CODE,
                             SQL_STATE)
      VALUES ('ADD_COUNTRY',
              RDB$ERROR(MESSAGE), -- текст сообщения об ошибке
              RDB$ERROR(EXCEPTION), -- имя пользовательского исключения
              GDSCODE,
              SQLCODE,
              SQLSTATE
      );
    -- Повторно возбуждаем ошибку
    EXCEPTION;
  END
END

Example 3. Обработка в одном WHEN … DO блоке нескольких ошибок

...
WHEN GDSCODE GRANT_OBJ_NOTFOUND,
	   GDSCODE GRANT_FLD_NOTFOUND,
	   GDSCODE GRANT_NOPRIV,
	   GDSCODE GRANT_NOPRIV_ON_BASE
DO
BEGIN
	EXECUTE PROCEDURE LOG_GRANT_ERROR(GDSCODE);
	EXIT;
END
...

Example 4. Перехват ошибок по коду SQLSTATE.

EXECUTE BLOCK
AS
    DECLARE VARIABLE I INT;
BEGIN
  BEGIN
    I = 1 / 0;
    WHEN SQLSTATE '22003' DO
      EXCEPTION E_CUSTOM_EXCEPTION
        'Numeric value out of range.';
    WHEN SQLSTATE '22012' DO
      EXCEPTION E_CUSTOM_EXCEPTION 'Division by zero.';
    WHEN SQLSTATE '23000' DO
      EXCEPTION E_CUSTOM_EXCEPTION
        'Integrity constraint violation.';
  END
END

См. также:

Оператор EXCEPTION,Коды ошибок SQLSTATE и их описание,Коды ошибок GDSCODE и SQLCODE и их описание.

`DECLARE … CURSOR`

Назначение:

Объявление курсора.

Доступно в:

PSQL

Синтаксис

DECLARE [VARIABLE] cursor_name
  [SCROLL | NO SCROLL]
  CURSOR FOR (<select_statement>);

Table 1. Параметры оператора `DECLARE … CURSOR`
Параметр	Описание
cursor_name	Имя курсора.
select_statement	Оператор SELECT.

Оператор DECLARE … CURSOR FOR объявляет именованный курсор, связывая его с набором данных, полученным в операторе SELECT, указанном в предложении CURSOR FOR.В дальнейшем курсор может быть открыт, использоваться для обхода результирующего набора данных, и снова быть закрытым.Также поддерживаются позиционированные обновления и удаления при использовании WHERE CURRENT OF в операторах UPDATE и DELETE.

Имя курсора можно использовать в качестве ссылки на курсор, как на переменные типа запись.Текущая запись доступна через имя курсора, что делает необязательным предложение INTO в операторе FETCH.

Однонаправленные и прокручиваемые курсоры

Курсор может быть однонаправленными прокручиваемым.Необязательное предложение SCROLL делает курсор двунаправленным (прокручиваемым), предложение NO SCROLL — однонаправленным.По умолчанию курсоры являются однонаправленными.

Однонаправленные курсоры позволяют двигаться по набору данных только вперёд.Двунаправленные курсоры позволяют двигаться по набору данных не только вперёд, но и назад, а также на N позиций относительно текущего положения.

Warning

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

Особенности использования курсора

Предложение FOR UPDATE разрешено использовать в операторе SELECT, но оно не требуется для успешного выполнения позиционированного обновления или удаления;
Удостоверьтесь, что объявленные имена курсоров не совпадают, ни с какими именами, определёнными позже в предложениях AS CURSOR;
Если курсор требуется только для прохода по результирующему набору данных, то практически всегда проще (и менее подвержено ошибкам) использовать оператор FOR SELECT с предложением AS CURSOR. Объявленные курсоры должны быть явно открыты, использованы для выборки данных и закрыты. Кроме того, вы должны проверить контекстную переменную ROW_COUNT после каждой выборки и выйти из цикла, если её значение ноль. Предложение FOR SELECT делает эту проверку автоматически. Однако объявленные курсоры дают большие возможности для контроля над последовательными событиями и позволяют управлять несколькими курсорами параллельно;
Оператор SELECT может содержать параметры, например: "SELECT NAME || :SFX FROM NAMES WHERE NUMBER = :NUM". Каждый параметр должен быть заранее объявлен как переменная PSQL (это касается также входных и выходных параметров). При открытии курсора параметру присваивается текущее значение переменной;
Если опция прокрутки опущена, то по умолчанию принимается NO SCROLL (т.е курсор открыт для движения только вперёд). Это означает, что могут быть использованы только команды FETCH [NEXT FROM]. Другие команды будут возвращать ошибки.

Warning

Если значение переменной PSQL, используемой в операторе SELECT, изменяется во время выполнения цикла, то её новое значение может (но не всегда) использоваться при выборке следующих строк.Лучше избегать таких ситуаций.Если вам действительно требуется такое поведение, то необходимо тщательно протестировать код и убедиться, что вы точно знаете, как изменения переменной влияют на результаты выборки.Особо отмечу, что поведение может зависеть от плана запроса, в частности, от используемых индексов.В настоящее время нет строгих правил для таких ситуаций, но в новых версиях Firebird это может измениться.

Примеры использования именованного курсора

Example 1. Объявление именованного курсора

CREATE OR ALTER TRIGGER TBU_STOCK
BEFORE UPDATE ON STOCK
AS
  -- Объявление именованного курсора
  DECLARE C_COUNTRY CURSOR FOR (
    SELECT
      COUNTRY,
      CAPITAL
    FROM COUNTRY
  );
BEGIN
  /* Операторы PSQL */
END

Example 2. Объявление прокручиваемого курсора

EXECUTE BLOCK
RETURNS (
  N INT,
  RNAME CHAR(63))
AS
  -- Объявление прокручиваемого курсора
  DECLARE C SCROLL CURSOR FOR (
    SELECT
      ROW_NUMBER() OVER(ORDER BY RDB$RELATION_NAME) AS N,
      RDB$RELATION_NAME
    FROM RDB$RELATIONS
    ORDER BY RDB$RELATION_NAME);
BEGIN
  /* Операторы PSQL */
END

См. также:

OPEN,FETCH,CLOSE,FOR SELECT.

`DECLARE PROCEDURE`

Назначение

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

Доступно в

PSQL

Синтаксис

<subproc-declaration> ::=
  DECLARE PROCEDURE subprocname [(<input-parameters>)]
  [RETURNS (<output-parameters>)];

<subproc-implimentation> ::=
  DECLARE PROCEDURE subprocname [(<input-parameters>)]
  [RETURNS (<output-parameters>)]
  <psql-routine-body>

<input-parameters> ::= <inparam> [, <inparam> ...]

<output-parameters> ::= <outparam> [, <outparam> ...]

<psql-routine-body> ::=
  См. Синтаксис тела модуля

Table 1. Параметры оператора `DECLARE PROCEDURE`
Параметр	Описание
subprocname	Имя подпроцедуры.
inparam	Описание входного параметра.
outparam	Описание выходного параметра.

Оператор DECLARE PROCEDURE объявляет или реализует подпроцедуру.

На подпроцедуру накладываются следующие ограничения:

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

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

Example 1. Использование подпроцедуры

SET TERM ^;
--
-- Подпроцедуры в EXECUTE BLOCK
--
EXECUTE BLOCK
RETURNS (
    name VARCHAR(63))
AS
  -- Подпроцедура, возвращающая список таблиц
  DECLARE PROCEDURE get_tables
  RETURNS(table_name VARCHAR(63))
  AS
  BEGIN
    FOR
      SELECT
        rdb$relation_name
      FROM
        rdb$relations
      WHERE
        rdb$view_blr IS NULL
      INTO table_name
    DO SUSPEND;
  END

  -- Подпроцедура, возвращающая список представлений
  DECLARE PROCEDURE get_views
  RETURNS(view_name  VARCHAR(63))
  AS
  BEGIN
    FOR
      SELECT
        rdb$relation_name
      FROM
        rdb$relations
      WHERE
        rdb$view_blr IS NOT NULL
      INTO view_name
    DO SUSPEND;
  END

BEGIN
  FOR
    SELECT
        table_name
    FROM
        get_tables
    UNION ALL
    SELECT
        view_name
    FROM
        get_views
    INTO name
  DO SUSPEND;
END^

Example 2. Использование подпроцедур с предварительным объявлением

EXECUTE BLOCK RETURNS (o INTEGER)
AS
  -- Предварительное объявление P1.
  DECLARE PROCEDURE p1(i INTEGER = 1) RETURNS (o INTEGER);

  -- Предварительное объявление P2.
  DECLARE PROCEDURE p2(i INTEGER) RETURNS (o INTEGER);

  -- Реализация P1. Вы не должны переопределять значение параметра по умолчанию
  DECLARE PROCEDURE p1(i INTEGER) RETURNS (o INTEGER)
  AS
  BEGIN
    EXECUTE PROCEDURE p2(i) RETURNING_VALUES o;
  END

  DECLARE PROCEDURE p2(i INTEGER) RETURNS (o INTEGER)
  AS
  BEGIN
    o = i;
  END
BEGIN
  EXECUTE PROCEDURE p1 RETURNING_VALUES o;
  SUSPEND;
END!

См. также:

DECLARE FUNCTION,CREATE PROCEDURE.

DECLARE FUNCTION

Назначение

Объявление и реализация подфункции.

Доступно в

PSQL

Синтаксис

<subfunc-declaration> ::=
  DECLARE FUNCTION subfuncname [(<input-parameters>)]
  RETURNS <type> [COLLATE collation] [DETERMINISTIC];

<subfunc-implimentation> ::=
  DECLARE FUNCTION subfuncname [(<input-parameters>)]
  RETURNS <type> [COLLATE collation] [DETERMINISTIC]
  <psql-routine-body>

<input-parameters> ::= <inparam> [, <inparam> ...]

<output-parameters> ::= <outparam> [, <outparam> ...]

<psql-routine-body> ::=
  См. Синтаксис тела модуля

Table 1. Параметры оператора DECLARE FUNCTION
Параметр	Описание
subfuncname	Имя подфункции.
inparam	Описание входного параметра.
type	Тип выходного результата.
collation	Порядок сортировки.

Оператор DECLARE FUNCTION объявляет подфункцию.

На подфункцию накладываются следующие ограничения:

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

Example 1. Использование подфункции

--
-- Подфункция внутри хранимой функции
--
CREATE OR ALTER FUNCTION FUNC1 (n1 INTEGER, n2 INTEGER)
  RETURNS INTEGER
AS
  -- Подфункция
  DECLARE FUNCTION SUBFUNC (n1 INTEGER, n2 INTEGER)
    RETURNS INTEGER
  AS
  BEGIN
    RETURN n1 + n2;
  END

BEGIN
  RETURN SUBFUNC(n1, n2);
END ^

Example 2. Использование рекурсивной подфункции

EXECUTE BLOCK RETURNS (i INTEGER, o INTEGER)
AS
  -- Рекусривная подпрограмма-функция без предварительного объявления.
  DECLARE FUNCTION fibonacci(n INTEGER) RETURNS INTEGER
  AS
  BEGIN
    IF (n = 0 OR n = 1) THEN
      RETURN n;
    ELSE
      RETURN fibonacci(n - 1) + fibonacci(n - 2);
  END
BEGIN
  i = 0;

  WHILE (i < 10)
  DO
  BEGIN
    o = fibonacci(i);
    SUSPEND;
    i = i + 1;
  END
END!

См. также:

DECLARE PROCEDURE,CREATE FUNCTION.

`BEGIN … END`

Назначение

Обозначение составного оператора.

Доступно в

PSQL.

Синтаксис

<block> ::=
  BEGIN
    [<compound_statement> ...]
  END

<compound_statement> ::= {<block> | <statement>}

Операторные скобки BEGIN … END определяют составной оператор или блок операторов, который выполняется как одна единица кода.Каждый блок начинается оператором BEGIN и завершается оператором END.Блоки могут быть вложенными.Максимальная глубина ограничена 512 уровнями вложенности блоков.Составной оператор может быть пустым, что позволяет использовать его как заглушку, позволяющую избежать написания фиктивных операторов.

После операторов BEGIN и END точка с запятой не ставится.Однако утилита командной строки isql требует, чтобы после последнего оператора END в определении PSQL модуля следовал символ терминатора, установленного командой SET TERM.Терминатор не является частью синтаксиса PSQL.

Последний оператор END в триггере завершает работу триггера.Последний оператор END в хранимой процедуре работает в зависимости от типа процедуры:

В селективной процедуре последний оператор END возвращает управление приложению и устанавливает значение SQLCODE равным 100, что означает, что больше нет строк для извлечения;
В выполняемой процедуре последний оператор END возвращает управление и текущие значения выходных параметров, если таковые имеются, вызывающему приложению.

Примеры `BEGIN … END`

Пример процедуры из базы данных employee.fdb, демонстрирующий простое использование блоков BEGIN … END:

Example 1. Использование BEGIN … END

SET TERM ^;
CREATE OR ALTER PROCEDURE DEPT_BUDGET (
    DNO CHAR(3))
RETURNS (
    TOT DECIMAL(12,2))
AS
    DECLARE VARIABLE SUMB DECIMAL(12,2);
    DECLARE VARIABLE RDNO CHAR(3);
    DECLARE VARIABLE CNT  INTEGER;
BEGIN
  TOT = 0;

  SELECT
      BUDGET
  FROM
      DEPARTMENT
  WHERE DEPT_NO = :DNO
  INTO :TOT;

  SELECT
      COUNT(BUDGET)
  FROM
      DEPARTMENT
  WHERE HEAD_DEPT = :DNO
  INTO :CNT;

  IF (CNT = 0) THEN
    SUSPEND;

  FOR
      SELECT
          DEPT_NO
      FROM
          DEPARTMENT
      WHERE HEAD_DEPT = :DNO
      INTO :RDNO
  DO
  BEGIN
    EXECUTE PROCEDURE DEPT_BUDGET(:RDNO)
    RETURNING_VALUES :SUMB;
    TOT = TOT + SUMB;
  END

  SUSPEND;
END^
SET TERM ;^

См. также:

EXIT, LEAVE, SET TERM.

`IF … THEN … ELSE`

Назначение

Условный переход.

Доступно в

PSQL

Синтаксис

IF (<condition>)
  THEN <compound_statement>
  [ELSE <compound_statement>]

Table 1. Параметры оператора `IF … THEN … ELSE`
Параметр	Описание
condition	Логическое условие возвращающее TRUE, FALSE или UNKNOWN.
compound_statement	Составной оператор (оператор или блок операторов).

Оператор условного перехода IF используется для выполнения ветвления процесса обработки данных в PSQL.Если условие возвращает значение TRUE, то выполняется составной оператор или после ключевого слова THEN.Иначе (если условие возвращает FALSE или UNKNOWN) выполняется составной оператор после ключевого слова ELSE, если оно присутствует.Условие всегда заключается в круглые скобки.

Оператор ветвления

PSQL не обеспечивает более сложных переходов с несколькими ветвями, таких как CASE или SWITCH.Однако можно объединить операторы IF … THEN … ELSE в цепочку, см. Раздел примеров ниже.В качестве альтернативы, оператор CASE из DSQL доступен в PSQL и может удовлетворить по крайней мере некоторые варианты использования в виде switch:

CASE <test_expr>
  WHEN <expr> THEN <result>
  [WHEN <expr> THEN <result> ...]
  [ELSE <defaultresult>]
END

CASE
  WHEN <bool_expr> THEN <result>
  [WHEN <bool_expr> THEN <result> ...]
  [ELSE <defaultresult>]
END

Example 1. Использования CASE в PSQL.

...
C = CASE
      WHEN A=2 THEN 1
      WHEN A=1 THEN 3
      ELSE 0
    END;
...

Примеры `IF`

Example 1. Использование оператора IF

Предположим, что переменные FIRST, LINE2 и LAST были объявлены ранее.

...
IF (FIRST IS NOT NULL) THEN
  LINE2 = FIRST || ' ' || LAST;
ELSE
  LINE2 = LAST;
...

Example 2. Объединение IF … THEN … ELSE в цепочку

Предположим, что переменные INT_VALUE и STRING_VALUE были объявлены ранее.

...
IF (INT_VALUE = 1) THEN
  STRING_VALUE = 'one';
ELSE IF (INT_VALUE = 2) THEN
  STRING_VALUE = 'two';
ELSE IF (INT_VALUE = 3) THEN
  STRING_VALUE = 'three';
ELSE
  STRING_VALUE = 'too much';
...

Этот пример можно заменить на функциюПростой CASE или DECODE.

См. также:

WHILE … DO, CASE.

`WHILE … DO`

Назначение

Циклическое выполнение операторов.

Доступно в

PSQL

Синтаксис

[label:]
WHILE (<condition>) DO
  <compound_statement>

Table 1. Параметры оператора WHILE … DO
Параметр	Описание
condition	Логическое условие возвращающее TRUE, FALSE или UNKNOWN.
compound_statement	Составной оператор (оператор или блок операторов).

Оператор WHILE используется для организации циклов в PSQL.Составной оператор будет выполняться до тех пор, пока условие истинно (возвращает TRUE). Циклы могут быть вложенными, глубина вложения не ограничена.

Примеры `WHILE … DO`

Example 1. Использование оператора WHILE … DO

Процедура расчёта суммы от 1 до I для демонстрации использования цикла:

CREATE PROCEDURE SUM_INT (I INTEGER)
RETURNS (S INTEGER)
AS
BEGIN
  s = 0;
  WHILE (i > 0) DO
  BEGIN
    s = s + i;
    i = i - 1;
  END
END

При выполнении в isql:

EXECUTE PROCEDURE SUM_INT(4);

результат будет следующий

S
==========
10

См. также:

FOR SELECT, FOR EXECUTE STATEMENT, LEAVE, CONTINUE.

`BREAK`

Назначение

Выход из цикла.

Синтаксис

<loop_stmt>
BEGIN
  ...
  BREAK;
  ...
END

<loop_stmt> ::=
    FOR <select_stmt> INTO <var_list>  DO
  | FOR EXECUTE STATEMENT ... INTO <var_list> DO
  | WHILE (<condition>) DO

Table 1. Параметры оператора `BREAK`
Параметр	Описание
select_stmt	Оператор `SELECT`
condition	Логическое условие возвращающее TRUE, FALSE или UNKNOWN.

Оператор BREAK моментально прекращает работу внутреннего цикла операторов WHILE или FOR.Код продолжает выполняться с первого оператора после завершенного блока цикла.

Оператор BREAK похож на LEAVE, за исключением того, что не поддерживает метку перехода.

Note	Этот оператор считается устаревшим.Начиная с Firebird 1.5 рекомендуется использовать SQL-99 совместимый оператор LEAVE.

См. также:

LEAVE, EXIT, CONTINUE.

Операторы Процедурного SQL (PSQL)

Операторы процедурного SQL (PSQL)

Элементы PSQL

DML операторы с параметрами

Транзакции

Структура модуля

Заголовок модуля

Привилегии выполнения PSQL кода

Тело модуля

Тело PSQL модуля

isql команда SET TERM

Тело внешнего модуля

Хранимые процедуры

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

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

Выполняемые хранимые процедуры

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

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

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

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

Хранимые функции

Создание хранимой функции

Изменение хранимой функции

Удаление хранимой функции

PSQL блоки

Пакеты

Преимущества пакетов

Создание пакета

Модификация пакета

Удаление пакета

Триггеры

Порядок срабатывания

DML триггеры

Варианты триггеров

Контекстные переменные NEW и OLD

Триггеры на события базы данных

DDL триггеры

Переменные доступные в пространстве имён DDL_TRIGGER

Создание триггера

Изменение триггера

Удаление триггера

Написание кода тела модуля

Оператор присваивания

LEAVE

Примеры LEAVE

CONTINUE

Примеры CONTINUE

EXIT

Примеры EXIT

SUSPEND

Примеры SUSPEND

EXECUTE STATEMENT

Параметризованные операторы

Особенности использования параметризованных операторов

Примеры EXECUTE STATEMENT с параметрами

WITH {AUTONOMOUS | COMMON} TRANSACTION

WITH CALLER PRIVILEGES

ON EXTERNAL [DATA SOURCE]

Пул внешних подключений (External connection pool)

Особенности внешних подключений

Особенности пула транзакций (Transaction pooling)

Особенности обработки исключений

Другие замечания

AS USER, PASSWORD и ROLE

Предостережения

FOR SELECT

Необъявленный курсор

Примеры использования FOR SELECT

FOR EXECUTE STATEMENT

Примеры `FOR EXECUTE STATEMENT

OPEN

Примеры OPEN

FETCH

Примеры FETCH

CLOSE

Примеры CLOSE

DECLARE VARIABLE

Типы данных для переменных

Ограничение NOT NULL

Предложения CHARACTER SET и COLLATE

isql команда `SET TERM`

`LEAVE`

Примеры `LEAVE`

`CONTINUE`

Примеры `CONTINUE`

`EXIT`

Примеры `EXIT`

`SUSPEND`

Примеры `SUSPEND`

`EXECUTE STATEMENT`

Примеры `EXECUTE STATEMENT` с параметрами

`WITH {AUTONOMOUS | COMMON} TRANSACTION`

`WITH CALLER PRIVILEGES`

`ON EXTERNAL [DATA SOURCE]`

`FOR SELECT`

Примеры использования `FOR SELECT`

`FOR EXECUTE STATEMENT`

`OPEN`

Примеры `OPEN`

`FETCH`

Примеры `FETCH`

`CLOSE`

Примеры `CLOSE`

`DECLARE VARIABLE`

Ограничение `NOT NULL`

Предложения `CHARACTER SET` и `COLLATE`

`IN AUTONOMOUS TRANSACTION`

Примеры `IN AUTONOMOUS TRANSACTION`

`POST_EVENT`

Примеры `POST_EVENT`

`RETURN`

Примеры `RETURN`

`EXCEPTION`

Примеры `EXCEPTION`

`WHEN … DO`

Область действия оператора `WHEN … DO`

Примеры использования `WHEN…DO`

`DECLARE … CURSOR`

`DECLARE PROCEDURE`

`BEGIN … END`

Примеры `BEGIN … END`

`IF … THEN … ELSE`

Примеры `IF`

`WHILE … DO`

Примеры `WHILE … DO`

`BREAK`