Skip to content

RETURNING RECORD INTO

April 9, 2014

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 these ads

From → PL/SQL

4 Comments
  1. There seems to be a few facets where RETURNING is inadequate.
    And that SET ROW clause can be asking for Trouble.

  2. 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 :)

  3. I’d like to see this being done in the same way as for PostgreSQL:

    INSERT INTO mytable VALUES in_record
    RETURNING * [ INTO out_record ];
    

    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:

    SELECT a, b
    FROM (
        INSERT INTO mytable VALUES in_record
        RETURNING a, b, c
    ) t
    WHERE t.c = 1
    

    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? :-)

  4. Obviously, once SQL gets that esoteric / elegant, you may further continue to support RETURNING also on UPDATE. 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;
    

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 215 other followers

%d bloggers like this: