RETURNING RECORD INTO
This is an idea for an enhancement to the PL/SQL syntax.
If I have the following declaration:
DECLARE in_record mytable%ROWTYPE; out_record mytable%ROWTYPE; BEGIN
I can do this:
INSERT INTO mytable VALUES in_record;
I can also do this:
UPDATE mytable SET ROW = in_record WHERE ...;
I can do this, as long as I list each and every column, in the right order:
INSERT INTO mytable VALUES in_record RETURNING cola, colb, colc INTO out_record;
But I can’t do this:
INSERT INTO mytable VALUES in_record RETURNING ROW INTO out_record;
Can we make this happen, Oracle?
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
Scott
9 April 2014 - 1:06 pm
There seems to be a few facets where RETURNING is inadequate.
And that SET ROW clause can be asking for Trouble.
Jeffrey Kemp
9 April 2014 - 1:20 pm
There are some caveats, but for simple tables with no virtual columns it’s fine – the savings in code maintenance are too great to ignore 🙂
lukaseder
9 April 2014 - 2:09 pm
I’d like to see this being done in the same way as for PostgreSQL:
See: http://www.postgresql.org/docs/9.3/static/sql-insert.html
And while we’re at it, it would be great if this weren’t just a PL/SQL feature, but a regular SQL feature. In PostgreSQL, any
INSERT .. RETURNINGconstruct can be used as a table expression. For instance:SELECT a, b FROM ( INSERT INTO mytable VALUES in_record RETURNING a, b, c ) t WHERE t.c = 1What’s also interesting is that this isn’t strictly limited to single-row inserts.
RETURNINGcan also yield a complete result set.How does all that sound, Oracle? 🙂
lukaseder
9 April 2014 - 2:11 pm
Obviously, once SQL gets that esoteric / elegant, you may further continue to support
RETURNINGalso onUPDATE. From the PostgreSQL manual:WITH upd AS ( UPDATE employees SET sales_count = sales_count + 1 WHERE id = (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation') RETURNING * ) INSERT INTO employees_log SELECT *, current_timestamp FROM upd;