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

SKIP LOCKED

Назначение

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

Предложение SKIP LOCKED заставляет движок пропускать записи, заблокированные другими транзакциями, вместо того,чтобы ждать или вызывать ошибки при конфликте.

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

Note

Если предложение SKIP LOCKED используется совместно с FIRST/SKIP/ROWS/OFFSET/FETCH, то сначала пропускаются заблокированные записи,а затем применяются ограничители FIRST/SKIP/ROWS/OFFSET/FETCH к оставшимся записям.

См. также: SELECT …​ SKIP LOCKED,UPDATE …​ SKIP LOCKED.

Example 1. Использование предложения SKIP LOCKED для организации очереди

Подготовка метаданных.

create table emails_queue (
    subject varchar(60) not null,
    text blob sub_type text not null
);

set term !;

create trigger emails_queue_ins after insert on emails_queue
as
begin
    post_event('EMAILS_QUEUE');
end!

set term ;!

Отправка данных приложением или подпрограммой

insert into emails_queue (subject, text)
  values ('E-mail subject', 'E-mail text...');
commit;

Клиентское приложение может прослушивать событие EMAILS_QUEUE, чтобы отправлять электронные письма, используя этот запрос:

delete from emails_queue
  rows 10
  skip locked
  returning subject, text;

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

RETURNING

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

Note

  • В DML оператор DELETE с предложением RETURNING возвращает курсор (до Firebird 5.0 мог возвращать только одну запись).В настоящее время операторы с предложением RETURNING не могут быть применены вместе с предложением FOR для цикла по курсору в PSQL.Это поведение может быть изменено в последующих версиях Firebird.Если записи не были удалены, то возвращаемые столбцы содержат NULL;

  • В PSQL, если строка не была удалена, ничего не возвращается, и целевые переменные сохраняют свои значения;

  • Предложение INTO доступно только в PSQL.
Example 1. Использование предложения RETURNING в операторе DELETE
DELETE FROM Scholars
WHERE first_name = 'Henry' AND last_name = 'Higgins'
RETURNING last_name, fullname, id

DELETE FROM Dumbbells
ORDER BY iq DESC
ROWS 1
RETURNING last_name, iq
INTO :lname, :iq;

DELETE FRMO TempSales ts
WHERE ts.id = tempid
RETURNING ts.qty
INTO new.qty;

docnext count = 11

MERGE

Назначение

Слияние записей источника в целевую таблицу (или обновляемое представление).

Доступно в

DSQL, PSQL

Синтаксис
MERGE
  INTO target [[AS] target_alias]
  USING <source> [[AS] source_alias]
  ON <join condition>
  <merge when> [<merge when> ...]
  [<plan clause>]
  [<order by clause>]
  [<returning clause>]

<source> ::= tablename | (<select_stmt>)

<merge when> ::=
    <merge when matched>
  | <merge when not matched by target>
  | <merge when not matched by source>

<merge when matched> ::=
  WHEN MATCHED [ AND <condition> ]
    THEN { UPDATE SET <assignment_list> | DELETE }

<merge when not matched by target> ::=
  WHEN NOT MATCHED [ BY TARGET ] [ AND <condition> ]
    THEN INSERT [ <left paren> <column_list> <right paren> ]
    VALUES <left paren> <value_list> <right paren>

<merge when not matched by source> ::=
  WHEN NOT MATCHED BY SOURCE [ AND <condition> ] THEN
    { UPDATE SET <assignment list> | DELETE }

<assignment_list> ::=
  col_name = <m_value> [, colname = <m_value> ...]

<column_list> ::= col_name [, col_name ...]

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

<m_value> ::= <value_expression> | DEFAULT

<returning clause> ::= RETURNING <returning_list> [INTO <variable_list>]

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.* | NEW.* | OLD.*
  | <return_expression> [COLLATE collation] [[AS] ret_alias]

<return_expression> ::=
    <value_expression>
  | [target.]col_name
  | NEW.col_name
  | OLD.col_name

<value_expression> ::=
    <literal>
  | <context-variable>
  | <other-single-value-expr>

<variables> ::=
  [:]varname [, [:]varname ...]
Table 1. Параметры оператора MERGE
Параметр Описание

target

Целевая таблица или обновляемое представление.

source

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

target_alias

Псевдоним целевой таблицы или представления.

source_alias

Псевдоним источника.

join condition

Условие соединения целевой таблицы и источника.

condition

Дополнительные условия проверки в предложениях WHEN MATCHED или WHEN NOT MATCHED.

col_name

Столбец целевой таблицы или представления.

m_value

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

return_expression

Выражение, возвращаемое в предложении RETURNING.

ret_alias

Псевдоним для выражения, возвращаемого в предложении RETURNING.

varname

Имя PSQL переменной.

Оператор MERGE производит слияние записей источника и целевой таблицы (или обновляемым представлением).В процессе выполнения оператора MERGE читаются записи источника и выполняются INSERT, UPDATE или DELETE для целевойтаблицы в зависимости от условий.

Источником может быть таблица, представление, хранимой процедурой или производной таблицей. При выполнении оператора MERGE производится соединение между источником (USING) и целевой таблицей. Тип соединения зависит от присутствия предложений WHEN NOT MATCHED:

  • <merge when not matched by target> и <merge when not matched by source> — FULL JOIN

  • <merge when not matched by source> — RIGHT JOIN

  • <merge when not matched by target> — LEFT JOIN

  • только <merge when matched> — INNER JOIN

Действие над целевой таблицей, а также условие при котором оно выполняется, описывается в предложении WHEN. Допускается несколько предложений WHEN MATCHED, WHEN NOT MATCHED [BY TARGET] и WHEN NOT MATCHED BY SOURCE.

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

Note

WHEN NOT MATCHED [BY TARGET] оценивается с точки зрения источника, т.е.таблицы или набора данных указанного в предложения USING.Так сделано потому, что если запись источника не имеет совпадения с записью цели, то выполняется INSERT.Разумеется, если запись цели не соответствует записи в источнике, то никакие действия не производятся.

На данный момент переменная ROW_COUNT возвращает значение 1, даже если было модифицировано или вставлено более 1 записи.См. CORE-4400.

WHEN MATCHED

Указывает, что все строки target, которые соответствуют строкам, возвращенным выражением <source> ON <join condition>, и удовлетворяют дополнительным условиям поиска, обновляются (предложение UPDATE) или удаляются (предложение DELETE) в соответствии с предложением <merge when matched>.

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

Инструкция MERGE не может обновить одну строку более одного раза или одновременно обновить и удалить одну и ту же строку.

Note

Если условие WHEN MATCHED присутствует, и несколько записей совпадают с записями в целевой таблице, то будет выдана ошибка.

До Firebird 4.0 UPDATE выполнится для всех совпадающих записей источника, и каждое последующее обновление перезапишет предыдущее.Это поведение не соответствует SQL стандарту.

В списке SET предложения UPDATE вместо значения столбца можно использовать ключевое слово DEFAULT. В этом случае столбец получит значение по умолчанию, указанное при определении целевой таблицы. Если значение по умолчанию для столбца отсутствует, то столбец получит значение NULL.

WHEN NOT MATCHED [BY TARGET]

Указывает, что все строки target, которые не соответствуют строкам, возвращенным выражением <source> ON <join condition>, и удовлетворяют дополнительным условиям поиска, вставляются в целевую таблицу (предложение INSERT) в соответствии с предложением <merge when not matched by target>.

Допускается указывать несколько предложений WHEN NOT MATCHED [BY TARGET]. Если указано более одного предложенияWHEN NOT MATCHED [BY TARGET], то все их следует дополнять дополнительными условиями поиска, за исключением последнего.

В списке VALUES предложения INSERT вместо значения столбца можно использовать ключевое слово DEFAULT. В этом случае столбец получит значение по умолчанию, указанное при определении целевой таблицы.Если значение по умолчанию для столбца отсутствует, то столбец получит значение NULL.

WHEN NOT MATCHED BY SOURCE

Указывает, что все строки target, которые не соответствуют строкам, возвращенным выражением <source> ON <join condition>, и удовлетворяют дополнительным условиям поиска, (предложение UPDATE) или удаляются (предложение DELETE) в соответствии с предложением <merge when not matched by source>.

Предложение WHEN NOT MATCHED BY SOURCE доступно начиная с Firebird 5.0.

Допускается указывать несколько предложений WHEN NOT MATCHED BY SOURCE. Если указано более одного предложенияWHEN NOT MATCHED BY SOURCE, то все их следует дополнять дополнительными условиями поиска, за исключением последнего.

В списке SET предложения UPDATE вместо значения столбца можно использовать ключевое слово DEFAULT. В этом случае столбец получит значение по умолчанию, указанное при определении целевой таблицы. Если значение по умолчанию для столбца отсутствует, то столбец получит значение NULL.

Note

Обратите внимание! В списке SET предложения UPDATE не имеет смысла использовать выражения со ссылкой на <source>,поскольку ни одна запись из <source> не соответствует записям target.

Примеры

Example 1. Простые операторы MERGE
MERGE INTO books b
USING purchases p
ON p.title = b.title AND p.booktype = 'bk'
WHEN MATCHED THEN
  UPDATE SET b.descr = b.descr || '; ' || p.descr
WHEN NOT MATCHED THEN
  INSERT (title, descr, bought)
  VALUES (p.title, p.descr, p.bought);

-- с использованием производной таблицы
MERGE INTO customers c
USING (SELECT * FROM customers_delta WHERE id > 10) cd
ON (c.id = cd.id)
WHEN MATCHED THEN
  UPDATE SET name = cd.name
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (cd.id, cd.name);

-- совместно с рекурсивным CTE
MERGE INTO numbers
USING (
  WITH RECURSIVE r(n) AS (
    SELECT 1 FROM rdb$database
    UNION ALL
    SELECT n+1 FROM r WHERE n < 200
  )
  SELECT n FROM r
) t
ON numbers.num = t.n
WHEN NOT MATCHED THEN
  INSERT(num) VALUES(t.n);

-- с использованием предложения DELETE
MERGE INTO SALARY_HISTORY
USING (
  SELECT EMP_NO
  FROM EMPLOYEE
  WHERE DEPT_NO = 120) EMP
ON SALARY_HISTORY.EMP_NO = EMP.EMP_NO
WHEN MATCHED THEN DELETE
Example 2. Использование оператора MERGE с дополнительными условиями в предложениях WHEN [NOT] MATCHED

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

MERGE INTO PRODUCT_INVENTORY AS TARGET
USING (
  SELECT
    SL.ID_PRODUCT,
    SUM(SL.QUANTITY)
  FROM SALES_ORDER_LINE SL
    JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
  WHERE S.BYDATE = CURRENT_DATE
  GROUP BY 1
) AS SRC(ID_PRODUCT, QUANTITY)
ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET
    TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
    TARGET.BYDATE = CURRENT_DATE
Example 3. Использование оператора MERGE с WHEN NOT MATCHED BY SOURCE

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

MERGE
  INTO customers c
  USING new_customers nc
  ON (c.id = nc.id)
  WHEN MATCHED THEN
    UPDATE SET
	  name = cd.name
  WHEN NOT MATCHED BY SOURCE THEN
	DELETE
См. также:

SELECT, INSERT, UPDATE, DELETE.

RETURNING

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

Возвращаемые значения содержат изменения, произведённые в триггерах BEFORE.

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

Вместо списка столбцов может быть указана звёздочка (*), в этом случае будут возвращены все столбцы целевой таблицы.Префиксы NEW и OLD могут быть использованы совместно со звёздочкой.

Для предложений WHEN MATCHED UPDATE и MERGE WHEN NOT MATCHED неуточненные имена столбцов или уточнённые именамитаблиц или их псевдонимами понимаются как столбцы с префиксом NEW, для предложений MERGE WHEN MATCHED DELETE — с префиксом OLD.

Note

  • В DML оператор MERGE с предложением RETURNING возвращает курсор (до Firebird 5.0 мог возвращать только одну запись).В настоящее время операторы с предложением RETURNING не могут быть применены вместе с предложением FOR для цикла по курсору в PSQL.Это поведение может быть изменено в последующих версиях Firebird.

  • Предложение INTO доступно только в PSQL.
Example 1. Использование оператора MERGE с предложением RETURNING

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

MERGE INTO PRODUCT_IVENTORY AS TARGET
USING (
  SELECT
    SL.ID_PRODUCT,
    SUM(SL.QUANTITY)
  FROM SALES_ORDER_LINE SL
    JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
  WHERE S.BYDATE = CURRENT_DATE
    AND SL.ID_PRODUCT = :ID_PRODUCT
  GROUP BY 1
) AS SRC(ID_PRODUCT, QUANTITY)
ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET
    TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
    TARGET.BYDATE = CURRENT_DATE
RETURNING OLD.QUANTITY, NEW.QUANTITY, SRC.QUANTITY
INTO :OLD_QUANTITY, :NEW_QUANTITY, :DIFF_QUANTITY

EXECUTE PROCEDURE

Назначение

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

Доступно в

DSQL, ESQL, PSQL

Синтаксис
EXECUTE PROCEDURE procname
   [{ <inparam-list> | ( <inparam-list> ) }]
   [RETURNING_VALUES { <outvar-list> | ( <outvar-list> ) }]

<inparam-list> ::=
  <inparam> [, <inparam> ...]

<outvar-list> ::=
  <outvar> [, <outvar> ...]

<outvar> ::= [:]varname
Table 1. Параметры оператора EXECUTE PROCEDURE
Параметр Описание

procname

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

inparam

Выражение совместимое по типу с входным параметром хранимой процедуры.

varname

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

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

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

Оператор EXECUTE PROCEDURE является наиболее часто используемым стилем вызова хранимой процедуры, которая написанадля модификации некоторых данных. Их код не содержит оператора SUSPEND.Такие хранимые процедуры могут возвратить набор данных, состоящий не более чем из одной строки.Этот набор может быть передан в переменные другой (вызывающей) процедуры с помощью предложения RETURNING_VALUES.Клиентские интерфейсы, как правило, имеют обертку API, которые могут извлекать выходные значения в однострочный буфер при вызове процедуры через EXECUTE PROCEDURE в DSQL.

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

Note

  • В PSQL И DSQL входными параметрами могут быть любые совместимые по типу выражения;

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

  • Предложение RETURNING_VALUES доступно только в PSQL.
Example 1. Использование оператора EXECUTE PROCEDURE в PSQL
EXECUTE PROCEDURE MakeFullName(:First_Name, :Middle_Name, :Last_Name)
RETURNING_VALUES :FullName;

В этом операторе использование двоеточия (“:”) для входных и выходных параметров необязательно.

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

EXECUTE PROCEDURE MakeFullName
   ('Mr./Mrs. ' || First_Name, Middle_Name, upper(Last_Name))
RETURNING_VALUES FullName;
Example 2. Вызов оператора EXECUTE PROCEDURE в isql
EXECUTE PROCEDURE MakeFullName
  'J', 'Edgar', 'Hoover';

EXECUTE BLOCK

Назначение

Выполнение анонимного PSQL блока.

Доступно в

DSQL

Синтаксис
EXECUTE BLOCK [(<inparams>)]
  [RETURNS (<outparams>)]
  <psql-routine-body>

<inparams> ::= <param_decl> = ? [, <inparams> ]

<outparams> ::= <param_decl> [, <outparams>]

<param_decl> ::= paramname <type> [NOT NULL] [COLLATE collation]

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

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

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

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

<psql-routine-body> ::=
  См. Синтаксис тела модуля
Table 1. Параметры оператора EXECUTE BLOCK
Параметр Описание

param_decl

Описание входного или выходного параметра.

paramname

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

non_array_datatype

Тип данных SQL за исключение массивов.

collation

Порядок сортировки.

domain

Домен.

rel

Имя таблицы или представления.

col

Имя столбца таблицы или представления.

Выполняет блок PSQL кода, так как будто это хранимая процедура, возможно с входными и выходными параметрами и локальными переменными.Это позволяет пользователю выполнять “на лету” PSQL в контексте DSQL.

Примеры:

Этот пример вводит цифры от 0 до 127 и соответствующие им ASCII символов в таблицу ASCIITABLE:

EXECUTE BLOCK
AS
  DECLARE i INT = 0;
BEGIN
  WHILE (i < 128) DO
  BEGIN
    INSERT INTO AsciiTable VALUES (:i, ascii_char(:i));
    i = i + 1;
  END
END

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

EXECUTE BLOCK (
  x DOUBLE PRECISION = ?,
  y DOUBLE PRECISION = ?)
RETURNS (gmean DOUBLE PRECISION)
AS
BEGIN
  gmean = sqrt(x*y);
  SUSPEND;
END

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

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

EXECUTE BLOCK (smallest INT = ?, largest INT = ?)
RETURNS (
  number INT,
  square BIGINT,
  cube BIGINT,
  fourth BIGINT)
AS
BEGIN
  number = smallest;
  WHILE (number <= largest) DO
  BEGIN
    square = number * number;
    cube = number * square;
    fourth = number * cube;
    SUSPEND;
    number = number + 1;
  END
END

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

См. также:

Операторы языка PSQL.

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

Выполнение блока без входных параметров должно быть возможным с любым клиентом Firebird, который позволяет пользователю вводить свои собственные DSQL операторы.Если есть входные параметры, все становится сложнее: эти параметры должны получить свои значения после подготовки оператора, но перед его выполнением.Это требует специальных возможностей, которыми располагает не каждое клиентское приложение (Например, isql такой возможности не предлагает).

Сервер принимает только вопросительные знаки ("?") в качестве заполнителей для входных значений, а не ":а", ":MyParam" и т.д., или литеральные значения.Клиентское программное обеспечение может поддерживать форму ":ххх", в этом случае будет произведена предварительная обработка запроса перед отправкой его на сервер.

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

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

Для получения дополнительной информации о параметрах и объявлениях переменных, [TYPE OF] domain, TYPE OF COLUMN и т.д.обратитесь к главе DECLARE VARIABLE.

Терминатор оператора

Некоторые редакторы SQL-операторов — в частности утилита isql, которая идёт в комплекте с Firebird, ивозможно некоторые сторонние редакторы — используют внутреннее соглашение, которое требует, чтобы все операторы были завершены с точкой с запятой.

Это создает конфликт с синтаксисом PSQL при кодировании в этих средах.Если вы не знакомы с этой проблемой и ее решением, пожалуйста, изучать детали в главе PSQL в разделе,озаглавленном Изменение терминатора в isql.