The DML statements that can use the single-set aggregates in their returning clauses are UPDATE and DELETE. We will begin with the UPDATE statement.

Single-set Aggregates in UPDATE Statements

The general format for the UPDATE statement when using a RETURING clause is for a single table UPDATE, using the INTO clause is shown below.

UPDATE <table|MV|view> SET <column>=<exprs>|<subquery>
RETURNING <exprs> INTO <data_items>;

Where:

• Table or expression -- This is a valid table, materialized view or updatable view
   
• Column,… -- A comma-separated list of column names or a single column name
   
• Subquery - a valid subquery to generate updated values
   
• Exprs -- A set of expressions based on the affected row
   
• Data_items -- A valid set of variables in which to load the values returned by the expressions

The purpose of the RETURNING clause is to return the rows affected by the UPDATE statement. The RETURNING clause can only be used with single tables and materialized views and regular views based on a single table.

When the target of the UPDATE is a single row, the RETURNING clause can retrieve column expressions using the affected row, rowid, and REFs to the affected row.

Single-set aggregates can only be used when the returning clause returns a single row. Single-set aggregates cannot be combined with simple expressions in the same returning clause. Single-set aggregates cannot contain the DISTINCT keyword. Single-set aggregates cannot be used with an INSERT statement.

An example UPDATE using the RETURNING clause and a single-set aggregate would be:

Set serveroutput on

Variable tot_sal number;

begin

UPDATE emp SET sal=sal*1.1

RETURNING sum(sal) INTO :tot_sal;

dbms_output.put_line('Total Company Payroll now : ' || to_char(:tot_sal,'$999,999.00'));

end;

/

An example of running this would be:

SQL> Set serveroutput on

SQL> Variable tot_sal number;

SQL> begin

  2  UPDATE emp SET sal=sal*1.1

  3  RETURNING sum(sal) INTO :tot_sal;

  4  dbms_output.put_line('Total Company Payroll now : ' || to_char(:tot_sal,'$999,999.00'));

  5  end;

  6  /
 

Total Company Payroll now :   $31,927.50

 

PL/SQL procedure successfully completed.