RETURNING INTO

While doing a quality check on new code, I encountered some row by row processing. This always triggers me to make a remark about rewriting the code to a single SQL, or at least bulk processing. But in this case, inside the loop, several local variables were used to do some counting. And at the end of the loop, a processing report was created to inform the end user about how many records had been processed, split out for several statusses. When rewriting it to a single SQL, you can use the SQL%ROWCOUNT to determine how many rows were processed, but if you want to split this number up per status, how to do that?

Here is an example, which doesn't look like the original code in any way, but it shows the same principle. First, let's create a table with some random statusses and create a procedure that creates a processing report in the dbms_output buffer:

rwijk@ORA11GR1> create table t (id,col,status)
  2  as
  3   select level
  4        , '**********'
  5        , cast(dbms_random.string('u',1) as varchar2(1))
  6     from dual
  7  connect by level <= 1000
  8  /

Table created.

rwijk@ORA11GR1> select status
  2       , count(*)
  3    from t
  4   where status in ('A','B','C','D','E')
  5   group by status
  6   order by status
  7  /

S   COUNT(*)
- ----------
A         38
B         42
C         37
D         39
E         29

5 rows selected.

rwijk@ORA11GR1> create procedure print_processing_report
  2  ( p_count_a in number
  3  , p_count_b in number
  4  , p_count_c in number
  5  , p_count_d in number
  6  , p_count_e in number
  7  )
  8  is
  9    procedure print_one_line
 10    ( p_status in t.status%type
 11    , p_count in number
 12    )
 13    is
 14    begin
 15      dbms_output.put_line
 16      ( '* Total number of ' || p_status || '''s processed: ' ||
 17        rpad(to_char(p_count),4) || '*'
 18      );
 19    end print_one_line
 20    ;
 21  begin
 22    dbms_output.put_line('**************************************');
 23    dbms_output.put_line('* P R O C E S S I N G    R E P O R T *');
 24    dbms_output.put_line('* ================================== *');
 25    print_one_line('A',p_count_a);
 26    print_one_line('B',p_count_b);
 27    print_one_line('C',p_count_c);
 28    print_one_line('D',p_count_d);
 29    print_one_line('E',p_count_e);
 30    dbms_output.put_line('**************************************');
 31  end;
 32  /

Procedure created.

And here is the code doing row by row processing:

rwijk@ORA11GR1> declare
  2    cursor c_t
  3    is
  4    select t.status
  5      from t
  6     where t.status in ('A','B','C','D','E')
  7           for update of t.status
  8    ;
  9    a pls_integer := 0;
 10    b pls_integer := 0;
 11    c pls_integer := 0;
 12    d pls_integer := 0;
 13    e pls_integer := 0;
 14  begin
 15    for r in c_t
 16    loop
 17      update t
 18         set col = 'z'
 19       where current of c_t
 20      ;
 21      case r.status
 22        when 'A' then a := a + 1;
 23        when 'B' then b := b + 1;
 24        when 'C' then c := c + 1;
 25        when 'D' then d := d + 1;
 26        when 'E' then e := e + 1;
 27      end case
 28      ;
 29    end loop
 30    ;
 31    print_processing_report(a,b,c,d,e);
 32  end;
 33  /
**************************************
* P R O C E S S I N G    R E P O R T *
* ================================== *
* Total number of A's processed: 38  *
* Total number of B's processed: 42  *
* Total number of C's processed: 37  *
* Total number of D's processed: 39  *
* Total number of E's processed: 29  *
**************************************

PL/SQL procedure successfully completed.

When rewriting it to a single SQL statement, it is of course possible to execute another statement to retrieve the numbers per status, but that's an extra query, and a query that's executed at a different time, so you'd have to switch to a serializable isolation level, or use flashback technology to be accurate.

Or you can use aggregate functions in your returning into clause. When reading about the RETURNING INTO clause in the documentation, it talks about a "single_row_expression" being "An expression that returns a single row of a table." So it is not immediately obvious that an aggregate can be used. But it is allowed:

rwijk@ORA11GR1> rollback
  2  /

Rollback complete.

rwijk@ORA11GR1> declare
  2    a pls_integer;
  3    b pls_integer;
  4    c pls_integer;
  5    d pls_integer;
  6    e pls_integer;
  7  begin
  8    update t
  9       set col = 'z'
 10     where status in ('A','B','C','D','E')
 11           returning
 12             count(decode(status,'A',1))
 13           , count(decode(status,'B',1))
 14           , count(decode(status,'C',1))
 15           , count(decode(status,'D',1))
 16           , count(decode(status,'E',1))
 17           into
 18             a
 19           , b
 20           , c
 21           , d
 22           , e
 23    ;
 24    print_processing_report(a,b,c,d,e);
 25  end;
 26  /
**************************************
* P R O C E S S I N G    R E P O R T *
* ================================== *
* Total number of A's processed: 38  *
* Total number of B's processed: 42  *
* Total number of C's processed: 37  *
* Total number of D's processed: 39  *
* Total number of E's processed: 29  *
**************************************

PL/SQL procedure successfully completed.

Needless to say, this is the fastest solution.

The funny thing though is that this exact same code doesn't work in Oracle10 (tested on 10.2.0.1 and 10.2.0.4):

rwijk@ORA10GR2> declare
  2    a pls_integer;
  3    b pls_integer;
  4    c pls_integer;
  5    d pls_integer;
  6    e pls_integer;
  7  begin
  8    update t
  9       set col = 'z'
 10     where status in ('A','B','C','D','E')
 11           returning
 12             count(decode(status,'A',1))
 13           , count(decode(status,'B',1))
 14           , count(decode(status,'C',1))
 15           , count(decode(status,'D',1))
 16           , count(decode(status,'E',1))
 17           into
 18             a
 19           , b
 20           , c
 21           , d
 22           , e
 23    ;
 24    print_processing_report(a,b,c,d,e);
 25  end;
 26  /
**************************************
* P R O C E S S I N G    R E P O R T *
* ================================== *
* Total number of A's processed: 190 *
* Total number of B's processed: 190 *
* Total number of C's processed: 190 *
* Total number of D's processed: 190 *
* Total number of E's processed: 190 *
**************************************

PL/SQL-procedure is geslaagd.

And I don't know why the above fails to produce the right numbers, but it does work on Oracle10g when using the sum aggregate function:

rwijk@ORA10GR2> rollback
  2  /

Rollback is voltooid.

rwijk@ORA10GR2> declare
  2    a pls_integer;
  3    b pls_integer;
  4    c pls_integer;
  5    d pls_integer;
  6    e pls_integer;
  7  begin
  8    update t
  9       set col = 'z'
 10     where status in ('A','B','C','D','E')
 11           returning
 12             sum(case status when 'A' then 1 else 0 end)
 13           , sum(case status when 'B' then 1 else 0 end)
 14           , sum(case status when 'C' then 1 else 0 end)
 15           , sum(case status when 'D' then 1 else 0 end)
 16           , sum(case status when 'E' then 1 else 0 end)
 17           into a, b, c, d, e
 18    ;
 19    print_processing_report(a,b,c,d,e);
 20  end;
 21  /
**************************************
* P R O C E S S I N G    R E P O R T *
* ================================== *
* Total number of A's processed: 36  *
* Total number of B's processed: 35  *
* Total number of C's processed: 41  *
* Total number of D's processed: 43  *
* Total number of E's processed: 35  *
**************************************

PL/SQL-procedure is geslaagd.

So yet another excuse less to write cursor for loops with DML inside.

***

Two other random nice-things-to-know about the returning into clause:

1) You can use an object type to "return into":

rwijk@ORA11GR1> create type my_object is object
  2  ( col1 number
  3  , col2 varchar2(1)
  4  );
  5  /

Type created.

rwijk@ORA11GR1> declare
  2    o my_object;
  3  begin
  4    update t
  5       set col = 'z'
  6     where id = 123
  7           returning my_object(t.id,t.status)
  8           into o
  9    ;
 10    dbms_output.put_line(o.col1);
 11    dbms_output.put_line(o.col2);
 12  end;
 13  /
123
T

PL/SQL procedure successfully completed.

2) The returning into clause doesn't work together with the "where current of" clause:

rwijk@ORA11GR1> declare
  2    cursor c is select ename from emp for update of sal;
  3    l_ename emp.ename%type;
  4  begin
  5    for r in c
  6    loop
  7      update emp
  8         set sal = sal * 1.1
  9       where current of c
 10             returning ename into l_ename
 11      ;
 12      dbms_output.put_line(l_ename);
 13    end loop;
 14  end;
 15  /
           returning ename into l_ename
           *
ERROR at line 10:
ORA-06550: line 10, column 12:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 7, column 5:
PL/SQL: SQL Statement ignored

So you'll have to mimic the current of clause by selecting and using the rowid:

rwijk@ORA11GR1> declare
  2    cursor c is select rowid, ename from emp for update of sal;
  3    l_ename emp.ename%type;
  4  begin
  5    for r in c
  6    loop
  7      update emp
  8         set sal = sal * 1.1
  9       where rowid = r.rowid
 10             returning ename into l_ename
 11      ;
 12      dbms_output.put_line(l_ename);
 13    end loop;
 14  end;
 15  /
SMITH
ALLEN
WARD
JONES
MARTIN
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.