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.