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?

Grassroots on APEX 5
Too Much Validation is Too Much

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

Your email address will not be published / Required fields are marked *