RETURNING INTO revisited

While reviewing some code I came across a feature with the RETRUNING INTO clause that I’m not sure I knew about (or I had long forgotten about). In any case it’s worth noting as a follow-up to my earlier RETURNING INTO posts…

As previously noted, RETURNING INTO is used to return scalar values back to PL/SQL from an SQL statement. The RETURNING INTO BULK COLLECT option is used for returning a set of values. The feature noted in a recent code review showed that it’s possible to use aggregate functions with the RETURNING INTO clause to collapse a multi-row operation into one or more scalar values. From a quick search of other posts on the internet, this feature crept in somewhere around Oracle 10g.

A simple example should illustrate things better. First up, we’ll create a simple table with two columns and ten rows:

CREATE TABLE t1
   (id      NUMBER (6) NOT NULL
   ,val     NUMBER (4) NOT NULL)
/

INSERT INTO t1
SELECT ROWNUM
,      ROWNUM
FROM   dual
CONNECT BY ROWNUM <= 10
/

COMMIT
/

Next we’ll write a simple PL/SQL block that increments the VAL column and, using the RETURNING INTO clause, returns the SUM and AVG of the values updated:

DECLARE
   l_sum  NUMBER;
   l_avg  NUMBER;
BEGIN
   UPDATE t1
   SET    val = val + 1
   RETURNING SUM(val), AVG(val) INTO l_sum, l_avg;
 
   dbms_output.enable; 
   dbms_output.put_line ('Sum: ' || TO_CHAR(l_sum));
   dbms_output.put_line ('Avg: ' || TO_CHAR(l_avg));
END;
/   

which gives us a result of:

Sum: 65
Avg: 6.5

The sum value of 65 is (2+3+4+5+6+7+8+9+10+11), which are values the VAL column contains after the update. Obviously the average of 6.5 is the sum of 65 divided by the 10 rows that were modified.

The same applies when deleting rows:

DECLARE
   l_sum  NUMBER;
   l_avg  NUMBER;
BEGIN
   DELETE
   FROM   t1
   WHERE  id BETWEEN 1 AND 4
   RETURNING SUM(val), AVG(val) INTO l_sum, l_avg;
 
   dbms_output.enable; 
   dbms_output.put_line ('Sum: ' || TO_CHAR(l_sum));
   dbms_output.put_line ('Avg: ' || TO_CHAR(l_avg));
END;
/   

which results in (assuming we’re operating on the original table, prior to the UPDATE above):

Sum: 10
Avg: 2.5

Note, it’s not possible to use the DISTINCT clause of the agregate functions:

DECLARE
   l_count  NUMBER;
BEGIN
   UPDATE t1
   SET    val = val + 1
   RETURNING COUNT(id) INTO l_count;
 
   dbms_output.enable; 
   dbms_output.put_line ('Count: ' || TO_CHAR(l_count));
END;
/   

which results in the following error:

ERROR at line 1:
ORA-00934: group function is not allowed here
ORA-06512: at line 4

So, +1 for learning new things from code reviews. Unfortunately the developer totally spoilt it all by using something else I didn’t know about and I would definitely not recommend but that’s a topic for a different post…

Advertisement