18. Процедурный язык PSQL

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

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

18.1. Элементы PSQL

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

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

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

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

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

Транзакции

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

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

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

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

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

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

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

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

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

  • С привилегиями вызывающего пользователя (привилегии CURRENT_USER);

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

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

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

Тело модуля

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

<определение тела модуля> ::=
  <определение модуля>
| <определение внешнего модуля>

<определение модуля> ::=
[<привилегии выполнения>] <тело модуля>

<привилегии выполнения> ::=
SQL SECURITY {DEFINER | INVOKER}

<тело модуля> ::=
AS
        [<объявление>]
BEGIN
        [<блок операторов>]
END

<объявление> ::=
<объявление элемента> [<объявление элемента> ...]

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

<объявление подпрограммы> ::= <объявление подфункции> | <объявление подпроцедуры>

<определение внешнего модуля> ::=
EXTERNAL NAME <внешний модуль> ENGINE <имя движка>
[AS <тело внешнего модуля>]

<реализация подпрограммы> ::= <реализация подфункции> | <реализация подпроцедуры>

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

Параметр

Описание

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

Тело внешнего модуля. Строковый литерал который может использоваться UDR для различных целей.

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

Имя внешнего модуля, в котором расположена функция.

Имя функции в модуле

Внутреннее имя функции внутри внешнего модуля.

Информация

Определяемая пользователем информация для передачи в функцию внешнего модуля.

Имя движка

Имя движка для использования внешних функций. Обычно указывается имя UDR.

Тело PSQL модуля

Тело PSQL начинается с необязательного раздела, в котором объявляются переменные, курсоры и подпрограммы. Далее следует блок операторов, которые выполняются в логической последовательности как программа. Блок операторов - или составной оператор - заключен в ключевые слова BEGIN и END и выполняется как единый блок кода. Основной блок BEGIN ... END может содержать любое количество других блоков BEGIN ... END, как встроенных, так и последовательных. Максимальная вложенность блоков составляет 512 уровней.

Оператор SET TERM

Символом конца строки (завершения команды) по умолчанию является точка с запятой. Этот символ можно изменить командой:

SET TERM <строка>;

где <строка> может быть как одним символом, так и группой символов.

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

Например, при создании некоторого триггера, хранимой функции или хранимой процедуры следует выполнить следующие операторы:

SET TERM ^;
/* Формирование значение первичного ключа таблицы STAFF */
CREATE TRIGGER TBI_STAFF FOR STAFF
   BEFORE INSERT
AS
BEGIN
   ...           /*Текст триггера*/
END ^
SET TERM ;^

Здесь вначале в качестве терминатора выбирается символ ^ (это наиболее часто выбираемый символ, поскольку он довольно редко присутствует в программных текстах; в SQL он используется в качестве символа отрицания, который можно представить и восклицательным знаком). После операторов создания триггеров, функций и хранимых процедур терминатор возвращается в исходное состояние.

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

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

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

18.2. PSQL блоки

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

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

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

Синтаксис блока PSQL:

EXECUTE BLOCK
        [(<входной параметр> = ? [, <входной параметр> = ? ...])]
        [RETURNS (<выходной параметр> [, <выходной параметр> ...])]
        <определение тела модуля>

Подробное описание EXECUTE BLOCK см. в листинге .

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

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

Маркер двоеточия

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

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

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

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

Синтаксис операции присваивания значения внутренней переменной имеет следующий вид:

<имя переменной> = <выражение>;

  • Имя переменной - Имя локальной переменной или параметра процедуры (функции).

  • Выражение - Выражение, константа или переменная совместимая по типу данных.

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

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

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. Синтаксис оператора представлен в листинге .

Листинг 18.1 Синтаксис оператора объявления локальной переменной и курсора DECLARE VARIABLE

DECLARE [VARIABLE] {
   <имя локальной переменной> <тип>
   [NOT NULL]
   [COLLATE <порядок сортировки>]
   [{ = | DEFAULT } <значение по умолчанию>]
}

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

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

Оператор 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, литерал и любая контекстная переменная совместимая по типу данных.

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

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

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

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

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

Для описания курсора также используется оператор DECLARE VARIABLE. Синтаксис оператора:

Листинг 18.2 Синтаксис оператора объявления локальной переменной и курсора DECLARE VARIABLE

DECLARE [VARIABLE] {
     <имя курсора> [SCROLL | NO SCROLL] CURSOR FOR (<оператор SELECT>)
}

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

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

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

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

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

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

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

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

Предложение 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]. Другие команды будут возвращать ошибки.

Примечание

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

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

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

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

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

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

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

DECLARE VARIABLE NEW_CURSOR
  CURSOR FOR (SELECT
              CODCOUNTRY, CODREGION, NAMEREG, CENTER
              FROM VIEW_RUSSIA2);
BEGIN
  OPEN NEW_CURSOR;
  WHILE (1 = 1) DO
  BEGIN
     FETCH NEW_CURSOR
         INTO :CODCOUNTRY, :CODREGION, :NAMEREG, :CENTER;
     IF (ROW_COUNT = 0) THEN
         LEAVE;
     SUSPEND;
  END
  CLOSE NEW_CURSOR;
END ^

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

Синтаксис объявления подпроцедуры:

<объявление подпроцедуры> ::=
 DECLARE PROCEDURE <имя подпроцедуры> [(<входной параметр> [, <входной> ...])]
 [RETURNS (<выходной параметр> [, <выходной параметр> ...])];

<реализация подпроцедуры> ::=
 DECLARE PROCEDURE <имя подпроцедуры> [(<входной> [, <входной> ...])]
 [RETURNS (<выходной параметр> [, <выходной параметр> ...])]
 <тело модуля>

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

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

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

  • Переменные из основного модуля доступны внутри подпрограммы;

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

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

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

Пример использования подпроцедуры:

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^

Пример использования подпроцедур с предварительным объявлением:

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 <имя подфункции> [(<входной> [, <входной> ...])]
 RETURNS <тип> [COLLATE <сортировка>] [DETERMINISTIC];

<реализация подфункции> ::=
 DECLARE FUNCTION <имя подфункции> [(<входной> [, <входной> ...])]
 RETURNS <тип> [COLLATE <сортировка>] [DETERMINISTIC];
 <тело модуля>

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

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

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

  • Переменные из основного модуля доступны внутри подпрограммы;

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

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

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

Определение составного оператора

Синтаксис составного оператора:

<блок> ::=
BEGIN
        [<составной оператор> ...]
END
<составной оператор> ::= {<блок> | <оператор>}

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

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

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

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

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

Для выполнения ветвления процесса обработки данных в PSQL используется оператор IF-THEN-ELSE. Его синтаксис представлен в листинге .

Оператор IF-THEN-ELSE

Оператор IF-THEN-ELSE выполняет условный переход.

Листинг 18.3 Синтаксис оператора IF-THEN-ELSE

IF (<условие>)
THEN <составной оператор>
[ELSE <составной оператор>];

  • Условие - Логическое условие возвращающее TRUE, FALSE или UNKNOWN.

  • Составной оператор - Оператор или блок операторов.

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

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

CASE <условие>
        WHEN <выражение> THEN <результат>
        [WHEN <выражение> THEN <результат> ...]
        [ELSE <результат>]
END

CASE
        WHEN <логическое выражение> THEN <результат>
        [WHEN <логическое выражение> THEN <результат> ...]
        [ELSE <результат>]
END

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

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

IF (INPUT_PASSWORD <> STORED_PASSWORD)
THEN EXCEPTION WRONG_PASSWORD;

Оператор WHILE-DO

Оператор WHILE-DO позволяет организовать в PSQL цикл. Синтаксис оператора представлен в листинге .

Листинг 18.4 Синтаксис оператора WHILE-DO

[<метка>:]
WHILE (<условие>) DO
   <составной оператор>

  • Условие - Логическое условие возвращающее TRUE, FALSE или UNKNOWN.

  • Составной оператор - Оператор или блок операторов.

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

В следующем фрагменте хранимой процедуры осуществляется расчет чисел Фибоначчи. Первые два числа (в приведенном фрагменте PREV_ITEM и NEXT_ITEM) имеют значение, соответственно, 1 и 2. Каждое следующее число в последовательности является суммой двух предыдущих. Вызвавшей программе в качестве выходного параметра RESULT возвращается последнее полученное число. Количество итераций задается входным целочисленным параметром LAST_NUM.

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

BREAK

Оператор BREAK осуществляет выход из цикла. Синтаксис оператора:

Листинг 18.5 Синтаксис оператора BREAK

<оператор цикла>
BEGIN
     ...
     BREAK;
     ...
     END

<оператор цикла> ::=
    FOR <оператор SELECT> INTO <список переменных> DO
  | FOR EXECUTE STATEMENT ... INTO <список переменных> DO
  | WHILE (<логическое условие>) DO

  • Логическое условие - Логическое условие возвращающее TRUE, FALSE или UNKNOWN.

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

LEAVE

Этот оператор осуществляет выход из цикла. Синтаксис оператора представлен в листинге .

Листинг 18.6 Синтаксис оператора LEAVE

[<метка>:]
<оператор цикла>
BEGIN
     ...
     LEAVE [метка];
     ...
     END

<оператор цикла> ::=
    FOR <оператор SELECT> INTO <список переменных> DO
  | FOR EXECUTE STATEMENT ... INTO <список переменных> DO
  | WHILE (<логическое условие>) DO

  • Логическое условие - Логическое условие возвращающее TRUE, FALSE или UNKNOWN.

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

Если в операторе не указана метка, то оператор просто осуществляет выход из текущего цикла WHILE. Пример расчета чисел Фибоначчи можно изменить, используя оператор LEAVE:

...
DECLARE VARIABLE I INTEGER;            -- Параметр цикла
DECLARE VARIABLE PREV_ITEM BIGINT;     -- Предыдущий элемент
DECLARE VARIABLE NEXT_ITEM BIGINT;     -- Следующий элемент
DECLARE VARIABLE INTERMEDIATE BIGINT;  -- Временный элемент
...
PREV_ITEM = 1;
NEXT_ITEM = 2;
I = 2;
INTERMEDIATE = NEXT_ITEM;
WHILE (1 = 1) DO
BEGIN
   IF (I > LAST_NUM) THEN
      LEAVE;
   INTERMEDIATE = NEXT_ITEM;
   NEXT_ITEM = PREV_ITEM + NEXT_ITEM;
   PREV_ITEM = INTERMEDIATE;
   I = I + 1;
END
RESULT = NEXT_ITEM;
...

Здесь в операторе WHILE-DO задается бесконечный цикл, поскольку условие выхода из цикла всегда истинно. Фактический выход из цикла осуществляется после соответствующей проверки условия в операторе IF с использованием оператора LEAVE.

CONTINUE

Оператор CONTINUE досрочно начинает новую итерацию цикла.

Синтаксис оператора:

Листинг 18.7 Синтаксис оператора CONTINUE

[<метка>:]
<оператор цикла>
BEGIN
     ...
     CONTINUE [метка];
     ...
END

<оператор цикла> ::=
    FOR <оператор SELECT> INTO <список переменных> DO
  | FOR EXECUTE STATEMENT ... INTO <список переменных> DO
  | WHILE (<логическое условие>) DO

  • Логическое условие - Логическое условие возвращающее TRUE, FALSE или UNKNOWN.

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

Использование оператора CONTINUE:

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

EXIT

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

Листинг 18.8 Синтаксис оператора EXIT

EXIT;

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

SUSPEND

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

Листинг 18.9 Синтаксис оператора SUSPEND

SUSPEND;

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

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

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

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

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

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

EXECUTE STATEMENT

Оператор EXECUTE STATEMENT предназначен для выполнения динамически созданных SQL операторов. Синтаксис оператора представлен в листинге .

Листинг 18.10 Синтаксис оператора EXECUTE STATEMENT

 <execute_statement> ::=
     EXECUTE STATEMENT <аргумент>
             [<опция> ...]
             [INTO [:] <внутренняя переменная> [, [:] <внутренняя переменная>...] ]

     <аргумент> ::=
       <непараметризованный SQL запрос>
     | (<параметризованный SQL запрос>) (<параметры>)

     <параметры> ::= <именованные параметры> | <позиционные параметры>

     <именованные параметры> ::=
     [EXCESS] <имя параметра>:=<выражение>
     [, [EXCESS] <имя параметра>:=<выражение> ...]

     <позиционные параметры> ::= <выражение> [, <выражение> ...]

     <опция> ::=
       WITH {AUTONOMOUS | COMMON} TRANSACTION
     | WITH CALLER PRIVILEGES
     | AS USER <имя пользователя>
     | PASSWORD <пароль>
     | ROLE <роль>
     | ON EXTERNAL [DATA SOURCE] <строка подключения>

  • Непараметризованный SQL запрос - Строковый литерал или переменная, содержащая непараметризованный SQL запрос.

  • Параметризованный SQL запрос - Строковый литерал или переменная, содержащая параметризованный SQL запрос.

  • Имя параметра - Имя параметра SQL запроса.

  • Выражение - Выражение для получения значения параметра запроса.

  • Имя пользователя - Имя пользователя. Может быть строкой, CURRENT_USER или переменной.

  • Пароль - Пароль. Может быть строкой или переменной.

  • Роль - Роль. Может быть строкой, CURRENT_ROLE или переменной.

  • Строка подключения - Строка соединения с удалённой БД Может быть строкой или переменной.

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

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

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

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

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

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

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

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

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

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

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

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

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

AS USER, PASSWORD и ROLE

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

При использовании предложения ON EXTERNAL открывается новое соединение и:

  • Если присутствует, по крайней мере, один из параметров AS USER, PASSWORD и ROLE, то будет предпринята попытка нативной аутентификации с указанными значениями параметров (в зависимости от строки соединения - локально или удалённо). Для недостающих параметров не используются никаких значений по умолчанию;

  • Если все три параметра отсутствуют, и строка подключения не содержит имени сервера (или IP адреса), то новое соединение устанавливается к локальному серверу с пользователем и ролью текущего соединения. Термин локальный означает компьютер, где установлен сервер. Это совсем не обязательно компьютер клиента;

  • Если все три параметра отсутствуют, но строка подключения содержит имя сервера (или IP адреса), то будет предпринята попытка доверенной (trusted) авторизации к удалённому серверу. Если авторизация прошла, то удалённая операционная система назначит пользователю имя - обычно это учётная запись, под которой работает сервер РЕД Базы Данных.

Если предложение ON EXTERNAL отсутствует:

  • Если присутствует, по крайней мере, один из параметров AS USER, PASSWORD и ROLE, то будет открыто соединение к текущей базе данных с указанными значениями параметров. Для недостающих параметров не используются никаких значений по умолчанию;

  • Если все три параметра отсутствуют, то оператор выполняется в текущем соединении.

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

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

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

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

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

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

  4. Возвращаемые значения строго проверяются на тип данных во избежание непредсказуемых исключений преобразования типа. Например, строка '1234' преобразуется в целое число 1234, а строка 'abc' вызовет ошибку преобразования. В целом эта функция должна использоваться очень осторожно, а вышеупомянутые факторы всегда должны приниматься во внимание. Если такого же результата можно достичь с использованием PSQL и/или DSQL, то это всегда предпочтительнее.

Пул внешних подключений

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

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

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

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

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

  • если пул достиг максимального размера, то самое старое бездействующее соединение закрывается;

  • когда РЕД База Данных просит создать новое внешнее соединение, то пул сначала ищет кандидата в списке простаивающих соединений. Поиск основан на 4 параметрах:

    • строка подключения;

    • имя пользователя;

    • пароль;

    • роль.

    Поиск чувствителен к регистру;

  • если подходящее соединение найдено, то проверятся живое ли оно;

  • если соединение не прошло проверку, то оно удаляется и поиск повторяется (ошибка не возвращается пользователю);

  • найденное (и живое) соединение перемещается из списка простаивающих соединение в список активных соединений и возвращается вызывающему;

  • если имеется несколько подходящих соединений, то будет выбрано наиболее часто используемое;

  • если нет подходящего соединения, то создаётся новое и помещается в список активных соединений;

  • когда время жизни простаивающего соединения истекло, то оно удаляется из пула и закрывается.

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

  • отсутствие "вечных" внешних соединений;

  • ограниченное количество неактивных (простаивающих) внешних соединений в пуле;

  • поддерживает быстрый поиск среди соединений (по 4 параметрам указанным выше);

  • пул является общим для всех внешних баз данных;

  • пул является общим для всех локальных соединений, обрабатываемых данным процессом РЕД Базы Данных.

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

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

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

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

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

Таблица 18.2 Переменные пространства имён 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. В этом случае использованное соединение остаётся открытым и после выполнения оператора, т.к. у этого соединения есть, по крайней мере, одна не закрытая транзакция. Если включен пул внешних соединений, то вместо закрытия соединения, такие соединения будут попадать в список неактивных (простаивающих) соединений.

  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.

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

  1. Набор символов, используемый для внешнего соединения, совпадает с используемым набором для текущего соединения.

  2. Двухфазные транзакции не поддерживаются.

FOR SELECT-DO

Оператор FOR SELECT-DO является оператором цикла, выбирающим строки из таблицы, представления, хранимой процедуры выбора. Синтаксис оператора представлен в листинге .

Листинг 18.11 Синтаксис оператора цикла FOR SELECT-DO

[<метка>:]
FOR
   <оператор SELECT>
       INTO [:]<имя переменной/параметра> [, [:]<имя переменной/параметра> ...]
   [AS CURSOR <имя курсора>]
DO <составной оператор>;

  • Метка - Необязательная метка для LEAVE и/или CONTINUE. Должна следовать правилам для идентикаторов.

  • Имя курсора - Имя курсора. Должно быть уникальным среди имён переменных и курсоров PSQL модуля.

  • Имя переменной - Имя локальной переменной или входного/выходного параметра.

  • Составной оператор - Составной оператор (оператор или блок операторов).

Оператор 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 в триггерах), что позволяет получить доступ к столбцам результирующего набора. Использование предложение AS CURSOR делает предложение INTO необязательным.

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

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

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

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

  • В операторе FOR SELECT без предложения AS CURSOR необходимо использовать предложение INTO. Если указано предложение AS CURSOR, предложение INTO не требуется, но разрешено;

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

  • Над курсором, объявленным с помощью предложения AS CURSOR нельзя выполнять операторы OPEN, FETCH и CLOSE;

  • Убедитесь, что имя курсора, определённое здесь, не совпадает ни с какими именами, созданными ранее оператором DECLARE VARIABLE;

  • Предложение FOR UPDATE, разрешённое для использования в операторе SELECT, не является обязательным для успешного выполнения позиционного обновления или удаления.

Пример

В главе 16 был показан пример создания представления VIEW_RUSSIA2, выбирающего все регионы страны Россия. Следующий пример показывает возможность использования этого представления в цикле FOR SELECT-DO.

FOR SELECT
   CODCOUNTRY, CODREGION, NAMEREG, CENTER
FROM VIEW_RUSSIA2
INTO
   :CODCOUNTRY, :CODREGION, :NAMEREG, :CENTER
DO
BEGIN
   EXECUTE PROCEDURE PROC_N (CODCOUNTRY, CODREGION, NAMEREG, CENTER)
   RETURNING_VALUES RESULT;
   SUSPEND;
END

Внутренние переменные CODCOUNTRY, CODREGION, NAMEREG и CENTER являются выходными параметрами этой хранимой процедуры.

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

FOR EXECUTE STATEMENT

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

Синтаксис оператора представлен в листинге .

Листинг 18.12 Синтаксис оператора цикла FOR EXECUTE STATEMENT

[метка:]
     FOR <определение EXECUTE STATEMENT> DO <составной оператор>

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

OPEN

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

Листинг 18.13 Синтаксис оператора открытия курсора OPEN

OPEN <имя курсора>;

Курсор с указанным именем должен быть предварительно объявлен с помощью оператора DECLARE CURSOR.

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

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

FETCH

Данные очередной строки таблицы (представления) при использовании курсора получаются при выполнении оператора FETCH для этого курсора. Синтаксис оператора FETCH представлен в листинге .

Листинг 18.14 Синтаксис оператора чтения очередной строки из курсора FETCH

FETCH [<продвижение курсора> FROM] <имя курсора>
   [INTO [:]<внутр. переменная> [, [:]<внутр. переменная> ...]];

<продвижение курсора> ::=
  NEXT
| PRIOR
| FIRST
| LAST
| RELATIVE n
| ABSOLUTE 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 в триггерах), что позволяет получить доступ к столбцам результирующего набора.

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

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

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

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

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

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

CLOSE

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

Листинг 18.15 Синтаксис оператора закрытия курсора CLOSE

CLOSE <имя курсора>;

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

IN AUTONOMOUS TRANSACTION

Оператор IN AUTONOMOUS TRANSACTION позволяет выполнить оператор или блок операторов в автономной транзакции. Синтаксис оператора представлен в листинге .

Листинг 18.16 Синтаксис оператора IN AUTONOMOUS TRANSACTION

IN AUTONOMOUS TRANSACTION DO <составной оператор>

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

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

Отправка событий базы данных

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

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

Для отправки сообщения (события) используется оператор POST_EVENT. Его синтаксис представлен в листинге .

Листинг 18.17 Синтаксис оператора передачи события POST_EVENT

POST_EVENT {<сообщение>};

Сообщение - это строка размером до 127 байт. Содержимое строки может быть строковым литералом, переменной или любым допустимым выражением SQL, которое возвращает строку.

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

Операторы в блоке выполняются последовательно. В PSQL существуют операторы ветвления (IF-THEN-ELSE), операторы цикла (WHILE-DO, FOR SELECT-DO, FOR EXECUTE STATEMENT), операторы обращения к данным в базе данных (операторы выборки данных, добавления, изменения, удаления).

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

В любом месте текста, где допустим пробел, могут быть помещены комментарии, которые располагаются между символами /* и */. Один такой комментарий может занимать любое количество строк. Существует и другая форма комментариев: подряд идущие два символа минус (--). Текст комментария в этом случае продолжается лишь до конца текущей строки.

RETURN

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

RETURN <значение>;

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

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

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

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

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

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

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

Листинг 18.18 Синтаксис оператора вызова пользовательского исключения EXCEPTION

EXCEPTION <имя пользовательского исключения> [<текст сообщения> | USING (<значение> [,<значение>...])];

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

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

  • Если \(N\)-ый параметр не передан, его слот не заменяется;

  • Если передано значение NULL, слот будет заменён на строку '***null***';

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

  • Максимальный номер параметра равен 9;

  • Общая длина сообщения, включая значения параметров, ограничена 1053 байтами.

Пример.

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

CREATE EXCEPTION NO_MOTHER
'В базе данных отсутствует запись, соответствующая матери человека по имени @1';
...
INSERT INTO PEOPLE (..., CODMOTHER, ...)
VALUES (..., 'DF', ...);
WHEN ANY
DO EXCEPTION NO_MOTHER USING(FULLNAME);

Здесь, если при добавлении новой записи человека в таблицу PEOPLE в базе данных в той же таблице PEOPLE будет отсутствовать строка, соответствующая матери вводимого человека, то программа (триггер, хранимая процедура или хранимая функция) в блоке обработки ошибок WHEN-DO выдаст пользовательское исключение с именем NO_MOTHER и текстом: 'В базе данных отсутствует запись, соответствующая матери человека'. Если в данной программе не задана обработка подобного исключения, то работа программы завершится. Новая запись не будет помещена в базу данных.

WHEN-DO

Для обработки ошибочных ситуаций базы данных и пользовательских исключений в языке хранимых процедур, функций и триггеров используется оператор WHEN-DO. Оператор позволяет перехватить любые указанные ошибки базы данных и/или пользовательские исключения (EXCEPTION) при обращении к базе данных. Синтаксис оператора представлен в листинге .

Листинг 18.19 Синтаксис оператора обработки ошибок базы данных или пользовательских исключений WHEN-DO

<блок операторов> ::=
 BEGIN
   [<составной оператор> ...]
   [<when_do> ...]
 END

<составной оператор> ::= {<блок операторов> | <оператор>}

<when_do>::=
 WHEN { <ошибка> [, <ошибка> ...] | ANY }
 DO <составной оператор>;

<ошибка> ::= {
   SQLCODE <код ошибки SQLCODE>
 | SQLSTATE <код ошибки SQLSTATE>
 | GDSCODE <название кода ошибки>
 | EXCEPTION <имя пользовательского исключения> }

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

В условии оператора до ключевого слова DO задается перечисление тех ситуаций, при которых будет выполняться составной оператор. Здесь можно через запятую перечислить произвольное количество значений кодов SQLCODE, GDSCODE, SQLSTATE, имен пользовательских исключений или задать ключевое слово ANY, которое означает, что обработка ошибочной ситуации будет выполняться при появлении любой ошибки базы данных и/или любого пользовательского исключения.

После ключевого слова DO помещается оператор или блок операторов, заключенных в операторные скобки BEGIN и END. В этом блоке выполняется обработка возникшей ситуации.

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

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

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

INSERT INTO COUNTRY (CODCOUNTRY) VALUES ('USA');
WHEN SQLCODE -803
DO ... ;

Однако подобное значение SQLCODE будет сформировано и при попытке добавления в базу данных новой строки, создающей дублирующее значение построенного пользователем индекса (см. 30). Чтобы более точно определить данную конкретную ошибочную ситуацию, следует использовать не код SQLCODE, а название кода GDSCODE.

INSERT INTO COUNTRY (CODCOUNTRY) VALUES ('USA');
WHEN GDSCODE unique_key_violation
DO ... ;

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

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

Для перехвата и обработки пользовательского исключения используется оператор WHEN-DO с ключевым словом EXCEPTION. В следующем примере показан вариант выдачи пользовательского исключения NO_MOTHER. В том же самом блоке операторов или в любом вышележащем можно перехватить и обработать это пользовательское исключение, задав оператор:

WHEN EXCEPTION NO_MOTHER
DO ... ;

Коды ошибок SQLCODE и GDSCODE детально описаны в 30.