The RETURNING INTO clause is a very convenient way to retrieve values from rows that are being inserted/deleted/updated. When used with UPDATE and DELETE statements, it’s possible to retrieve values of multiple rows by using the RETURNING BULK COLLECT INTO. This is something that does not work with the INSERT statements: in that case you will be able to only return values from a single row.
Let’s see how this works and create a simple test table that will be the target of our DML operations
|
1 2 3 4 5 6 7 8 9 |
create table mytab(id number(3), run_date date, cust_id varchar2(30)); insert into mytab (select rownum, date '2016-01-01' + round(dbms_random.value(0, 30)), 'CLI'||round(dbms_random.value(100, 999))||upper(dbms_random.string('A', 3)) from dual connect by level <= 100); commit; |
First of all let’s use a simple case just to have a look at how this works. Let’s update one row and retrieve rowid and run_date of the row just updated
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> declare 2 vRowid rowid; 3 vRunDate date; 4 begin 5 update mytab 6 set cust_id = 'RUN1' 7 where id = 1 8 returning rowid, run_date into vRowid, vRunDate; 9 10 dbms_output.put_line(vRowid); 11 dbms_output.put_line(vRunDate); 12 commit; 13 end; 14 / AAAb5CAAEAAAAzTAAA 10-JAN-16 PL/SQL procedure successfully completed |
As you can see from the output, we retrieved rowid and run_date through the RETURNING INTO clause. The same identical thing can be done with DELETE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> declare 2 vRowid rowid; 3 vRunDate date; 4 begin 5 delete mytab 6 where id = 1 7 returning rowid, run_date into vRowid, vRunDate; 8 9 dbms_output.put_line(vRowid); 10 dbms_output.put_line(vRunDate); 11 commit; 12 end; 13 / AAAb5CAAEAAAAzTAAA 10-JAN-16 PL/SQL procedure successfully completed |
and INSERT
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> declare 2 vRowid rowid; 3 vRunDate date; 4 begin 5 insert into mytab values(0, 6 date '2016-01-01' + round(dbms_random.value(0, 30)), 7 'CLI'||round(dbms_random.value(100, 999))||upper(dbms_random.string('A', 3))) 8 returning rowid, run_date into vRowid, vRunDate; 9 10 dbms_output.put_line(vRowid); 11 dbms_output.put_line(vRunDate); 12 commit; 13 end; 14 / AAAb5CAAEAAAAzTABk 27-JAN-16 PL/SQL procedure successfully completed |
Now imagine this scenario: after executing an update that involves multiple rows, you need to execute another operation on the same recordset. It would be handy to have the rowids of those records without the need to query again the database. Here is where the RETURNING INTO clause becomes really useful. It’s possible, in fact, to retrieve data from a multi-row operation by specifying BULK COLLECT
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
SQL> declare 2 type tt_rowids is table of rowid index by binary_integer; 3 vIds tt_rowids; 4 begin 5 update mytab 6 set cust_id = 'RUN2' 7 where id between 2 and 10 8 returning rowid bulk collect into vIds; 9 10 for i in vIds.first..vIds.last loop 11 dbms_output.put_line('ROWID of updated row '||i||' is '||vIds(i)); 12 end loop; 13 commit; 14 end; 15 / ROWID of updated row 1 is AAAb5CAAEAAAAzTAAB ROWID of updated row 2 is AAAb5CAAEAAAAzTAAC ROWID of updated row 3 is AAAb5CAAEAAAAzTAAD ROWID of updated row 4 is AAAb5CAAEAAAAzTAAE ROWID of updated row 5 is AAAb5CAAEAAAAzTAAF ROWID of updated row 6 is AAAb5CAAEAAAAzTAAG ROWID of updated row 7 is AAAb5CAAEAAAAzTAAH ROWID of updated row 8 is AAAb5CAAEAAAAzTAAI ROWID of updated row 9 is AAAb5CAAEAAAAzTAAJ PL/SQL procedure successfully completed |
The RETURNING INTO BULK COLLECT operation is possible with both UPDATE and DELETE statements but not with multi-row inserts (insert….select). If you try to execute it you simply get an exception
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL> declare 2 type tt_rowids is table of rowid index by binary_integer; 3 vIds tt_rowids; 4 begin 5 insert into mytab 6 (select rownum, date '2016-01-01' + round(dbms_random.value(0, 30)), 'CLI'||round(dbms_random.value(100, 999))||upper(dbms_random.string('A', 3)) 7 from dual 8 connect by level <= 10) 9 returning rowid bulk collect into vIds; 10 11 for i in vIds.first..vIds.last loop 12 dbms_output.put_line('ROWID of inserted row '||i||' is '||vIds(i)); 13 end loop; 14 commit; 15 end; 16 / ORA-06550: line 8, column 29: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 5, column 1: PL/SQL: SQL Statement ignored |
How can we overcome this limitation in case we want to use the RETURNING INTO clause in a multi-row insert? There is a very interesting solution that goes through the use of a FORALL statement.
If we first insert our data into a collection like a Pl/Sql table and then call the insert statement that will be executed inside a FORALL so that we are able to return all the values in our collection
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
SQL> declare 2 type tt_mytab is table of mytab%rowtype 3 index by binary_integer; 4 5 type tt_rowids is table of rowid 6 index by binary_integer; 7 8 vMyTab tt_mytab; 9 vIds tt_rowids; 10 begin 11 select rownum, date '2016-01-01' + round(dbms_random.value(0, 30)), 'CLI'||round(dbms_random.value(100, 999))||upper(dbms_random.string('A', 3)) 12 bulk collect into vMyTab 13 from dual 14 connect by level <= 10; 15 16 forall i in vMyTab.first..vMyTab.last 17 insert into mytab values vMyTab(i) 18 returning rowid bulk collect into vIds; 19 20 for i in vIds.first..vIds.last loop 21 dbms_output.put_line('ROWID of inserted row '||i||' is '||vIds(i)); 22 end loop; 23 commit; 24 end; 25 / ROWID of inserted row 1 is AAAb5CAAEAAAAzTABm ROWID of inserted row 2 is AAAb5CAAEAAAAzTABn ROWID of inserted row 3 is AAAb5CAAEAAAAzTABo ROWID of inserted row 4 is AAAb5CAAEAAAAzTABp ROWID of inserted row 5 is AAAb5CAAEAAAAzTABq ROWID of inserted row 6 is AAAb5CAAEAAAAzTABr ROWID of inserted row 7 is AAAb5CAAEAAAAzTABs ROWID of inserted row 8 is AAAb5CAAEAAAAzTABt ROWID of inserted row 9 is AAAb5CAAEAAAAzTABu ROWID of inserted row 10 is AAAb5CAAEAAAAzTABv PL/SQL procedure successfully completed |
So how does this work? The trick lies inside the nature of FORALL. Keep in mind that FORALL is not a loop but a declarative statement. It tells Oracle to execute the DML for all the provided values but with a single context switch. Basically, the engine generates all the inserts and executes them all in one single “shot”, without switching context between Pl/Sql and Sql for every single insert like a normal loop would do.
This perfectly serves our purpose of retrieving the rowids of the newly inserted rows.
Fantastic!
