Advertise at FreeMegaZone
Give tremendous boost to your business by advertising at FreeMegaZone. Contact webmaster@freemegazone.com |
|
|
|
|
Author: Donald K. Burleson
The DML statements that can use the single-set aggregates in their returning clauses are UPDATE and DELETE.
Single-set aggregates:
- 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.
Returning Clause:
- The RETURNING clause can only be used with single tables and materialized views and regular views based on a single table.
- The purpose of the RETURNING clause in Single-set Aggregates in UPDATE statements is to return the rows affected by the DELETE statement.
- The purpose of the RETURNING clause in Single-set Aggregates in DELETE statements is to return the rows affected by the UPDATE statement.
- 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.
- When the target of the DELETE 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 in UPDATE Statements:
Below code shows the general format for the UPDATE statement when using a RETURING clause.
UPDATE
<table|MV|view>
SET
<column>=<exprs>|<subquery>
RETURNING <exprs>
INTO <data_items>;
|
Where
- Table or expression is a valid table, materialized view or updatable view
- Column is a comma-separated list of column names or a single column name
- Subquery is a valid subquery to generate updated values
- Exprs is a set of expressions based on the affected row
- Data_items is a valid set of variables in which to load the values returned by the expressions
|
An example UPDATE using the RETURNING clause and a single-set aggregate is shown below.
Example |
Running the example |
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;
/ |
SQL> set echo on
SQL> @my_example
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 $63,855.00
PL/SQL procedure successfully completed. |
Benefits:
- Single-set Aggregates in UPDATE Statements is a very useful feature for performing large inserts when you need to see the resulting aggregate values. The resulting aggregates could then be used to populate summary tables.
- This type of functionality can be used to return summary information after updates.
Single-set Aggregates in DELETE Statements:
Below code shows the general format for the DELETE statement, when using a RETURING clause is for a single table delete.
DELETE <table or expression>
<alias>
WHERE
<where_clause>
RETURNING <exprs>
INTO <data_items>; |
Where:
-
Table or expression is a valid table, materialized view, single-table view, or an expression based on one of the above
- Alias is valid alias for the table or expression
- Where_clause is a valid where clause which may include a subquery
- Exprs is a set of expressions based on the affected row
- Data_items is a valid set of variables in which to load the values returned by the expressions.
|
An example DELETE using a subquery in the WHERE statement and the RETURNING clause with a single-set aggregate is shown below.
| Example |
Running the example |
set serveroutput on
variable tot_sal number;
begin
delete emp a where a.rowid > (
select min (x.rowid) from emp x
where x.empno=a.empno)
RETURNING sum(a.sal) INTO :tot_sal;
dbms_output.put_line('Total Company Payroll now '||to_char(:tot_sal,'$999,999.00'));
end;
/
|
SQL> @my_example
SQL> set serveroutput on
SQL> variable tot_sal number;
SQL> begin
delete emp a where a.rowid > (
select min (x.rowid) from emp x
where x.empno=a.empno)
RETURNING sum(a.sal) INTO :tot_sal;
dbms_output.put_line('Total Company Payroll now: ' ||
to_char(:tot_sal,'$999,999.00'));
end;
/
Total Company Payroll now: $31,927.50
PL/SQL procedure successfully completed.
|
More Oracle Articles, Database Articles and DBA Tips
Database Security: Step by step guideline
Best Practice for Multiple Oracle Homes!
DBA Tips for Determining Invalid Materialized Views!!
Determining Oracle TEMP Size Usage!!
Expert Tips on Drop Temporary Tablespace Hangs!!
|
