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?
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 .. RETURNING
construct can be used as a table expression. For instance:What’s also interesting is that this isn’t strictly limited to single-row inserts.
RETURNING
can 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
RETURNING
also onUPDATE
. From the PostgreSQL manual: