aggregating data with the returning clause
I'm pleased to announce that I've been selected as a finalist in the inaugural Oracle Database Developer Choice Awards. To quote, these awards "celebrate and recognize technical expertise and contributions in the Oracle Database community".
I've been selected for the PL/SQL category, primarily for the articles and utilities that I share here on oracle-developer.net. I'm very pleased to be recognised by the Oracle Community in this way and if you have found oracle-developer.net to be a helpful and/or interesting resource, then I'd appreciate your vote (my voting page is here).
More information on the Awards, including an explanatory video, the rules, other categories and finalists can all be found on the main page.
Thanks for your support!
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.
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).
The source code for the examples in this article can be downloaded from here.
Adrian Billington, June 2004Back to Top