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

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

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

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

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

docnext count = 47

`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

CHILD

MARBLES

Anita

Bob E.

Chris

<null>

Deirdre

Eve

Fritz

Gerry

Hadassah

<null>

Isaac

Первое, обратите внимание на разницу между 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 список.Вы можете это сделать тремя способами:

Копировать выражение дословно из списка выбора, например “class” или “'D:' || upper(doccode)”;
Указать псевдоним, если он существует;
Задать положение столбца в виде целого числа, которое находится в диапазоне от 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

ANUMBER

AVG_AGE

BOARDING

13.3

DAY

13.5

BOARDING

13.6

DAY

13.4

BOARDING

13.7

DAY

13.7

BOARDING

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

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

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

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

Если источником данных является столбец 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 могут быть изменены, если:

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

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

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

Если источником данных является столбец типа 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`

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

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 заставляет движок пропускать записи, заблокированные другими транзакциями, вместо того,чтобы ждать или вызывать ошибки при конфликте.

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

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.

Соединения С Хранимыми Процедурами

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

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

docnext count = 47

WHERE

Примеры

GROUP BY

Примеры

HAVING

Примеры

WINDOW

Примеры

PLAN

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

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

UNION

Примеры

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

INSERT

INSERT …​ VALUES

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

INSERT …​ SELECT

INSERT …​ DEFAULT VALUES

Директива OVERRIDING

RETURNING

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

UPDATE

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

Примеры

SET

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

WHERE

PLAN

ORDER BY и ROWS

SKIP LOCKED

RETURNING

INTO

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

UPDATE OR INSERT

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

RETURNING

DELETE

WHERE

PLAN

ORDER BY и ROWS

SKIP LOCKED

RETURNING

MERGE

WHEN MATCHED

WHEN NOT MATCHED [BY TARGET]

WHEN NOT MATCHED BY SOURCE

Примеры

RETURNING

EXECUTE PROCEDURE

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

EXECUTE BLOCK

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

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

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

`WHERE`

`GROUP BY`

`WINDOW`

`PLAN`

`UNION`

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

`INSERT`

`INSERT … VALUES`

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

`INSERT … SELECT`

`INSERT … DEFAULT VALUES`

Директива `OVERRIDING`

`RETURNING`

`UPDATE`

`SET`

`WHERE`

`PLAN`

`ORDER BY` и `ROWS`

`SKIP LOCKED`

`RETURNING`

`UPDATE OR INSERT`

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

`RETURNING`

`DELETE`

`WHERE`

`PLAN`

`ORDER BY` и `ROWS`

`SKIP LOCKED`

`RETURNING`

`MERGE`

`WHEN MATCHED`

`WHEN NOT MATCHED [BY TARGET]`

`WHEN NOT MATCHED BY SOURCE`

`Примеры`

`RETURNING`

`EXECUTE PROCEDURE`

`EXECUTE BLOCK`