FirebirdSQL logo
 COMMENTSОператоры процедурного SQL (PSQL) 
Соединения с явными условиями

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

Довольно часто, это условие — проверка на равенство (или ряд проверок на равенство объединённых оператором AND) использующая оператор "=". Такие соединения называются эквисоединениями.(Примеры в главе Внутренние (INNER) и внешние (OUTER) соединения были эквисоединениями).

Примеры соединений с явными условиями:

/*
 * Выборка всех заказчиков из города Детройт, которые
 * сделали покупку.
 */
SELECT *
FROM customers c
JOIN sales s ON s.cust_id = c.id
WHERE c.city = 'Detroit'

/*
 * Тоже самое, но включает в выборку заказчиков, которые
 * не совершали покупки.
 */
SELECT *
FROM customers c
LEFT JOIN sales s ON s.cust_id = c.id
WHERE c.city = 'Detroit'

/*
 * Для каждого мужчины выбрать женщин, которые выше него.
 * Мужчины, для которых такой женщины не существуют,
 * не будут выключены в выборку.
 */
SELECT
    m.fullname AS man,
    f.fullname AS woman
FROM males m
JOIN females f ON f.height > m.height

/*
 * Выборка всех учеников, их класса и наставника.
 * Ученики без наставника буду включены в выборку.
 * Ученики без класса не будут включены в выборку.
 */
SELECT
    p.firstname,
    p.middlename,
    p.lastname,
    c.name,
    m.name
FROM pupils p
JOIN classes c ON c.id = p.class
LEFT JOIN mentors m ON m.id = p.mentor
Соединения именованными столбцами

Эквисоединения часто сравнивают столбцы, которые имеют одно и то же имя в обеих таблицах.Для таких соединений мы можем использовать второй тип явных соединений, называемый соединением именованными столбцами (Named Columns Joins). Соединение именованными столбцами осуществляются с помощью предложения USING, в котором перечисляются только имена столбцов.

Note

Соединения именованными столбцами доступны только в диалекте 3.

Таким образом, следующий пример:

SELECT *
FROM flotsam f
  JOIN jetsam j
    ON f.sea = j.sea AND f.ship = j.ship

можно переписать так:

SELECT *
FROM flotsam
JOIN jetsam USING (sea, ship)

что значительно короче.Результирующий набор несколько отличается, по крайней мере, при использовании "SELECT *":

  • Результат соединения с явным условием соединения в предложении ON будет содержать каждый из столбцов SEA и `SHIP дважды: один раз для таблицы FLOTSAM и один раз для таблицы JETSAM. Очевидно, что они будут иметь они и те же значения;

  • Результат соединения именованными столбцами, с помощью предложения USING, будет содержать эти столбцы один раз.

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

SELECT f.*, j.*
FROM flotsam f
JOIN jetsam j USING (sea, ship)

Для внешних (OUTER) соединений именованными столбцами, существуют дополнительные нюансы, при использовании “SELECT *” или неполного имени столбца.Если столбец строки из одного источника не имеет совпадений со столбцом строки из другого источника, но все равно должен быть включён результат из-за инструкций LEFT, RIGHT или FULL, то объединяемый столбец получит не NULL значение.Это достаточно справедливо, но теперь вы не можете сказать из какого набора левого, правого или обоих пришло это значение.Это особенно обманывает, когда значения пришли из правой части набора данных, потому что “*” всегда отображает для комбинированных столбцов значения из левой части набора данных, даже если используется RIGHT соединение.

Является ли это проблемой, зависит от ситуации.Если это так, используйте “f.*, j.*” подход, продемонстрированный выше, где f и j имена или алиасы двух источников.Или лучше вообще избегать “*” в серьёзных запросах и перечислять все имена столбцов для соединяемых множеств.Такой подход имеет дополнительное преимущество, заставляя вас думать, о том какие данные вы хотите получить и откуда.

Вся ответственность за совместимость типов столбцов между соединяемыми источниками, имена которых перечислены в предложении USING, лежит на вас.Если типы совместимы, но не равны, то Firebird преобразует их в тип с более широким диапазоном значений перед сравнением.Кроме того, это будет типом данных объединённого столбца, который появится в результирующем наборе, если используются “SELECT *” или неполное имя столбца.Полные имена столбцов всегда будут сохранять свой первоначальный тип данных.

Tip

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

SELECT 1 FROM t1 a JOIN t2 b USING(x) WHERE x = 0;
PLAN JOIN (A NATURAL, B INDEX (RDB$2))

однако

SELECT 1 FROM t1 a JOIN t2 b USING(x) WHERE a.x = 0; -- или 'b.x'
PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

SELECT 1 FROM t1 a JOIN t2 b USING(x) WHERE b.x = 0;
PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

Дело в том, неуточнённый столбец в данном случае неявно заменяется на COALESCE(a.x, b.x).Этот хитрый трюк применяется для устранения неоднозначности имён столбцов, но он же мешает применению индекса.

docnext count = 54

Естественные соединения (NATURAL JOIN)

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

Note

Естественные соединения доступны только в диалекте 3.
Синтаксис
<natural-join> ::= NATURAL [<join-type>] JOIN <source>

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

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

CREATE TABLE TA (
    a BIGINT,
    s VARCHAR(12),
    ins_date DATE
);

CREATE TABLE TB (
    a BIGINT,
    descr VARCHAR(12),
    x FLOAT,
    ins_date DATE
);

Естественное соединение таблиц TA и TB будет происходить по столбцам a и ins_date и два следующих оператора дадут один и тот же результат:

SELECT *
FROM TA
NATURAL JOIN TB;

SELECT *
FROM TA
JOIN TB USING (a, ins_date);

Как и все соединения, естественные соединения являются внутренними соединениями по умолчанию, но вы можете превратить их во внешние соединения, указав LEFT, RIGHT или FULL перед ключевым словом JOIN.

Important
Внимание

Если в двух исходных таблицах не будут найдены одноименные столбцы, то будет выполнен CROSS JOIN.

Перекрёстное соединение (CROSS JOIN)

Перекрёстное соединение или декартово произведение.Каждая строка левой таблицы соединяется с каждой строкой правой таблицы.

Синтаксис
<cross-join> ::=
    <table-reference> [, <table-reference> ...]
  | <table-reference> CROSS JOIN <table-primary>

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

Перекрёстное соединение двух наборов эквивалентно их соединению по условию тавтологии (условие, которое всегда верно).

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

SELECT *
FROM TA
CROSS JOIN TB;

SELECT *
FROM TA
JOIN TB ON 1 = 1;

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

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

SELECT
    m.name,
    s.size,
    c.name
FROM materials m
CROSS JOIN sizes s
CROSS JOIN colors c
Неявные соединения

В стандарте SQL-89 таблицы, участвующие в соединении, задаются списком с разделяющими запятыми в предложении FROM.Условия соединения задаются в предложении WHERE среди других условий поиска.Такие соединения называются неявными.

Синтаксис неявного соединения может осуществлять только внутренние соединения.

Пример неявного соединения:

/*
 * Выборка всех заказчиков из города Детройт, которые
 * сделали покупку.
 */
SELECT *
FROM customers c, sales s
WHERE s.cust_id = c.id AND c.city = 'Detroit'
Important

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

Смешивание явных и неявных соединений не рекомендуется, но позволяется.Некоторые виды смешивания запрещены в Firebird.

Например, такой запрос вызовет ошибку "Column does not belong to referenced table"

SELECT *
FROM
TA, TB
JOIN TC ON TA.COL1 = TC.COL1
WHERE TA.COL2 = TB.COL2

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

SELECT *
FROM
TA, TB
JOIN TC ON TB.COL1 = TC.COL1
WHERE TA.COL2 = TB.COL2

Неоднозначные имена полей в соединениях

Firebird отвергает неполные имена полей в запросе, если эти имена полей существуют в более чем одном наборе данных, участвующих в объединении.Это также верно для внутренних эквисоединений, в которых имена полей фигурируют в предложении ON:

SELECT a, b, c
FROM TA
JOIN TB ON TA.a = TB.a

Существует одно исключение из этого правила: соединения по именованным столбцам и естественные соединения, которые используют неполное имя поля в процессе подбора, могут использоваться законно.Это же относится и к одноименным объединяемым столбцам.Для соединений по именованным столбцам эти столбцы должны быть перечислены в предложении USING.Для естественных соединений это столбцы, имена которых присутствуют в обеих таблицах.Но снова замечу, что, особенно во внешних соединениях, плоское имя colname является не всегда тем же самым что left.colname или right.colname.Типы данных могут отличаться, и один из полных столбцов может иметь значение NULL, в то время как другой нет.В этом случае значение в объединённом, неполном столбце может замаскировать тот факт, что одно из исходных значений отсутствует.

Соединения с хранимыми процедурами

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

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

SELECT *
FROM MY_TAB
JOIN MY_PROC(MY_TAB.F) ON 1 = 1

Запрос же написанный следующим образом вызовет ошибку

SELECT *
FROM MY_PROC(MY_TAB.F)
JOIN MY_TAB ON 1 = 1

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

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

Элемент LATERAL может находиться на верхнем уровне списка FROM или в дереве JOIN.В последнем случае он может также ссылаться на любые элементы в левой части JOIN, справа от которого он находится.

Когда элемент FROM содержит ссылки LATERAL, то запрос выполняется следующим образом: сначала вычисляется значения всех столбцов о которых зависит производная таблица с ключевым словом LATERAL, затем вычисляется сама производная таблица с LATERAL для каждой полученной записи.Результирующие строки полученные из производной таблицы с LATERAL соединяются со строками из которых они получены.

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

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

SELECT
    HORSE.NAME,
    M.BYDATE,
    M.HEIGHT_HORSE,
    M.LENGTH_HORSE
FROM HORSE
CROSS JOIN LATERAL(SELECT
                       *
                   FROM MEASURE
                   WHERE MEASURE.CODE_HORSE = HORSE.CODE_HORSE
                   ORDER BY MEASURE.BYDATE DESC
                   FETCH FIRST ROW ONLY) M

другой вариант написание этого запроса

SELECT
    HORSE.NAME,
    M.BYDATE,
    M.HEIGHT_HORSE,
    M.LENGTH_HORSE
FROM HORSE,
     LATERAL(SELECT
               *
             FROM MEASURE
             WHERE MEASURE.CODE_HORSE = HORSE.CODE_HORSE
             ORDER BY MEASURE.BYDATE DESC
             FETCH FIRST ROW ONLY) M

Если необходимо выводить лошадей, не зависимо есть ли у них хотя бы один промер, то необходимо заменить CROSS JOIN на LEFT JOIN:

SELECT
    HORSE.NAME,
    M.BYDATE,
    M.HEIGHT_HORSE,
    M.LENGTH_HORSE
FROM HORSE
LEFT JOIN LATERAL(SELECT
                       *
                   FROM MEASURE
                   WHERE MEASURE.CODE_HORSE = HORSE.CODE_HORSE
                   ORDER BY MEASURE.BYDATE DESC
                   FETCH FIRST ROW ONLY) M ON TRUE

WHERE

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

Условие в предложении WHERE часто называют условием поиска, выражением поиска или просто поиск.

В DSQL и ESQL, выражение поиска могут содержать параметры.Это полезно, если запрос должен быть повторен несколько раз с разными значениями входных параметров.В строке SQL запроса, передаваемого на сервер, вопросительные знаки используются как заполнители для параметров.Их называют позиционными параметрами, потому что они не могут сказать ничего кроме как о позиции в строке.Библиотеки доступа часто поддерживают именованные параметры в виде :id, :amount, :a и т.д.Это более удобно для пользователя, библиотека заботится о трансляции именованных параметров в позиционные параметры, прежде чем передать запрос на сервер.

Условие поиска может также содержать локальные (PSQL) или хост (ESQL) имена переменных, предваряемых двоеточием.

Синтаксис
SELECT ...
  FROM ...
  [...]
  WHERE <search-condition>
  [...]
Table 1. Параметры предложения WHERE
Параметр Описание

search-condition

Логическое выражение возвращающее TRUE, FALSE и возможно UNKNOWN (NULL).

Только те строки, для которых условие поиска истинно будут включены в результирующий набор.Будьте осторожны с возможными получаемыми значениями NULL: если вы отрицаете выражение, дающее NULL с помощью NOT, то результат такого выражения все равно будет NULL и строка не пройдёт.Это демонстрируется в одном из ниже приведённых примеров.

Примеры
SELECT genus, species
FROM mammals
WHERE family = 'Felidae'
ORDER BY genus;

SELECT *
FROM persons
WHERE birthyear IN (1880, 1881)
   OR birthyear BETWEEN 1891 AND 1898;

SELECT name, street, borough, phone
FROM schools s
WHERE EXISTS (SELECT * FROM pupils p WHERE p.school = s.id)
ORDER BY borough, street;

SELECT *
FROM employees
WHERE salary >= 10000 AND position <> 'Manager';

SELECT name
FROM wrestlers
WHERE region = 'Europe'
  AND weight > ALL (SELECT weight FROM shot_putters
                    WHERE region = 'Africa');

SELECT id, name
FROM players
WHERE team_id = (SELECT id FROM teams
                 WHERE name = 'Buffaloes');

SELECT SUM (population)
FROM towns
WHERE name LIKE '%dam'
  AND province CONTAINING 'land';

SELECT pass
FROM usertable
WHERE username = current_user;

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

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

CHILD MARBLES

Anita

23

Bob E.

12

Chris

<null>

Deirdre

1

Eve

17

Fritz

0

Gerry

21

Hadassah

<null>

Isaac

6

Первое, обратите внимание на разницу между NULL и 0.Известно, что Fritz не имеет шариков вовсе, однако неизвестно количество шариков у Chris и Hadassah.

Теперь, если ввести этот SQL оператор:

SELECT LIST(child) FROM marbletable WHERE marbles > 10

вы получите имена Anita, Bob E., Eve и Gerry.Все эти дети имеют более чем 10 шариков.

Если вы отрицаете выражение:

SELECT LIST(child) FROM marbletable WHERE NOT marbles > 10

запрос вернёт Deirdre, Fritz и Isaac.Chris и Hadassah не будут включены в выборку, так как не известно 10 у них шариков или меньше.Если вы измените последний запрос так:

SELECT LIST(child) FROM  marbletable WHERE marbles <= 100

результат будет тем же самым, поскольку выражение NULL <= 10 даёт UNKNOWN.Это не то же самое что TRUE, поэтому Chris и Hadassah не отображены.Если вы хотите что бы в списке были перечислены все "бедные" дети, то измените запрос следующим образом:

SELECT LIST(child)
FROM marbletable
WHERE marbles <= 10 OR marbles IS NULL

Теперь условие поиска становится истинным для Chris и Hadassah, потому что условие “marbles is null” возвращает TRUE в этом случае.Фактически, условие поиска не может быть NULL ни для одного из них.

Наконец, следующие два примера SELECT запросов с параметрами в условии поиска.Как определяются параметры запроса и возможно ли это, зависит от приложения.Обратите внимание, что запросы подобные этим не могут быть выполнены немедленно, они должны быть предварительно подготовлены.После того как параметризованный запрос был подготовлен, пользователь (или вызывающий код) может подставить значения параметров и выполнить его многократно, подставляя перед каждым вызовом новые значения параметров.Как вводятся значения параметров, и проходят ли они предварительную обработку зависит от приложения.В GUI средах пользователь, как правило, вводит значения параметров через одно и более текстовых полей, и щелкает на кнопку "Execute", "Run" или "Refresh".

SELECT name, address, phone
FROM stores
WHERE city = ? AND class = ?

SELECT *
FROM pants
WHERE model = :model AND size = :size AND color = :col

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

GROUP BY

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

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

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

Синтаксис
SELECT ...
FROM ...
GROUP BY <grouping-item> [, <grouping-item> ...]
[HAVING <grouped-row-condition>] ...

<grouping-item> ::= <non-aggr-select-item> | <non-aggr-expression>

<non-aggr-select-item> ::=
    column-copy
  | column-alias
  | column-position
Table 1. Параметры предложения GROUP BY
Параметр Описание

non-aggr-expression

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

column-copy

Дословная копия выражения из списка выбора, не содержащего агрегатной функции.

column-alias

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

column-position

Номер позиции выражения (столбца) из списка выбора, не содержащего агрегатной функции.

Общее правило гласит, что каждый не агрегированный столбец в SELECT списке, должен быть так же включён в GROUP BY список.Вы можете это сделать тремя способами:

  1. Копировать выражение дословно из списка выбора, например “class” или “'D:' || upper(doccode)”;

  2. Указать псевдоним, если он существует;

  3. Задать положение столбца в виде целого числа, которое находится в диапазоне от 1 до количества столбцов в списке SELECT. Целые значения, полученные из выражений, параметров или просто инварианты будут использоваться в качестве таковых в группировке. Они не будут иметь никакого эффекта, поскольку их значение одинаково для каждой строки.

Important

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

В дополнении к требуемым элементам, список группировки так же может содержать:

  • Столбцы исходной таблицы, которые не включены в список выборки SELECT, или неагрегатные выражения, основанные на таких столбцах. Добавление таких столбцов может дополнительно разбить группы. Но так как эти столбцы не в списке выборки SELECT, вы не можете сказать, какому значению столбца соответствует значение агрегированной строки. Таким образом, если вы заинтересованы в этой информации, вы так же должны включить этот столбец или выражение в список выборки SELECT, что возвращает вас к правилу "каждый не агрегированный столбце в списке выборки SELECT должен быть включён в список группировки `GROUP BY`";

  • Выражения, которые не зависят от данных из основного набора, т.е. константы, контекстные переменные, некоррелированные подзапросы, возвращающие единственное значение и т.д. Это упоминается только для полноты картины, т.к. добавление этих элементов является абсолютно бессмысленным, поскольку они не повлияют на группировку вообще. "Безвредные, но бесполезные" элементы так же могут фигурировать в списке выбора SELECT без их копирования в список группировки GROUP BY.

Примеры

Когда в списке выбора SELECT содержатся только агрегатные столбцы, предложение GROUP BY необязательно:

SELECT COUNT(*), AVG(age)
FROM students
WHERE sex = 'M'

Этот запрос вернёт одну строку с указанием количества студентов мужского пола и их средний возраст.Добавление выражения, которое не зависит от строк таблицы STUDENTS, ничего не меняет:

SELECT COUNT(*), AVG(age), current_date
FROM students
WHERE sex = 'M'

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

Тем не менее в обоих приведённых выше примерах это разрешено.Это совершенно справедливо и для запроса:

SELECT COUNT(*), AVG(age)
FROM students
WHERE sex = 'M'
GROUP BY class

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

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

SELECT class, COUNT(*), AVG(age)
FROM students
WHERE sex = 'M'
GROUP BY class

Теперь у нас есть полезный запрос.Обратите внимание, что добавление столбца CLASS делает предложение GROUP BY обязательным.Мы не можем удалить это предложение, так же мы не можем удалить столбец CLASS из списка столбцов.

Результат последнего запроса будет выглядеть примерно так:

CLASS COUNT AVG

2A

12

13.5

2B

9

13.9

3A

11

14.6

3B

12

14.4

…​

…​

…​

Заголовки “COUNT” и “AVG” не очень информативны.В простейшем случае вы можете обойти это, но лучше, если мы дадим им значимые имена с помощью псевдонимов:

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M'
GROUP BY class

Как вы помните из формального синтаксиса списка столбцов, ключевое слово AS не является обязательным.

Добавление большего не агрегированных (или точнее строчно зависимых) столбцов требуется добавления их в предложения GROUP BY тоже.Например, вы хотите видеть вышеуказанную информацию о девочках то же, и хотите видеть разницу между интернатами и студентами дневного отделения:

SELECT
    class,
    sex,
    boarding_type,
    COUNT(*) AS anumber,
    AVG(age) AS avg_age
FROM students
GROUP BY class, sex, boarding_type
CLASS SEX BOARDING_TYPE ANUMBER AVG_AGE

2A

F

BOARDING

9

13.3

2A

F

DAY

6

13.5

2A

M

BOARDING

7

13.6

2A

M

DAY

5

13.4

2B

F

BOARDING

11

13.7

2B

F

DAY

5

13.7

2B

M

BOARDING

6

13.8

…​

…​

…​

…​

…​

Каждая строка в результирующем наборе соответствует одной конкретной комбинации переменных CLASS, SEX и BOARDING_TYPE.Агрегированные результаты — количество и средний возраст — приведены для каждой из конкретизированной группы отдельно.В результате запроса вы не можете увидеть обобщённые результаты для мальчиков отдельно или для студентов дневного отделения отдельно.Таким образом, вы должны найти компромисс.Чем больше вы добавляете неагрегатных столбцов, тем больше вы конкретизируете группы, и тем больше вы упускаете общую картину из виду.Конечно, вы все ещё можете получить “большие” агрегаты, с помощью отдельных запросов.

HAVING

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

Условие(я) в предложении HAVING может ссылаться на:

  • Любой агрегированный столбец в списке выбора SELECT. Это наиболее широко используемый случай;

  • Любое агрегированное выражение, которое не находится в списке выбора SELECT, но разрешено в контексте запроса. Иногда это полезно;

  • Любой столбец в списке GROUP BY. Однако более эффективно фильтровать не агрегированные данные на более ранней стадии в предложении WHERE;

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

Предложение HAVING не может содержать:

  • Не агрегированные выражения столбца, которые не находятся в списке GROUP BY;

  • Позицию столбца. Целое число в предложении HAVING – просто целое число;

  • Псевдонимы столбца –- даже если они появляются в предложении GROUP BY.

Примеры

Перестроим наши ранние примеры.Мы можем использовать предложение HAVING для исключения малых групп студентов:

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M'
GROUP BY class
HAVING COUNT(*) >= 5

Выберем только группы, которые имеют минимальный разброс по возрасту 1.2 года:

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M'
GROUP BY class
HAVING MAX(age) - MIN(age) > 1.2

Обратите внимание, что если вас действительно интересует эта информация, то неплохо бы включить в список выбора min(age) и max(age) или выражение max(age) – min(age).

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

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M'
GROUP BY class
HAVING class STARTING WITH '3'

Однако гораздо лучше переместить это условие в предложение WHERE:

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M' AND class STARTING WITH '3'
GROUP BY class

WINDOW

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

Синтаксис
<query spec> ::=
  SELECT
    [<first clause>] [<skip clause>]
    [<distinct clause>]
    <select list>
    <from clause>
    [<where clause>]
    [<group clause>]
    [<having clause>]
    [<named windows clause>]
    [<order clause>]
    [<rows clause>]
    [<offset clause>] [<limit clause>]
    [<plan clause>]

<named windows clause> ::=
  WINDOW <window definition> [, <window definition>] ...

<window definition> ::=
  window-name AS <window specification>

<window specification> ::=
   ([window-name] [<window partition>] [<window order>] [<window frame>])


<window partition> ::= PARTITION BY <expr> [, <expr> ...]

<window order> ::=
  ORDER BY <expr> [<direction>] [<nulls placement>]
        [, <expr> [<direction>] [<nulls placement>] ...]

<direction> ::= {ASC | DESC}

<nulls placement> ::= NULLS {FIRST | LAST}

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

<window frame extent> ::=
  <window frame preceding> | <window frame between>

<window frame preceding> ::=
  UNBOUNDED PRECEDING | <expr> PRECEDING | CURRENT ROW

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

Имя окна может быть использовано в предложении OVER для ссылки на определение окна, кроме того оно может бытьиспользовано в качестве базового окна для другого именованного или встроенного (в предложении OVER) окна.Окна с рамкой (с предложениями RANGE и ROWS) не могут быть использованы в качестве базового окна, но могут бытьиспользованы в предложении OVER window_name. Окно, которое использует ссылку на базовое окно, не может иметь предложение PARTITION BY и не может переопределять сортировку с помощью предложения ORDER BY.

Примеры
Example 1. Использование именованных окон
SELECT
    id,
    department,
    salary,
    count(*) OVER w1,
    first_value(salary) OVER w2,
    last_value(salary) OVER w2,
    sum(salary) over (w2 ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS s
FROM employee
WINDOW w1 AS (PARTITION BY department),
       w2 AS (w1 ORDER BY salary)
ORDER BY department, salary;
См. также:

Оконные (аналитические) функции.

PLAN

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

Синтаксис
PLAN <plan-expr>

<plan-expr> ::=
    (<plan-item> [, <plan-item> ...])
  | <sorted-item>
  | <joined-item>
  | <merged-item>
  | <hash-item>

<sorted-item> ::= SORT (<plan-item>)

<joined-item> ::= JOIN (<plan-item>, <plan-item> [, <plan-item> ...])

<merged-item> ::=
  [SORT] MERGE (<sorted-item>, <sorted-item> [, <sorted-item> ...])

<hash-item> ::= HASH (<plan-item>, <plan-item> [, <plan-item> ...])

<plan-item> ::= <basic-item> | <plan-expr>

<basic-item> ::= <relation> {
    NATURAL
  | INDEX (<indexlist>)
  | ORDER index [INDEX (<indexlist>)]
}

<relation> ::= table | view [table]

<indexlist> ::= index [, index ...]
Table 1. Параметры предложения PLAN
Параметр Описание

table

Имя таблицы или её алиас.

view

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

index

Имя индекса.

Каждый раз, когда пользователь отправляет запрос ядру Firebird, оптимизатор вычисляет стратегию извлечения данных.Большинство клиентов Firebird имеют возможность отобразить пользователю план извлечения данных.В собственном инструменте isql это делается с помощью команды SET PLAN ON.Если вы хотите только изучить план запроса без его выполнения, то вам необходимо ввести команду SET PLANONLY ON, после чего будут извлекаться планы запросов без их выполнения.Для возврата isql в режим выполнения запросов введите команду SET PLANONLY OFF.

Note

Более подробный план можно получить при включении расширенного плана.В isql это делается с помощью команды SET EXPLAIN ON.Этот план выводит более подробную информацию о методах доступа используемых оптимизатором, однако его нельзя включить в запрос.Описание расширенного плана выходит за рамки данного руководства.

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

Простые планы

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

SELECT * FROM students
PLAN (students NATURAL)

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

Select Expression
  -> Table "STUDENTS" Full Scan

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

SELECT *
FROM students
WHERE class = '3C'
PLAN (students INDEX (ix_stud_class))

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

Select Expression
  -> Filter
      -> Table "STUDENTS" Access By ID
          -> Bitmap
              -> Index "IX_STUD_CLASS" Range Scan (full match)

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

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

SELECT *
FROM students
PLAN (students ORDER pk_students)
ORDER BY id

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

Select Expression
  -> Table "STUDENTS" Access By ID
      -> Index "PK_STUDENTS" Full Scan

Инструкции ORDER и INDEX могут быть объединены:

SELECT *
FROM students
WHERE class >= '3'
PLAN (students ORDER pk_students INDEX (ix_stud_class))
ORDER BY id

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

Select Expression
  -> Filter
      -> Table "STUDENTS" Access By ID
          -> Index "PK_STUDENTS" Full Scan
              -> Bitmap
                  -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

В инструкциях ORDER и INDEX разрешено указывать один и тот же индекс:

SELECT *
FROM students
WHERE class >= '3'
PLAN (students ORDER ix_stud_class INDEX (ix_stud_class))
ORDER BY class

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

Select Expression
  -> Filter
      -> Table "STUDENTS" Access By ID
          -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)
              -> Bitmap
                  -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

Для сортировки наборов данных, когда невозможно использовать индекс (или вы хотите подавить его использование), уберите инструкцию ORDER и предварите выражение плана инструкцией SORT:

SELECT *
FROM students
PLAN SORT (students NATURAL)
ORDER BY name

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

Select Expression
  -> Sort (record length: 128, key length: 56)
      -> Table "STUDENTS" Full Scan

Или когда индекс используется для поиска:

SELECT *
FROM students
WHERE class >= '3'
PLAN SORT (students INDEX (ix_stud_class))
ORDER BY name

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

Select Expression
  -> Sort (record length: 136, key length: 56)
      -> Filter
          -> Table "STUDENTS" Access By ID
              -> Bitmap
                  -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

Обратите внимание, что инструкция SORT, в отличие от ORDER, находится за пределами скобок.Это отражает тот факт, что строки данных извлекаются неотсортированными и сортируются впоследствии.

При выборке из представления указывается само представление и участвующее в нем таблица.Например, если у вас есть представление FRESHMEN, которое выбирает только студентов первокурсников:

SELECT *
FROM freshmen
PLAN (freshmen students NATURAL)

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

Select Expression
  -> Table "STUDENTS" as "FRESHMEN" Full Scan

Или, например:

SELECT *
FROM freshmen
WHERE id > 10
PLAN SORT (freshmen students INDEX (pk_students))
ORDER BY name DESC

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

Select Expression
  -> Sort (record length: 144, key length: 24)
      -> Filter
          -> Table "STUDENTS" as "FRESHMEN" Access By ID
              -> Bitmap
                  -> Index "PK_STUDENTS" Range Scan (lower bound: 1/1)

Обратите внимание: если вы назначили псевдоним таблице или представлению, то в предложении PLAN необходимо использовать псевдоним, а не оригинальное имя.

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

Если вы делаете соединение, то вы можете указать индекс, который будет использоваться для сопоставления.Кроме того, вы должны использовать директиву 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
);

Полный синтаксис 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.