Получение в программе значения первичного ключа после INSERT

    Часто бывает, что после добавления строки в таблицу необходимо получить сгенерированное в СУБД значение первичного ключа. Например, это необходимо для последующей вставки detail-данных. Самый "оригинальный" способ, что я видел: стартуем транзакцию, вызываем INSERT, делаем запрос "select MAX(ID) from table", вставляем detail-данные, коммитим... Но мы пойдем другим путем – без дополнительных запросов к базе данных. Мы воспользуемся вызовом оператора INSERT с модификатором RETURNING. Вопрос только в том, как получить в программе значение из RETURNING?
    Первый вариант – это делать добавление строки в таблицу используя функцию или процедуру, и возвращать программе значение первичного ключа вставленной строки в качестве результата функции или параметра процедуры. Мне такой вариант нравится – напоминает инкапсуляцию в ООП, когда все данные доступны пользователю только через методы объекта, а пользователь даже не догадывается о реальной структуре хранения данных.
    Второй вариант – воспользоваться возможностями библиотеки для доступа к базам данных. Рассмотрим этот вариант на примере Devart Universal Data Access Components (UniDAC).

1. Oracle
Создаем тестовую таблицу и сиквенс (не у всех есть Oracle 12c с IDENTITY) для генерации уникальных значений первичного ключа:

create table test 
(
  id number(10),
  t  varchar(100)
);
create sequence test_id;

Так как в Oracle при использовании "RETURNING" указывается куда поместить результат, то логично поместить его в параметр:

q := TUniQuery.Create(nil);
q.Connection := dbOra;
q.SQL.Text := 'insert into test(id, t) ' +
              'values (test_id.nextval, ''test'') ' +
              'returning id into :id';
q.Params.Add;
q.Params[0].Name := 'id';
q.Params[0].ParamType := ptResult;
q.Params[0].DataType  := ftInteger;
q.Execute;
Writeln(q.Params[0].Name, ' ', q.Params[0].AsString);

2. PostgreSQL
Создаем тестовую таблицу со столбцом типа SERIAL, который будет генерировать нам уникальные идентификаторы для первичного ключа:

create table public.test 
(
  id serial,
  t  varchar(100)
);

В предыдущем примере меняем connection на подключение к PostgreSQL, а из теста запроса для вставки убираем ID и получение значения из сиквенса:

q.SQL.Text := 'insert into test(t) values (''test'') returning id into :id';

Запускаем программу и получаем:

Project XYZ.exe raised exception class EPgError with message 'syntax error at or near "INTO"'

То есть как с Oracle сделать не получилось, т.к. в синтаксисе RETURNING у PostgreSQL нет INTO, и он просто возвращает поля аналогично SELECT.
    Среди свойств TUniQuery я заметил свойство LastInsertId. Звучит многообещающе. Убираем из запроса "id into :id" и запускаем:

q := TUniQuery.Create(nil);
q.Connection := dbPG;
q.SQL.Text := 'insert into test(t) values (''test'') returning id';
q.Execute;
Writeln(q. LastInsertId);

В результате вместо 1 получаю 0. Глюк? Читаю справку, и понимаю, что LastInsertId мне не поможет. Оказалось, что для PostgreSQL он возвращает значение OID (Object Identifier) если при создании для таблицы указывается "WITH OIDS" или включён параметр конфигурации "default_with_oids".
    Что же делать? Мысль о том, что в PostgreSQL RETURNING возвращает поля аналогично SELECT, наводит на решение – проверить наличие полей после выполнения TUniQuery:

q := TUniQuery.Create(nil);
q.Connection := dbPG;
q.SQL.Text := 'insert into test(t) values (''test'') returning id';
q.Execute;
Writeln(q.Fields[0].FieldName, ' ', q.Fields[0].AsString);

В результате FieldByName('id') после запуска TUniQuery содержит значение ID вставленной строки.

3. Firebird/InterBase
Создаем тестовую таблицу и генератор (или сиквенс, как у Oracle) для генерации значения первичного ключа:

create table test 
(
  id integer,
  t  varchar(100)
);
create generator test_id;

В синтаксисе RETURNING у Firebird, как у PostgreSQL, нет INTO, поэтому по аналогии с PostgreSQL после запуска запроса на вставку проверяем нулевое поле:

q := TUniQuery.Create(nil);
q.Connection := dbFB;
q.SQL.Text := 'insert into test(id, t) ' +
              'values (gen_id(test_id, 1), ''test'') ' +
              'returning id';
q.Execute;
Writeln(q.Fields[0].FieldName, ' ', q.Fields[0].AsString);

и вместо результата получаем ошибку:

Project XYZ.exe raised exception class EDatabaseError with message 'List index out of bounds (0)'

Оказывается, что UniDAC для InterBase /Firebird, как для Oracle, возвращает результат RETURNING в параметры TUniQuery:

q := TUniQuery.Create(nil);
q.Connection := dbFB;
q.SQL.Text := 'insert into test(id, t) ' +
              'values (gen_id(test_id, 1), ''test'') ' +
              'returning id';
q.Execute;
Writeln(q.Params[0].Name, ' ', q.Params[0].AsString);

UniDAC даже сам создает результирующий параметр.

4. MS SQL Server
Создаем тестовую таблицу со столбцом типа IDENTITY, который будет генерировать нам уникальные идентификаторы для первичного ключа:

create table test 
(
  id integer identity,
  t  varchar(100)
);

У MS SQL Server нет RETURNING! Что делать? Классическое решение – это после INSERT запросом "SELECT SCOPE_IDENTITY()" получить значение последнего введенного идентификатора в данной сессии. Но мы ищем решение без дополнительных запросов, поэтому, как я писал выше, мы пойдем другим путем. При операциях DML SQL Server автоматически создает временные таблицы INSERTED и DELETED, которые используются обычно в триггерах. Попробуем использовать временную таблицу INSERTED и мы:

q := TUniQuery.Create(nil);
q.Connection := dbMS;
q.SQL.Text := 'insert into test(t) output INSERTED.ID values (''test'')';
q.Execute;
Writeln(q.Fields[0].FieldName, ' ', q.Fields[0].AsString);

И снова верный результат. TUniQuery, как для PostgreSQL, вернул значение ID вставленной строки в виде поля.

Других СУБД у меня сейчас под рукой нет, но я думаю, что в TUniQuery для любой СУБД можно получить значение первичного ключа после вставки в таблицу новой строки – или как поле или как параметр.

---

21.10.2021 FireDAC vs UniDAC. Получение значения первичного ключа новой строки.
28.07.2023 Новый способ получения OUTPUT-значения после DML операции MS SQL Server в FireDAC Delphi 12.