Элементы языка PSQL

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

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

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

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

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

В синтаксисе PSQL можно выделить заголовок и тело. Заголовок содержит имя программного объекта, описание локальных переменных. Для триггеров в заголовке указывается событие базы данных и фаза, при которой автоматически вызывается триггер. В заголовке хранимой процедуры можно указать входные и выходные параметры. В заголовке хранимой функции можно указать входные параметры и тип выходного результата. Тело хранимой процедуры, функции или триггера представляет собой блок операторов, содержащий описание выполняемых программой действий. Блок операторов заключается в операторные скобки BEGIN и END. В самих программах возможно присутствие произвольного количества блоков, как последовательных, так и вложенных друг в друга.

Рассмотрим пример простейшей хранимой процедуры, возвращающей случайное число от 0 до 100.

CREATE OR ALTER PROCEDURE R RETURNS (I INT)
AS
BEGIN
   I = RAND() * 100;
   SUSPEND;
END;

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

Созданную хранимую процедуру можно использовать в запросах SELECT. Например, запрос:

SELECT * FROM R;

Может вернуть следующий результат:

I
23

Локальные переменные

Для описания одной локальной переменной используется оператор DECLARE VARIABLE. Синтаксис оператора представлен в листинге:

DECLARE [VARIABLE] <переменная> <тип данных SQL> [NOT NULL] [= <значение>]

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

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

Типом данных может быть любой тип данных, используемый в SQL.

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

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

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

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

CREATE OR ALTER PROCEDURE R RETURNS (I INT)
AS
DECLARE N INT = 100;
BEGIN
   I = RAND() * N;
   SUSPEND;
END;

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

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

<имя параметра> <тип данных SQL> [NOT NULL] [= <значение по умолчанию>]

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

Выходные параметры хранимой процедуры описываются в предложении RETURNS:

RETURNS (<параметр> [, <параметр> ...])

Локальные переменные триггеров, функций и процедур, входные и выходные параметры, используемые только в хранимых процедурах и функциях, являются внутренними переменными. Имена внутренних переменных могут совпадать с именами столбцов используемых таблиц базы данных. Это не вызовет проблемы двусмысленности имен. При использовании внутренних переменных в операторах SELECT, INSERT, UPDATE или DELETE именам внутренних переменных всегда должно предшествовать двоеточие, чтобы не спутать их с именами столбцов таблицы. Во всех остальных случаях в любых других операторах имена внутренних переменных записываются обычным образом без двоеточия.

Например, изменим наш пример таким образом, чтобы указывать верхний диапазон значений.

CREATE OR ALTER PROCEDURE R(N INT = 100) RETURNS (I INT)
AS
BEGIN
   I = RAND() * N;
   SUSPEND;
END;

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

SELECT * FROM R 
   UNION ALL 
SELECT * FROM R(1000);

Такой запрос может вернуть

I
80
993

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

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

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

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

Арифметическое выражение содержит четыре арифметические операции — сложение (+), вычитание (-), умножение (*) и деление (/).

Строковое выражение представлено одной строковой операцией конкатенации (||) — соединения двух строк в одну.

Логическое выражение может содержать операцию отрицания (NOT), дизъюнкции (OR) и конъюнкции (AND).

Конструкция NEXT VALUE FOR <имя генератора> является аналогом функции GEN_ID (<имя генератора>, 1). Значение указанного генератора увеличивается на единицу, и конструкция возвращает новое значение.

В качестве выражения может быть также указано пустое значение NULL.

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

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

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

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

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

Составной оператор — это одиночный оператор или блок операторов, заключенных в операторные скобки BEGIN и END.

Например, пусть процедура возвращает значения ‘Четное’ или ‘Нечетное’, для сгенерированного случайного числа.

CREATE OR ALTER PROCEDURE R RETURNS (I INT, S VARCHAR(16))
AS
BEGIN
   I = RAND() * 100;
   IF (MOD(I, 2) = 0)
   	THEN
   		S = 'Четное';
   	ELSE
   		S = 'Нечетное';
   SUSPEND;
END;

SELECT * FROM R;

Такой запрос может вернуть

IS
80Четное

Оператор цикла WHILE

Оператор WHILE позволяет организовать в PSQL обычный цикл с условием.

WHILE (<условие>) DO
   <составной оператор>

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

Циклы могут быть вложенными, глубина вложения не ограничена.

Изменим наш пример таким образом, чтобы он генерировал заданное количество случайных чисел.

CREATE OR ALTER PROCEDURE R(N INT) RETURNS (I INT)
AS
BEGIN
    WHILE (N > 0) DO
    BEGIN
        N = N - 1;
        I = RAND() * 100;
        SUSPEND;
    END
END;

SELECT * FROM R(5);

Такой запрос может вернуть

I
79
36
45
3
47

Операторы перехода

В PSQL не существует оператора GOTO, выполняющего переход на указанную метку в программном тексте, что соответствует правилам структурного программирования. При этом есть операторы, позволяющие выйти из циклов или перейти на начало этого же или другого цикла (LEAVE), перейти на финальный оператор END (EXIT), временно приостановить выполнение хранимой процедуры для передачи вызвавшей стороне полученных данных (SUSPEND) и досрочно начать новую итерацию цикла (CONTINUE).

Оператор EXIT

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

EXIT;

Изменим наш пример, чтобы он использовал оператор EXIT.

CREATE OR ALTER PROCEDURE R(N INT) RETURNS (I INT)
AS
BEGIN
    WHILE (TRUE) DO
    BEGIN
        N = N - 1;
        I = RAND() * 100;
        SUSPEND;
        IF (N = 0)
           THEN
               EXIT;
    END
END;

Оператор LEAVE

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

LEAVE [<метка>];

Изменим наш пример, чтобы он использовал оператор EXIT.

CREATE OR ALTER PROCEDURE R(N INT) RETURNS (I INT)
AS
BEGIN
   LOOP:
   WHILE (TRUE) DO
   BEGIN
      N = N - 1;
      I = RAND() * 100;
      SUSPEND;
      IF (N = 0)
         THEN
            LEAVE LOOP;
   END
END;

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

Оператор BREAK

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

 BREAK;

Оператор BREAK похож на LEAVE, но не поддерживает работу с метками.

Изменим наш пример, чтобы он использовал оператор BREAK.

CREATE OR ALTER PROCEDURE R(N INT) RETURNS (I INT)
AS
BEGIN
    WHILE (TRUE) DO
    BEGIN
        N = N - 1;
        I = RAND() * 100;
        SUSPEND;
        IF (N = 0)
           THEN
               BREAK;
    END
END;

Оператор SUSPEND

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

SUSPEND;

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

Все примеры выше использовали оператор SUSPEND.

Оператор CONTINUE

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

CONTINUE [<метка>];

Изменим наш пример таким образом, чтобы он генерировал случайные числа, но пропускал нечетные.

CREATE OR ALTER PROCEDURE R(N INT) RETURNS (I INT)
AS
BEGIN
	LOOP:
	WHILE (N > 0) DO
	BEGIN
		N = N - 1;
		I = RAND() * 100;
		IF (MOD(I, 2) <> 0)
			THEN 
				CONTINUE LOOP;
			ELSE
				SUSPEND;
	END
END;

SELECT * FROM R(10);

Такой запрос может вернуть

I
70
46
34
88
30

И обратите внимание, что количество строк результата меньше 10.

Оператор EXECUTE PROCEDURE

Триггеры, хранимые процедуры и функции могут вызывать хранимые процедуры с помощью оператора EXECUTE PROCEDURE. Оператор также может быть использован в подмножестве языка DML.

EXECUTE PROCEDURE <имя процедуры> [(<параметр> [, <параметр>] ...)];

Если процедура получает параметры, то список входных параметров в операторе EXECUTE PROCEDURE является обязательным. При этом требуется полное соответствие количества передаваемых процедуре параметров и их типов данных описанным в процедуре входным параметрам.

Обычные операторы обращения к базе данных

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

Например, чтобы выполнить удаление регионов страны, чей код задан переменной CODCOUNTRY (в примере имя этой переменной совпадает с именем столбца в таблице регионов REGION), в хранимой процедуре, функции или в триггере нужно выполнить следующий оператор DELETE:

DELETE FROM REGION
   WHERE CODCOUNTRY = :CODCOUNTRY;

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

UPDATE REGION
      SET CODCOUNTRY = :NEWCODCOUNTRY
   WHERE CODCOUNTRY = :CODCOUNTRY;

Следующий оператор INSERT добавляет в таблицу стран COUNTRY новую страну. Значения для столбцов новой записи выбираются из внутренних переменных:

INSERT INTO COUNTRY (CODCOUNTRY, NAME, FULLNAME, CAPITAL)
   VALUES (:CODCOUNTRY, :NAME, :FULLNAME, :CAPITAL);

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

Например, следующий оператор SELECT выбирает строку из таблицы стран COUNTRY с кодом страны, находящимся во внутренней переменной CODCOUNTRY, и помещает краткое название страны и полное название страны в две внутренние переменные NAME и FULLNAME, которые имеют те же имена, что и столбцы таблицы:

SELECT NAME, FULLNAME
   FROM COUNTRY
   WHERE CODCOUNTRY = :CODCOUNTRY
   INTO :NAME, :FULLNAME;

Оператор FOR SELECT

Оператор FOR SELECT является оператором цикла, выбирающим строки из запроса.

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

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

Оператор SELECT должен содержать предложение INTO, которое располагается в конце оператора. Цикл повторяется, пока не будут прочитаны все строки. После этого происходит выход из цикла. Цикл также может быть завершен и раньше при использовании оператора LEAVE или BREAK.

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

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

CREATE PROCEDURE EMP_DEL(EMP_NO SMALLINT)
AS
BEGIN
	FOR SELECT * FROM EMPLOYEE AS CURSOR C DO
      IF (C.EMP_NO = EMP_NO) THEN
         DELETE FROM EMPLOYEE WHERE CURRENT OF C;
END;

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

SELECT
    A, B, C,
    IIF (D >= 0, (-B - SQRT(D)) / DENOM, NULL) SOL_1,
    IIF (D > 0, (-B + SQRT(D)) / DENOM, NULL) SOL_2
FROM
    (SELECT A, B, C, B*B - 4*A*C AS D, 2*A AS DENOM FROM COEFFS)

Эту задачу также можно решить с помощью хранимой процедуры.

CREATE PROCEDURE SOLVE 
	RETURNS (A DOUBLE PRECISION, 
				B DOUBLE PRECISION, 
				C DOUBLE PRECISION, 
				X1 DOUBLE PRECISION, 
				X2 DOUBLE PRECISION)
AS
DECLARE D DOUBLE PRECISION;
BEGIN
	FOR SELECT A, B, C FROM COEFFS INTO :A, :B, :C DO
	BEGIN
		X1 = NULL;
		X2 = NULL;
		D = B*B - 4*A*C;
		IF ( D >= 0) THEN
			X1 = ( -B + SQRT(D) ) / ( 2*A );
		IF ( D > 0) THEN
			X2 = ( -B - SQRT(D) ) / ( 2*A );
		SUSPEND;
	END
END;

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

Оператор IN AUTONOMOUS TRANSACTION

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

IN AUTONOMOUS TRANSACTION DO 
   <оператор/блок операторов>

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

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

Примером может служить необходимость логирования каких-либо изменений, независимо от основной транзакции.

Допустим у нас есть таблица ACTIONS для хранения событий.

RECREATE TABLE ACTIONS (
	S VARCHAR(64)
)

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

CREATE PROCEDURE R RETURNS (I INT)
AS
BEGIN
	IN AUTONOMOUS TRANSACTION DO
		INSERT INTO ACTIONS VALUES ('CALL OF THE PROCEDURE R');
	I = RAND() * 100;
	SUSPEND;
END

Тогда после выполнения запроса

SELECT * FROM R

В таблице ACTIONS появится запись

S
CALL OF THE PROCEDURE R

Контекстная переменная ROW_COUNT

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

Контекстная переменная ROW_COUNT типа INTEGER может быть использована в триггерах и в хранимых процедурах. Она возвращает общее количество строк, которые были прочитаны, добавлены, изменены или удалены в процессе выполнения последнего оператора SQL. Чаще всего эта контекстная переменная используется после оператора SELECT или после оператора FETCH, читающего очередную запись из таблицы, заданной объявленным курсором. В этом случае она содержит количество считанных данным оператором строк. Обычно используется для определения завершения считывания данных из таблицы (представления), определенной объявленным внутренним курсором.

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

CREATE OR ALTER PROCEDURE NUM RETURNS (N INT, R INT)
AS
BEGIN
	N = 0;
	FOR SELECT * FROM EMPLOYEE AS CURSOR C DO
		N = N + 1;
	R = ROW_COUNT;
	SUSPEND;
END;

SELECT * FROM NUM;

Результатом такого запроса будет таблица.

NR
104104

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

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

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

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

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

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

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

Например, в БД employee есть процедура SHIP_ORDER, которая производит необходимые действия по изменению статуса заказа на доставлен (shipped). Однако, если заказ уже доставлен, то будет выброшено пользовательское исключение order_already_shipped.

Приведем фрагмент этого кода.

CREATE OR ALTER PROCEDURE SHIP_ORDER (PO_NUM CHAR(8)) 
AS
...
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;

   /* This purchase order has been already shipped. */
   IF (ord_stat = 'shipped') THEN
   BEGIN
      EXCEPTION order_already_shipped;
   END
   ...
END;

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

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

WHEN SQLCODE <код ошибки SQLCODE>
   DO <составной оператор>;

SQLCODE это устаревшая классификация ошибок. Одному коду могут соответствовать разные ошибки. В настоящее время этот способ не рекомендован к применению и оставлен для совместимости с существующими БД.

WHEN SQLSTATE <код ошибки SQLSTATE>
   DO <составной оператор>;

SQLSTATE это коды ошибок, определенные стандартом SQL. Он состоит из 5 символов: два первых символа класса ошибки и два символа подкласса. Например, класс 00 - это отсутствие ошибки, 01 - предупреждение, но не ошибка, 02 - отсутствие данных. Полный перечень классов и подклассов приведен в стандарте и в документации к СУБД.

WHEN GDSCODE <код ошибки GDSCODE>
   DO <составной оператор>;

GDSCODE это коды ошибок, определенные в РЕД Базе Данных и Firebird. Наиболее подробная классификация. Все ошибки описаны в документации.

WHEN EXCEPTION <имя пользовательского исключения>
   DO <составной оператор>;

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

WHEN ANY
   DO <составной оператор>;

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

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

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

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

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

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

INSERT INTO COUNTRY (CODCOUNTRY) VALUES ('USA');

WHEN SQLCODE -803 DO
BEGIN
... 
END;

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

INSERT INTO COUNTRY (CODCOUNTRY) VALUES ('USA');

WHEN GDSCODE -335544665 DO 
BEGIN
... 
END;

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

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

Использование курсоров

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

Курсор необходимо объявить в коде PSQL как локальную переменную. В процессе выполнения открыть этот курсор (оператор OPEN). После этого можно читать данные при использовании курсора (оператор FETCH). После считывания всех записей курсор нужно закрыть (оператор CLOSE).

Для объявления локальной переменной — курсора используется следующий вариант синтаксиса оператора DECLARE VARIABLE

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

Курсор может быть однонаправленным и прокручиваемым. Необязательное предложение SCROLL делает курсор двунаправленным (прокручиваемым), предложение NO SCROLL — однонаправленным. По умолчанию курсоры являются однонаправленными. Однонаправленные курсоры позволяют двигаться по набору данных только вперёд. Двунаправленные курсоры позволяют двигаться по набору данных не только вперёд, но и назад, а также на N позиций относительно текущего положения.

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

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

Этот оператор выполняет оператор SELECT, заданный при объявлении курсора. После выполнения этого оператора возможно получение данных из набора данных указанного курсора.

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

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

Оператор FETCH применим только к курсорам, объявленным в операторе DECLARE VARIABLE.

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

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

FETCH NEXT FROM <имя курсора> [INTO ...];
FETCH PRIOR FROM <имя курсора> [INTO ...];
FETCH FIRST FROM <имя курсора> [INTO ...];
FETCH LAST FROM <имя курсора> [INTO ...];
FETCH ABSOLUTE <n> FROM <имя курсора> [INTO ...];
FETCH RELATIVE <n> FROM <имя курсора> [INTO ...];

Предложение NEXT указывает, что указатель курсора должен продвинуться на 1 запись вперёд. Это предложение допустимо использовать как с прокручиваемыми, там и не прокручиваемыми курсорами. Остальные предложения допустимо использовать только с прокручиваемыми курсорами. Предложение PRIOR указывает, что указатель курсора должен продвинуться на 1 запись назад. Предложение FIRST позволяет переместить позицию курсора на первую запись, а предложение LAST – на последнюю. Предложение ABSOLUTE позволяет указать номер позиции, на которую будет установлен курсор. Номер позиции должен быть в диапазоне от 1 до максимального количества записей извлекаемых запросом курсора. Предложение RELATIVE позволяет указать, на какое количество записей относительно текущей позиции необходимо переместить указатель курсора. Если указано положительное число, то курсор перемещает вперёд на N позиций, если отрицательное, то назад.

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

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

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

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

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

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

Оператор CALL

Оператор CALL аналогичен EXECUTE PROCEDURE, но позволяет получать определенные выходные параметры или не получать их вовсе.

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

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

CALL <имя процедуры> ([<позиционные параметры>][<именованные параметры>])

В качестве примера рассмотрим процедуру вставки нового клиента (CUSTOMER)

CREATE PROCEDURE INSERT_CUSTOMER (LAST_NAME VARCHAR(30), FIRST_NAME VARCHAR(30))
   RETURNS (ID INTEGER, FULL_NAME VARCHAR(62))
AS
BEGIN
   INSERT INTO CUSTOMERS (LAST_NAME, FIRST_NAME)
      VALUES (:LAST_NAME, :FIRST_NAME)
      RETURNING ID, LAST_NAME || ', ' || FIRST_NAME
      INTO :ID, :FULL_NAME;
END

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

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

CALL INSERT_CUSTOMER('Иванов', 'Иван', ?)

Второй выходной параметр при этом не указан и не будет возвращен.

Если же нам необходимо получить только второй выходной параметр FULL_NAME, а первый ID при этом проигнорировать, то это можно сделать указав NULL вместо игнорируемого параметра.

CALL INSERT_CUSTOMER('Иванов', 'Иван', NULL, ?)

Того же эффекта можно добиться если использовать позиционные параметры.

CALL INSERT_CUSTOMER('Иванов', 'Иван', FULL_NAME => ?)

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

CALL INSERT_CUSTOMER(
   LAST_NAME => 'Иванов', 
   FIRST_NAME => 'Иван',
   FULL_NAME => ?,         -- выходной параметр
   ID => ?                 -- выходной параметр
)

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

Контрольные вопросы

  1. Чем отличается PSQL от обычных операторов DML?
  2. Какие объекты БД используют PSQL?
  3. Какие возможности поддерживает PSQL?
  4. Каким образом объявляются локальные переменные?
  5. Приведите пример оператора ветвления?
  6. Какие операторы используются для организации циклов?
  7. Какие операторы перехода доступны в PSQL?
  8. Можно ли досрочно выйти из вложенного цикла?
  9. Чем отличаются операторы LEAVE и BREAK?
  10. Как работает оператор SUSPEND и почему так называется?
  11. В чем особенность применения обычных операторов DML внутри PSQL?
  12. Возможно ли зафиксировать изменения в БД, даже если в последующем транзакция будет отменена?
  13. Какие инструменты существуют для обработки ошибок выполнения операторов?
  14. Для чего используются курсоры?
  15. Какие бывают типы курсоров и чем они отличаются?
  16. Какие особенности предлагает оператор CALL?