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

Составные планы

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

SELECT s.id, s.name, s.class, c.mentor
FROM students s
JOIN classes c ON c.name = s.class
PLAN JOIN (s NATURAL, c INDEX (pk_classes))

План в EXPLAIN форме:

Select Expression
  ->  Nested Loop Join (inner)
      -> Table "STUDENTS" as "S" Full Scan
      -> Filter
          -> Table "CLASSES" as "C" Access By ID
              -> Bitmap
                  -> Index "PK_CLASSES" Unique Scan

То же самое соединение, отсортированное по индексированному столбцу:

SELECT s.id, s.name, s.class, c.mentor
FROM students s
JOIN classes c ON c.name = s.class
PLAN JOIN (s ORDER pk_students, c INDEX (pk_classes))
ORDER BY s.id

План в EXPLAIN форме:

Select Expression
  ->  Nested Loop Join (inner)
      -> Table "STUDENTS" as "S" Access By ID
          -> Index "PK_STUDENTS" Full Scan
      -> Filter
          -> Table "CLASSES" as "C" Access By ID
              -> Bitmap
                  -> Index "PK_CLASSES" Unique Scan

И соединение, отсортированное не по индексированному столбцу:

SELECT s.id, s.name, s.class, c.mentor
FROM students s
JOIN classes c ON c.name = s.class
PLAN SORT (JOIN (S NATURAL, c INDEX (pk_classes))))
ORDER BY s.name

План в EXPLAIN форме:

Select Expression
  -> Sort (record length: 152, key length: 12)
      ->  Nested Loop Join (inner)
          -> Table "STUDENTS" as "S" Full Scan
          -> Filter
              -> Table "CLASSES" as "C" Access By ID
                  -> Bitmap
                      -> Index "PK_CLASSES" Unique Scan

Соединение с добавленным условием поиска:

SELECT s.id, s.name, s.class, c.mentor
FROM students s
JOIN classes c ON c.name = s.class
WHERE s.class <= '2'
PLAN SORT (JOIN (s INDEX (fk_student_class), c INDEX (pk_classes)))
ORDER BY s.name

План в EXPLAIN форме:

Select Expression
  -> Sort (record length: 152, key length: 12)
     ->  Nested Loop Join (inner)
         -> Filter
            -> Table "STUDENTS" as "S" Access By ID
               -> Bitmap
                  -> Index "FK_STUDENT_CLASS" Range Scan (lower bound: 1/1)
         -> Filter
            -> Table "CLASSES" as "C" Access By ID
               -> Bitmap
                  -> Index "PK_CLASSES" Unique Scan

То же самое, но используется левое внешнее соединение:

SELECT s.id, s.name, s.class, c.mentor
FROM classes c
LEFT JOIN students s ON c.name = s.class
WHERE s.class <= '2'
PLAN SORT (JOIN (c NATURAL, s INDEX (fk_student_class)))
ORDER BY s.name

План в EXPLAIN форме:

Select Expression
  -> Sort (record length: 192, key length: 56)
      -> Filter
         ->  Nested Loop Join (outer)
             -> Table "CLASSES" as "C" Full Scan
             -> Filter
                -> Table "STUDENTS" as "S" Access By ID
                   -> Bitmap
                      -> Index "FK_STUDENT_CLASS" Range Scan (full match)

Если нет доступных индексов для условия соединения (или вы не хотите его использовать), то возможно соединение потоков с помощью метода HASH или MERGE.

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

SELECT *
FROM students s
JOIN classes c ON c.cookie = s.cookie
PLAN HASH (c NATURAL, s NATURAL)

План в EXPLAIN форме:

Select Expression
    -> Filter
        -> Hash Join (inner)
            -> Table "STUDENTS" as "S" Full Scan
            -> Record Buffer (record length: 145)
                -> Table "CLASSES" as "C" Full Scan

При выполнении соединения методом MERGE план должен сначала отсортировать оба потока по соединяемым столбцам и затем произвести слияние.Это достигается с помощью директив SORT (которую вы уже встречали) и MERGE используемую вместо JOIN.

SELECT *
FROM students s
JOIN classes c ON c.cookie = s.cookie
PLAN MERGE (SORT (c NATURAL), SORT (s NATURAL))

Добавление предложения ORDER BY означает, что результат слияния также должен быть отсортирован:

SELECT *
FROM students s
JOIN classes c ON c.cookie = s.cookie
PLAN SORT (MERGE (SORT (c NATURAL), SORT (s NATURAL)))
ORDER BY c.name, s.id

И наконец, мы добавляем условие поиска на двух индексированных столбцах таблицы STUDENTS:

SELECT *
FROM students s
JOIN classes c ON c.cookie = s.cookie
WHERE s.id < 10 AND s.class <= '2'
PLAN SORT (MERGE (SORT (c NATURAL),
                  SORT (s INDEX (pk_students, fk_student_class))))
ORDER BY c.name, s.id

Как следует из формального определения синтаксиса, JOIN и MERGE могут объединять в плане более двух потоков.Кроме того, каждое выражение плана может использоваться в качестве элемента в охватывающем плане.Это означает, что планы некоторых сложных запросов могут иметь различные уровни вложенности.

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

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

SELECT *
FROM COLOR
WHERE EXISTS(
             SELECT *
             FROM HORSE
             WHERE HORSE.CODE_COLOR = COLOR.CODE_COLOR
             PLAN (HORSE INDEX (FK_HORSE_COLOR)))
PLAN(COLOR NATURAL)

UNION

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

По умолчанию, объединение подавляет дубликаты строк.UNION ALL отображает все строки, включая дубликаты.Необязательное ключевое слово DISTINCT делает поведение по умолчанию явным.

Синтаксис
<query-expression> ::=
  [<with-clause>]
  <query-expression-body>
  [<order-by-clause>]
  [<rows-clause> | {[<result-offset-clause>] [<fetch-first-clause>]}]

<query-expression-body> ::=
    <query-term>
  | <query-expression-body> UNION [{ DISTINCT | ALL }] <query-term>

<query-term> ::= <query-primary>

<query-primary> ::=
    <query-specification>
  | (<query-expression-body>
      [<order-by-clause>]
      [<result-offset-clause>] [<fetch-first-clause>]
    )

<query-specification> ::=
  SELECT
    <limit-clause>
    [{ ALL | DISTINCT }] <select-list>
  FROM <table-reference> [, <table-reference> ...]
  [WHERE <search-condition>]
  [GROUP BY <value-expression> [, <value-expression> ...]]
  [HAVING <search-condition>]
  [WINDOW <window-definition> [, <window-definition> ...]]
  [PLAN <plan-expression>]

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

Если объединение имеет предложение ORDER BY, то единственно возможными элементами сортировки являются целочисленные литералы, указывающие на позиции столбцов, необязательно сопровождаемые ASC | DESC и/или NULLS {FIRST | LAST} директивами.Это так же означает, что вы не можете упорядочить объединение ничем, что не является столбцом объединения.(Однако вы можете завернуть его в производную таблицу, которая даст вам все обычные параметры сортировки.)

Объединения позволены в подзапросах любого вида и могут самостоятельно содержать подзапросы.Они также могут содержать соединения (joins), и могут принимать участие в соединениях, если завёрнуты в производную таблицу.

Примеры

Этот запрос представляет информацию из различных музыкальных коллекций в одном наборе данных с помощью объединений:

SELECT id, title, artist, len, 'CD' AS medium
FROM cds
UNION
SELECT id, title, artist, len, 'LP'
FROM records
UNION
SELECT id, title, artist, len, 'MC'
FROM cassettes
ORDER BY 3, 2 -- artist, title

Если id, title, artist и length – единственные поля во всех участвующих таблицах, то запрос может быть записан так:

SELECT c.*, 'CD' AS medium
FROM cds c
UNION
SELECT r.*, 'LP'
FROM records r
UNION
SELECT c.*, 'MC'
FROM cassettes c
ORDER BY 3, 2 -- artist, title

Уточнение “звёзд” необходимо здесь, потому что они не являются единственным элементом в списке столбцов.Заметьте, что псевдонимы “c” в первой и третьей выборке не кусают друг друга.Они не имеют контекста объединения, а лишь применяются к отдельным запросам на выборку.

Следующий запрос получает имена и телефонные номера переводчиков и корректоров.Те переводчики, которые также работают корректорами, будут отображены только один раз в результирующем наборе, если номера их телефонов одинаковые в обеих таблицах.Тот же результат может быть получен без ключевого слова DISTINCT.Если вместо ключевого слова DISTINCT, будет указано ключевое слово ALL, эти люди будут отображены дважды.

SELECT name, phone
FROM translators
UNION DISTINCT
SELECT name, telephone
FROM proofreaders

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

SELECT name, phone, hourly_rate
FROM clowns
WHERE hourly_rate < ALL
  (SELECT hourly_rate FROM jugglers
   UNION
   SELECT hourly_rate FROM acrobats)
ORDER BY hourly_rate

Использование выражений запроса в скобках для отображения сотрудников с самой высокой и самой низкой зарплатой:

(
  select emp_no, salary, 'lowest' as type
  from employee
  order by salary asc
  fetch first row only
)
union all
(
  select emp_no, salary, 'highest' as type
  from employee
  order by salary desc
  fetch first row only
);

docnext count = 38

Полный синтаксис SELECT

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

Note

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

Несмотря на то, что здесь описан полный синтаксис, некоторые представления не отображаются (например, <value-expression>) и предполагается, что они понятны читателю, и в некоторых случаях мы используем сокращения, например, используя query-name или column-alias для идентификаторов в синтаксическом представлении.

Приведенный ниже синтаксис не включает PSQL синтаксис SELECT…​ INTO, который по сути представляет собой <cursor-specification> INTO <variable-list>.
Полный синтакси оператора SELECT
<cursor-specification> ::=
  <query-expression> [<updatability-clause>] [<lock-clause>]

<query-expression> ::=
  [<with-clause>] <query-expression-body> [<order-by-clause>]
    [{ <rows-clause>
     | [<result-offset-clause>] [<fetch-first-clause>] }]

<with-clause> ::=
  WITH [RECURSIVE] <with-list-element> [, <with-list-element> ...]

<with-list-element> ::=
  query-name [(<column-name-list>)] AS (<query-expression>)

<column-name-list> ::= column-name [, column-name ...]

<query-expression-body> ::=
    <query-term>
  | <query-expression-body> UNION [{ DISTINCT | ALL }] <query-term>

<query-term> ::= <query-primary>

<query-primary> ::=
    <query-specification>
  | (<query-expression-body> [<order-by-clause>]
     [<result-offset-clause>] [<fetch-first-clause>])

<query-specification> ::=
  SELECT <limit-clause> [{ ALL | DISTINCT }] <select-list>
    FROM <table-reference> [, <table-reference> ...]
    [WHERE <search-condition>]
    [GROUP BY <value-expression> [, <value-expression> ...]]
    [HAVING <search-condition>]
    [WINDOW <window-definition> [, <window-definition> ...]]
    [PLAN <plan-expression>]

<limit-clause> ::= [FIRST <limit-expression>] [SKIP <limit-expression>]

<limit-expression> ::=
    <integer-literal>
  | <query-parameter>
  | (<value-expression>)

<select-list> ::= * | <select-sublist> [, <select-sublist> ...]

<select-sublist> ::=
    table-alias.*
  | <value-expression> [[AS] column-alias]

<table-reference> ::= <table-primary> | <joined-table>

<table-primary> ::=
    <table-or-query-name> [[AS] correlation-name]
  | [LATERAL] <derived-table> [<correlation-or-recognition>]
  | <parenthesized-joined-table>

<table-or-query-name> ::=
    table-name
  | query-name
  | [package-name.]procedure-name [(<procedure-args>)]

<procedure-args> ::= <value-expression> [, <value-expression> ...]

<correlation-or-recognition> ::=
  [AS] correlation-name [(<column-name-list>)]

<derived-table> ::= (<query-expression>)

<parenthesized-joined-table> ::=
    (<parenthesized-joined-table>)
  | (<joined-table>)

<joined-table> ::=
    <cross-join>
  | <natural-join>
  | <qualified-join>

<cross-join>
  <table-reference> CROSS JOIN <table-primary>

<natural-join> ::=
  <table-reference> NATURAL [<join-type>] JOIN <table-primary>

<join-type> ::= INNER | { LEFT | RIGHT | FULL } [OUTER]

<qualified-join> ::=
  <table-reference> [<join-type>] JOIN <table-primary>
  { ON <search-condition>
  | USING (<column-name-list>) }

<window-definition> ::=
  new-window-name AS (<window-specification-details>)

<window-specification-details> ::=
  [existing-window-name]
    [<window-partition-clause>]
    [<order-by-clause>]
    [<window-frame-clause>]

<window-partition-clause> ::=
  PARTITION BY <value-expression> [, <value-expression> ...]

<order-by-clause> ::=
  ORDER BY <sort-specification> [, <sort-specification> ...]

<sort-specification> ::=
  <value-expression> [<ordering-specification>] [<null-ordering>]

<ordering-specification> ::=
    ASC  | ASCENDING
  | DESC | DESCENDING

<null-ordering> ::=
    NULLS FIRST
  | NULLS LAST

<window-frame-clause> ::= { RANGE | ROWS } <window-frame-extent>

<window-frame-extent> ::=
    <window-frame-start>
  | <window-frame-between>

<window-frame-start> ::=
    UNBOUNDED PRECEDING
  | <value-expression> PRECEDING
  | CURRENT ROW

<window-frame-between> ::=
  BETWEEN { UNBOUNDED PRECEDING | <value-expression> PRECEDING
          | CURRENT ROW | <value-expression> FOLLOWING }
  AND { <value-expression> PRECEDING | CURRENT ROW
      | <value-expression> FOLLOWING | UNBOUNDED FOLLOWING }

<rows-clause> ::= ROWS <value-expression> [TO <value-expression>]

<result-offset-clause> :: =
  OFFSET <offset-fetch-expression> { ROW | ROWS }

<offset-fetch-expression> ::=
    <integer-literal>
  | <query-parameter>

<fetch-first-clause> ::=
  [FETCH { FIRST | NEXT }
   [<offset-fetch-expression>] { ROW | ROWS } ONLY]

<updatability-clause> ::= FOR UPDATE [OF <column-name-list>]

<lock-clause> ::= WITH LOCK [SKIP LOCKED]

INSERT

Назначение

Вставка данных в таблицу.

Доступно в

DSQL, ESQL, PSQL

Синтаксис
INSERT INTO target [(<column_list>)]
  [OVERRIDE {SYSTEM | USER} VALUE]
  {DEFAULT VALUES | <value_source>}
  [RETURNING <returning_list> [INTO <variables>]]

<column_list> ::= col-name [, col-name ...]

<value_source> ::= VALUES (<value_list>) | <select_stmt>

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

<ins_value> :: = <value_expression> | DEFAULT

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

<output_column> ::=
    target.*
  | <return_expression> [COLLATE collation] [[AS] alias]

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

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

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

target

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

col-name

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

value_expression

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

literal

Литерал.

context-variable

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

other-single-value-expr

Любое другое выражение, возвращающее единственное значение типа данных Firebird или NULL.

return_expression

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

collation

Существующее имя сортировки (только для символьных типов).

alias

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

varname

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

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

Note

  • Один столбец не может быть указан более одного раза в списке столбцов;

  • При возвращении значений столбцов вставленной записи с помощью INTO в контекстные переменные NEW.columnname в триггерахнеобязательно использоваться префикс двоеточия (“:”).

INSERT …​ VALUES

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

Note

Вводный синтаксис даёт возможность определить набор символов для значений строковых констант (литералов).Вводный синтаксис работает только с литералами строк: он не может быть применён к строковым переменным, параметрам, ссылкам на столбцы или значения, выражениям.
Example 1. Использование INSERT с предложением VALUES
INSERT INTO cars (make, model, byyear)
VALUES ('Ford', 'T', 1908);

INSERT INTO cars
VALUES ('Ford', 'T', 1908, 'USA', 850);

-- обратите внимание на префикс '_' (introducer syntax)
INSERT INTO People
VALUES (_ISO8859_1 'Hans-Jörg Schäfer');

Ключевое слово DEFAULT

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

Если ключевое слово DEFAULT указано для столбца, определенного как GENERATED BY DEFAULT AS IDENTITY, то столбец получит следующее значение идентификации, так как будто этот столбец не был указан в запросе вовсе.

Example 1. Использование ключевого слова DEFAULT в операторе INSERT
CREATE TABLE cars (
  ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
  BYYEAR SMALLINT DEFAULT 1990 NOT NULL,
  NAME VARCHAR(45),
  CONSTRAINT pk_cars PRIMARY KEY (ID)
);

-- в столбец BYYEAR попадёт значение 1990
INSERT INTO cars (byyear, name)
VALUES (DEFAULT, 'Ford Focus');

-- в столбец id попадёт значение 2, как будто мы не указывали значение для id
INSERT INTO cars (id, byyear, name)
VALUES (DEFAULT, 1996, 'Ford Mondeo');

INSERT …​ SELECT

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

Example 1. Использование оператора в форме INSERT …​ SELECT
INSERT INTO cars (make, model, byyear)
SELECT make, model, byyear
FROM new_cars;

INSERT INTO cars
SELECT *
FROM new_cars;

INSERT INTO Members (number, name)
SELECT number, name
FROM NewMembers
WHERE Accepted = 1
UNION ALL
SELECT number, name
FROM SuspendedMembers
WHERE Vindicated = 1

INSERT INTO numbers(num)
WITH RECURSIVE r(n) AS (
  SELECT 1 FROM rdb$database
  UNION ALL
  SELECT n+1 FROM r where n < 100
)
SELECT n FROM r

Конечно, имена столбцов в таблице источнике необязательно должны быть такими же, как и в таблице приёмнике.

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

INSERT …​ DEFAULT VALUES

Предложение DEFAULT VALUES позволяет вставлять записи без указания значений вообще, ни непосредственно (в предложении VALUES), ни из оператора SELECT.Это возможно, только если для каждого NOT NULL поля и полей, на которые наложены другие ограничения, или имеются допустимые объявленные значения по умолчанию, или эти значения устанавливаются в BEFORE INSERT триггере.

Example 1. Использование DEFAULT VALUES в операторе INSERT
INSERT INTO journal
DEFAULT VALUES
RETURNING entry_id

Директива OVERRIDING

Значения столбцов идентификации (GENERATED BY DEFAULT AS IDENTITY) могут быть переопределены в операторах INSERT, UPDATE OR INSERT, MERGE.Для этого просто достаточно указать значение столбца в списке значений.Однако для столбцов определённых как GENERATED ALWAYS это недопустимо.

Директива OVERRIDING SYSTEM VALUE позволяет заменить сгенерированное системой значение на значение указанное пользователем.Директива OVERRIDING SYSTEM VALUE вызовет ошибку, если в таблице нет столбцов идентификации или если они определены как GENERATED BY DEFAULT AS IDENTITY.

Example 1. Использование директивы OVERRIDING SYSTEM VALUE в операторе INSERT
CREATE TABLE objects (
  id INT GENERATED ALWAYS AS IDENTITY,
  name CHAR(50));

-- будет вставлено значение с кодом 11
INSERT INTO objects (id, name)
OVERRIDING SYSTEM VALUE
VALUES (11, 'Laptop');

Директива OVERRIDE USER VALUE выполняет обратную задачу, т.е.заменяет значение указанное пользователем на значение сгенерированное системой, если столбец идентификации определён как GENERATED BY DEFAULT AS IDENTITY.Директива OVERRIDING USER VALUE вызовет ошибку, если в таблице нет столбцов идентификации или если они определены как GENERATED ALWAYS AS IDENTITY.

Example 2. Использование директивы OVERRIDING USER VALUE в операторе INSERT
CREATE TABLE objects (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  name CHAR(50));

-- значение 12 будет проигнорировано
INSERT INTO objects (id, name)
OVERRIDING SYSTEM VALUE
VALUES (12, 'Laptop');
См. также:

Столбцы идентификации (автоинкремент).

RETURNING

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

Important

  • В DML оператор INSERT …​ SELECT с предложением RETURNING возвращает курсор (до Firebird 5.0 мог возвращать только одну запись).

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

  • Оператор INSERT …​ VALUES с предложением RETURNING всегда возвращает одну запись. Если запись не была вставлена на самом деле, то все поля в возвращаемой строке будут иметь значения NULL. Это поведение может быть изменено позже. В PSQL, если ни одна запись не вставлена, то ничего не возвращается и все целевые переменные сохраняют свои прежние значения.
Example 1. Использование предложения RETURNING в операторе INSERT
INSERT INTO Scholars (firstname, lastname, address,
  phone, email)
VALUES (
  'Henry', 'Higgins', '27A Wimpole Street',
  '3231212', NULL)
RETURNING lastname, fullname, id;

INSERT INTO Scholars (firstname, lastname, address,
  phone, email)
VALUES (
  'Henry', 'Higgins', '27A Wimpole Street',
  '3231212', NULL)
RETURNING *;

INSERT INTO Dumbbells (first_name, last_name, iq)
SELECT fname, lname, iq
FROM Friends
ORDER BY iq ROWS 1
RETURNING id, first_name, iq
INTO :id, :fname, :iq;

Вставка столбцов BLOB

Вставка в столбцы BLOB только возможна при следующих обстоятельствах:

  1. Клиентское приложение вставляет BLOB посредством Firebird API. В этом случае все зависит от приложения, и не рассматривается в этом руководстве;

  2. Длина строкового литерала не может превышать 65,533 байт (64K - 3).

    Note

    Предел в символах вычисляется во время выполнения. Для мультибайтовых наборов символов он может отличаться.Например, для строки UTF8 (4 байта на символ) ограничение строкового литерала, вероятно, будет около (floor (65533/4)) = 16383 символов.

  3. Если источником данных является столбец BLOB или выражение, возвращающее BLOB. Например, при использовании формы INSERT …​ SELECT иливнутри PSQL кода, когда в качестве параметра подставляется переменная типа BLOB.

UPDATE

Назначение

Обновление данных в таблице.

Доступно в

DSQL, ESQL, PSQL

Синтаксис
UPDATE target [[AS] alias]
  SET col_name = <upd_value> [, col_name = <upd_value> ...]
  [WHERE {<search-conditions> | CURRENT OF cursorname}]
  [PLAN <plan_items>]
  [ORDER BY <sort_items>]
  [ROWS m [TO n]]
  [SKIP LOCKED]
  [RETURNING <returning_list> [INTO <variables>]]

<upd_value> ::= <value_expression> | DEFAULT

<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. Параметры оператора UPDATE
Параметр Описание

target

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

alias

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

col_name

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

upd_value

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

literal

Литерал.

context-variable

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

other-single-value-expr

Любое другое выражение, возвращающее единственное значение типа данных Firebird или NULL.

search-conditions

Условие поиска, ограничивающее набор обновляемых строк.

cursorname

Имя курсора, по которому позиционируется обновляемая запись.

plan_items

Части плана запроса.

sort_items

Столбцы, перечисленные в предложении ORDER BY.

m, n

Целочисленные выражения для ограничения количества обновляемых строк.

return_expression

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

collation

Существующее имя сортировки (только для символьных типов).

ret_alias

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

varname

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

Оператор UPDATE изменяет значения столбцов в таблице, или в одной, или нескольких таблицах, лежащих в основе представления.Новые значения столбцов указываются в предложении SET.Затронутые строки могут быть ограничены предложениями WHERE и ROWS.Если нет ни WHERE, ни ROWS, все записи в таблице будут обновлены.

Использование псевдонима

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

Примеры

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

update Fruit set soort = 'pisang' where ...

update Fruit set Fruit.soort = 'pisang' where ...

update Fruit F set soort = 'pisang' where ...

update Fruit F set F.soort = 'pisang' where ...

Неправильное использование:

update Fruit F set Fruit.soort = 'pisang' where ...

SET

Изменяемые столбцы указываются в предложении SET.Столбцы и их значения перечисляются через запятую.Слева имя столбца, и справа значение или выражение.

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

Example 1. Использование оператора UPDATE

Данные в таблице TSET:

A B
---
1 0
2 0

После выполнения оператора

update tset set a = 5, b = a
A B
---
5 1
5 2

Обратите внимание, что старые значения (1 и 2) используются для обновления столбца b, даже после того как столбцу a были назначено новое значение (5).

Ключевое слово DEFAULT

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

Example 1. Использование ключевого слова DEFAULT в операторе UPDATE
CREATE TABLE cars (
  ID INTEGER NOT NULL,
  BYYEAR SMALLINT DEFAULT 1990 NOT NULL,
  NAME VARCHAR(45),
  CONSTRAINT pk_cars PRIMARY KEY (ID)
);

INSERT INTO cars (1, byyear, name)
VALUES (1, 1985, 'Ford Focus');

-- столбцу BYYEAR будет присвоено значение 1990
UPDATE cars
SET BYYEAR = DEFAULT
WHERE ID = 1;

WHERE

Предложение WHERE ограничивает набор обновляемых записей заданным условием, или — в PSQL — текущей строкой именованного курсора, если указано предложение WHERE CURRENT OF.

Note

Предложение WHERE CURRENT OF используется только в PSQL, т.к.в DSQL нет оператора DSQL для создания курсора.

Строковые литералы могут предваряться именем набора символов, для того чтобы Firebird понимал, как интерпретировать данные.

Example 1. Использование предложения WHERE в операторе UPDATE
UPDATE addresses
SET city = 'Saint Petersburg', citycode = 'PET'
WHERE city = 'Leningrad';

UPDATE employees
SET salary = 2.5 * salary
WHERE title = 'CEO';

-- обратите внимание на префикс '_'
UPDATE People
SET name = _ISO8859_1 'Hans-Jörg Schäfer'
WHERE id = 53662;

UPDATE employee e
SET salary = salary * 1.05
WHERE EXISTS(
  SELECT *
  FROM employee_project ep
  WHERE e.emp_no = ep.emp_no);

PLAN

Предложение PLAN позволяет вручную указать план для оптимизатора.

Example 1. Использование предложения PLAN в операторе UPDATE
UPDATE company c SET c.company_name =
    ( SELECT k.contact_name
      FROM contact k
      WHERE k.id = c.contact_id
      PLAN (K INDEX (CONTACT_ID)))
WHERE c.company_name IS NULL OR c.company_name = ''
PLAN (C NATURAL)

ORDER BY и ROWS

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

Предложение ROWS имеет смысл только вместе с предложением ORDER BY.Однако его можно использовать отдельно.

При одном аргументе m, ROWS ограничивает update первыми m записями.

Особенности:

  • Если m больше количества обрабатываемых записей в целевой таблице, то обновляется весь набор строк;

  • Если m = 0, ни одна запись не обновляется;

  • Если m < 0, выдаётся ошибка.

При двух аргументах m и n, ROWS ограничивает update записей от m до n включительно.Оба аргумента – целочисленные, и начинаются с 1.

Особенности:

  • Если m больше количества записей в целевой таблице, ни одна запись не обновляется;

  • Если n больше количества записей в целевой таблице, то обновляются записи от m до конца набора;

  • Если m < 1 или n < 1, выдаётся ошибка;

  • Если n = m - 1, ни одна запись не обновляется;

  • Если n < m - 1, выдаётся ошибка.

Example 1. Использование предложения ROWS в операторе UPDATE
-- дать надбавку 20ти сотрудникам с наименьшей зарплатой
UPDATE employees
SET salary = salary + 50
ORDER BY salary ASC
ROWS 20;

SKIP LOCKED

Назначение

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

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

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

Note

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

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

RETURNING

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

Возвращаемые значения содержат изменения, произведённые в триггерах BEFORE UPDATE, но не в триггерах AFTER UPDATE.Выражения OLD.fieldname и NEW.fieldname могут быть использованы в качестве имён столбцов.Если OLD. или NEW. не указано, возвращаются новые значения столбцов NEW..

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

Note

  • В DML оператор UPDATE с предложением RETURNING возвращает курсор (до Firebird 5.0 мог возвращать только одну запись).

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

  • Если записи не были обновлены оператором, то возвращаемые значения содержат NULL.

INTO

Предложение INTO предназначено для передачи значений в локальные переменные.Оно доступно только в PSQL.Если записи не было обновлены, ничего не возвращается, и переменные, указанные в RETURNING, сохранят свои прежние значения.

Example 1. Использование предложения RETURNING в операторе UPDATE
UPDATE Scholars
SET first_name = 'Hugh', last_name = 'Pickering'
WHERE first_name = 'Henry' AND last_name = 'Higgins'
RETURNING id, old.last_name, new.last_name;
Example 2. Использование * в предложении RETURNING в операторе UPDATE
UPDATE Scholars
SET first_name = 'Hugh', last_name = 'Pickering'
WHERE first_name = 'Henry' AND last_name = 'Higgins'
RETURNING old.*;

Обновление столбцов BLOB

Обновление столбцов BLOB всегда полностью меняет их содержимое.Даже идентификатор BLOB (ID), который является ссылкой на данные BLOB и хранится в столбце, меняется.Столбцы типа BLOB могут быть изменены, если:

  1. Клиентское приложение меняет BLOB посредством Firebird API. В этом случае все зависит от приложения, и не рассматривается в этом руководстве;

  2. Длина строкового литерала не может превышать 65,533 байт (64K - 3).

    Note

    Предел в символах вычисляется во время выполнения. Для многобайтных наборов символов он может отличаться.Например, для строки UTF8 (4 байта на символ) ограничение строкового литерала, вероятно, будет около (floor (65533/4)) = 16383 символов.

  3. Если источником данных является столбец типа BLOB или выражение, возвращающее BLOB.

UPDATE OR INSERT

Назначение

Добавление новой или обновление существующей записи в таблице.

Доступно в

DSQL, PSQL

Синтаксис
UPDATE OR INSERT INTO target [(<column_list>)]
  VALUES (<value_list>)
  [MATCHING (<column_list>)]
  [RETURNING <returning_list> [INTO <variables>]]

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

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

<ins_value> ::= <value_expression> | DEFAULT

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

<output_column> ::=
    target.* | NEW.* | OLD.*
  | <return_expression> [COLLATE collation] [[AS] 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. Параметры оператора UPDATE OR INSERT
Параметр Описание

target

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

col_name

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

ins_value

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

literal

Литерал.

context-variable

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

other-single-value-expr

Любое другое выражение, возвращающее единственное значение типа данных Firebird или NULL.

return_expression

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

alias

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

varname

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

Оператор UPDATE OR INSERT вставляет или обновляет одну, или более существующих записей.Производимое действие зависит от значений столбцов в предложении MATCHING (или, если оно не указано, то от значений столбцов первичного ключа — PK). Если найдены записи, совпадающие с указанными значениями, то они обновляются.Если нет, то вставляется новая запись.

Совпадением считается полное совпадение значений столбцов MATCHING или PK.Совпадение проверяется с использованием IS NOT DISTINCT, поэтому NULL совпадает с NULL.

Note
Ограничения

  • Если у таблицы нет первичного ключа, то указание MATCHING считается обязательным;

  • В списке MATCHING, так же как и в списке столбцов update/insert, каждый столбец может быть упомянут только один раз;

  • Предложение INTO доступно только в PSQL.

Ключевое слово DEFAULT

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

Note
Ограничение

Столбец для которого вместо значения использовано ключевое слово DEFAULT не может быть использован в предложении MATCHING.
Example 1. Использование ключевого слова DEFAULT в операторе UPDATE OR INSERT
CREATE TABLE cars (
  ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
  BYYEAR SMALLINT DEFAULT 1990 NOT NULL,
  NAME VARCHAR(45),
  CONSTRAINT pk_cars PRIMARY KEY (ID)
);

-- в столбец BYYEAR попадёт значение 1990
UPDATE OR INSERT INTO cars (byyear, name)
VALUES (DEFAULT, 'Ford Focus')
MATCHING (name);

RETURNING

Предложение RETURNING может содержать любые столбцы, указанные в операторе, или другие столбцы и выражения.Возвращаемые значения содержат все изменения, произведённые в триггерах BEFORE, но не в триггерах AFTER.Выражения OLD.fieldname и NEW.fieldname могут быть использованы в качестве возвращаемых значений.Для обычных имён столбцов возвращаются новые значения.

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

Note

  • В DSQL оператор с RETURNING всегда возвращает только одну строку.
Example 1. Использование предложения RETURNING в операторе UPDATE OR INSERT
UPDATE OR INSERT INTO Cows (Name, Number, Location)
VALUES ('Suzy Creamcheese', 3278823, 'Green Pastures')
MATCHING (Number)
RETURNING rec_id
INTO :id;

DELETE

Назначение

Удаление данных из таблицы.

Доступно в

DSQL, ESQL, PSQL

Синтаксис:
DELETE
  FROM target [[AS] alias]
  [WHERE {<search-conditions> | CURRENT OF cursorname}]
  [PLAN <plan_items>]
  [ORDER BY <sort_items>]
  [ROWS m [TO n]]
  [SKIP LOCKED]
  [RETURNING <returning_list> [INTO <variables>]]

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

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

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

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

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

target

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

alias

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

col-name

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

search-conditions

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

cursorname

Имя курсора, по которому позиционируется удаляемая запись.

plan_items

Предложение плана.

sort_items

Предложение сортировки.

m, n

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

return_expression

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

literal

Литерал.

context-variable

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

other-single-value-expr

Любое другое выражение, возвращающее единственное значение типа данных Firebird или NULL.

collation

Существующее имя сортировки (только для символьных типов).

ret_alias

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

varname

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

Оператор DELETE удаляет строки из таблицы или из одной и более таблиц представления.

Если для таблицы указан псевдоним, то он должен использоваться для всех столбцов таблицы.

WHERE

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

Удаление с помощью WHERE CURRENT OF называется позиционированным удалением (positioned delete), потому что удаляется запись в текущей позиции.Удаление при помощи “WHERE условие” называется поисковым удалением (searched delete), поскольку Firebird ищет записи, соответствующие условию.

Note

В чистом DSQL выражение WHERE CURRENT OF не имеет смысла, т.к.в DSQL нет оператора для создания курсора.
Example 1. Использование предложения WHERE в операторе DELETE
DELETE FROM People
WHERE first_name <> 'Boris' AND last_name <> 'Johnson';
DELETE FROM employee e
WHERE NOT EXISTS(
  SELECT *
  FROM employee_project ep
  WHERE e.emp_no = ep.emp_no);
DELETE FROM Cities
WHERE CURRENT OF Cur_Cities; -- только в PSQL

PLAN

Предложение PLAN позволяет вручную указать план для оптимизатора.

Example 1. Использование предложения PLAN в операторе DELETE
DELETE FROM Submissions
WHERE date_entered < '1-Jan-2002'
PLAN (Submissions INDEX ix_subm_date)

ORDER BY и ROWS

Предложение ORDER BY упорядочивает набор перед его удалением.Что может быть важно в некоторых случаях.

Предложение ROWS позволяет ограничить количество удаляемых строк.Имеет смысл только в комбинации с предложением ORDER BY, но допустимо и без него.

В качестве m и n могут выступать любые целочисленные выражения.

При одном аргументе m, удаляются первые m записей.Порядок записей без ORDER BY не определён (случаен).

Замечания:

  • Если m больше общего числа записей в наборе, то весь набор удаляется;

  • Если m = 0, то удаление не происходит;

  • Если m < 0, то выдаётся сообщение об ошибке.

Если указаны аргументы m и n, удаление ограничено количеством записей от m до n, включительно.Нумерация записей начинается с 1.

Замечания по использованию двух аргументов:

  • Если m больше общего числа строк в наборе, ни одна строка не удаляется;

  • Если m > 0 и ⇐ числа строк в наборе, а n вне этих значений, то удаляются строки от m до конца набора;

  • Если m < 1 или n < 1, выдаётся сообщение об ошибке;

  • Если n = m – 1, ни одна запись не удаляется;

  • Если n < m – 1, выдаётся сообщение об ошибке.

Example 1. Использование ORDER BY и ROWS в операторе DELETE

Удаление самой старой покупки

DELETE FROM Purchases ORDER BY ByDate ROWS 1

Удаление заказов для 10 клиентов с самыми большими номерами

DELETE FROM Sales ORDER BY custno DESC ROWS 1 TO 10

Удаляет все записи из sales, поскольку не указано ROWS

DELETE FROM Sales ORDER BY custno DESC

Удаляет одну запись "с конца", т.е.от Z…​

DELETE FROM popgroups ORDER BY name DESC ROWS 1

Удаляет пять самых старых групп

DELETE FROM popgroups ORDER BY formed ROWS 5

Сортировка (ORDER BY) не указана, поэтому будут удалены 8 обнаруженных записей, начиная с пятой.

DELETE FROM popgroups ROWS 5 TO 12

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;

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.