The PL/SQL language has the useful RETURNING clause, which can be added to most DML statements:
DECLARE
v_result tab.id%TYPE;
BEGIN
INSERT INTO tab (col)
VALUES (123)
RETURNING id INTO v_result;
END;
/
It would be really useful if the SQL language also supported this clause, exactly like PostgreSQL and Firebird do as well:
INSERT INTO tab (col)
VALUES (123)
RETURNING id
This suggestion is one option to implement the desired feature. It would make the SQL and PL/SQL languages a bit more consistent. A much better option, of course, would be to implement the SQL standard (which is supported by DB2). In that case, the following syntax could be used:
SELECT *
FROM FINAL TABLE (
INSERT INTO tab (col)
VALUES (123)
)
That syntax is very powerful, as the results of several DML operations can be joined or otherwise combined (e.g. with unions). The clause is called <data change delta table> in the SQL:2016 standard, and is part of the Feature T495, “Combined data change and retrieval”