День семьсот шестьдесят второй. #ЗаметкиНаПолях #SQL
На третьем году существования канала я понял, что есть один язык, который я незаслуженно обходил вниманием. Восполняю упущение. Начинаю новую серию постов про SQL. Думаю, что основные основы читателям моего канала объяснять не надо (если надо, пишите в комментариях, что интересно), поэтому буду писать о «фишках». Проблема в том, что они различаются как по наличию в разных СУБД, так и по синтаксису. Поэтому буду стараться выбирать более-менее универсальные вещи и описывать концепцию, а не особенности синтаксиса.

Возвращение Данных из DML Запроса
Поначалу это может показаться странным, но возможность возвращать данные из DML - очень полезная функция.
- В запросе INSERT:
Наиболее распространённый вариант использования - получение автоматически сгенерированных значений. Например, ключей для создания записей в дочерних таблицах. Кроме того, возвращённые таким образом данные могут использоваться для аудита или логирования запросов.
- В запросе UPDATE:
Здесь выражение это используется не так часто, но всё же возможно его применение для получения значений по умолчанию, заданных в базе данных, либо для аудита запросов.
- В запросе DELETE:
Совсем редкий случай использования, в основном для целей аудита, либо, возможно, переноса данных в архивную таблицу, хотя так лучше не делать.

СУБД: Oracle, PostgreSQL, MariaDB
Выражение: RETURNING
Синтаксис:

INSERT INTO | UPDATE | DELETE FROM table …
RETURNING expression1 [INTO variable1]
[, expression2 [INTO variable2]] …;

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

my_row mytable%ROWTYPE;
…
RETURNING * INTO my_row;

Пример:

INSERT INTO users (firstname, lastname) 
VALUES ('Joe', 'Cool') RETURNING id;

СУБД: MsSQL
Выражение: OUTPUT
Синтаксис:

INSERT INTO | UPDATE | DELETE FROM table …
OUTPUT INSERTED.expression1 [INTO variable1]
[, DELETED.expression2 [INTO variable2]] …;

В MsSQL также можно обратиться к псевдотаблицам INSERTED и DELETED. В запросе UPDATE – DELETED представляет заменяемое значение, INSERTED представляет новое значение поля. В запросах INSERT и DELETE используется соответствующая псевдотаблица.

Пример:

DELETE Sales.ShoppingCartItem
OUTPUT DELETED.*;

Источники:
- https://app.pluralsight.com/library/courses/postgresql-data-manipulation-playbook/
- https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql