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.

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

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

    • 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;
      

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 180 other followers

%d bloggers like this: