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

`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см. Создание БД на удалённом сервере.

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

docnext count = 50

Пул внешних подключений (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.

WITH CALLER PRIVILEGES

WITH CALLER PRIVILEGES

ON EXTERNAL [DATA SOURCE]

docnext count = 50

Пул внешних подключений (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

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

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

IN AUTONOMOUS TRANSACTION

Примеры IN AUTONOMOUS TRANSACTION

POST_EVENT

Примеры POST_EVENT

RETURN

Примеры RETURN

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

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

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

EXCEPTION

Примеры EXCEPTION

WHEN …​ DO

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

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

DECLARE …​ CURSOR

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

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

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

DECLARE PROCEDURE

DECLARE FUNCTION

BEGIN …​ END

Примеры BEGIN …​ END

IF …​ THEN …​ ELSE

Примеры IF

WHILE …​ DO

Примеры WHILE …​ DO

BREAK

`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`