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

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

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

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

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

docnext count = 83

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

  1. Оператор SUSPEND может встречаться только в хранимых процедурах или подпроцедурах, а также в анонимных блоках EXECUTE BLOCK.

  2. Наличие ключевого слова SUSPEND определяет хранимую процедуру как выбираемую (selectable) процедуру.

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

  4. Если выбираемая (selectable) процедура выполняется с использованием EXECUTE PROCEDURE, она ведет себя как исполняемая процедура. Когда в такой хранимой процедуре выполняется инструкция SUSPEND, это то же самое, что выполнение инструкции EXIT, что приводит к немедленному завершению процедуры.

  5. Оператор 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 операторе можно использовать параметры.Параметры могут быть именованными и позиционными (безымянные). Значение должно быть присвоено каждому параметру.

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

  1. Одновременное использование именованных и позиционных параметров в одном запросе запрещено;

  2. Если у оператора есть параметры, они должны быть помещены в круглые скобки при вызове EXECUTE STATEMENT, независимо от вида их представления: непосредственно в виде строки, как имя переменной или как выражение;

  3. Именованным параметрам должно предшествовать двоеточие (‘:’) в самом операторе, но не при присвоении значения параметру;

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

  5. Присвоение значений параметров должно осуществляться при помощи специального оператора “:=”, аналогичного оператору присваивания языка Pascal;

  6. Каждый именованный параметр может использоваться в операторе несколько раз, но только один раз при присвоении значения;

  7. Для позиционных параметров число подставляемых значений должно точно равняться числу параметров (вопросительных знаков) в операторе;

  8. Необязательное ключевое слово 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

Текущее количество активных соединений в пуле.
Особенности внешних подключений

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

  2. Внешние соединения, созданные с использованием предложения WITH AUTONOMOUS TRANSACTION, закрываются после выполнения оператора или попадают в список неактивных соединений пула (если он включен);

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

  4. Если локальная транзакция запущена в режиме изолированности READ COMMITTED READ CONSISTENCY и внешний источник данных не поддерживает данный режим изолированности, то внешняя транзакция будет запущена в режиме изолированности SNAPSHOT (CONCURRENCY).

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

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

  2. При использовании предложения 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.Сравнение при авторизации сделано чувствительным к регистру: в большинстве случаев это означает, что имена пользователя и роли должны быть написаны в верхнем регистре.

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

  1. Не существует способа проверить синтаксис выполняемого SQL оператора;

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

  3. Выполнение оператора с помощью оператора EXECUTE STATEMENT значительно медленнее, чем при непосредственном выполнении;

  4. Возвращаемые значения строго проверяются на тип данных во избежание непредсказуемых исключений преобразования типа. Например, строка '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 переменные.

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

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

  • Для разрешения неоднозначности при доступе к переменной курсора перед именем курсора необходим префикс двоеточие;

  • К переменной курсора можно получить доступ без префикса двоеточия, но в этом случае, в зависимости от области видимости контекстов, существующих в запросе, имя может разрешиться как контекст запроса вместо курсора;

  • Переменные курсора доступны только для чтения;

  • Чтение из переменной курсора возвращает текущие значения полей. Это означает, что оператор 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 исключения имеют заранее определённые числовые (символьные) значение для контекстных переменных и связанные с ними тексты сообщений.Сообщения об ошибке написаны по умолчанию на английском языке.Существуют и локализованные сборки СУБД, в которых сообщения об ошибках переведены на другие языки.

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

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

Пользовательские исключения могут быть объявлены в базе данных как постоянные объекты и вызваны из 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.