aggregating data with the returning clause

From 10g, the RETURNING clause can be aggregated to return a single record or value from data affected by DML activity. We'll demonstrate this in this short article. Note that in previous versions, the same could only be achieved by returning all data for a column or selection of columns in bulk and then further processing these in a PL/SQL loop.

a simple example

We'll use the standard DEPT and EMP tables to demonstrate this new feature. In this rather contrived example, we'll imagine that our organisation wishes to outsource its research operation. We'll report on the effects of the resulting removal of this operation on the cost centre, including the monthly salary savings and the number of staff "removed".

SQL> DECLARE
  2
  3     TYPE rt_savings IS RECORD
  4     ( staff  NUMBER
  5     , salary NUMBER );
  6
  7     r_savings rt_savings;
  8
  9  BEGIN
 10
 11     DELETE FROM emp
 12     WHERE  deptno = ( SELECT deptno FROM dept WHERE dname = 'RESEARCH' )
 13     RETURNING COUNT(sal), SUM(sal) INTO r_savings;
 14
 15     DBMS_OUTPUT.PUT_LINE(
 16        TO_CHAR(r_savings.staff) || ' jobs outsourced with monthly ' || 
 17        'savings of £' ||TO_CHAR(r_savings.salary) || '.'
 18        );
 19
 20  END;
 21  /

5 jobs outsourced with monthly savings of £10875.

PL/SQL procedure successfully completed.

restrictions

The RETURNING clause can be very flexible (this is true of all supported versions). For example, data can be returned in bulk using BULK COLLECT in all versions from 8i onwards and will work with PL/SQL records (either a single record or, from 9i, with associative arrays/collections of records). There are notable exceptions however, and these are still present in 10g. Rather surprisingly, we cannot use the RETURNING clause with an INSERT..SELECT DML statement. Nor can we use this clause with a MERGE statement (which I suppose could be argued to be less surprising).

source code

The source code for the examples in this article can be downloaded from here.

Adrian Billington, June 2004

oracle-developer.net