January 20, 2004: Oracle Returning clause

Since Oracle 8i (and maybe already in version 8) Oracle offers 'interaction' functionality on dml statements (like insert, delete etc.) by means of the returning clause. This returning clause delivers 'information' from the affected records. For example:


declare
  t_name    varchar2(100);
  t_sal        number;
begin
  update    emp
  set         sal=sal*2
  where     empno=7900
  returning ename, sal into t_name, t_sal;
  dbms_output.put_line (t_name || ' - ' || t_sal);
end;
/

This works fine when the dml affects only one record. When more records are affected one is to use collections and bulk collects. A slight complication is that the collection types cannot be declared locally. For example:

create or replace type vclist is varray(100) of varchar2(50);
create or replace type nlist is varray(100) of number;
declare
  t_ns        nlist;
  t_names     vclist;
begin
  update  emp2
  set       sal = sal * 2
  where   deptno = 30 
  returning ename, sal bulk collect into t_names, t_ns;
  for i in t_names.first..t_names.last loop
    dbms_output.put_line (t_names (i) || ' - ' || t_ns(i));
  end loop;
end;
/

Often it is only necessary to know the number of affected records: SQL%ROWCOUNT will give you the answer.
The returning clause is especially handy when a record is automatically provided with its id by means of a trigger and a sequence:

  insert into [table] ([columns]) values ([values]) 
  returning id into t_id;

Often this functionality is not known by java developers (and probably by developers for other languages as well) and results in code to first retrieve the id and then use this id in the insert statement; 'hoping' that the trigger is intelligent enough not to overwrite this id.
Posted by Aino at 11:25