Oracle 10g New Features: DML Enhancements

In this article (chapter) we will look at new features surrounding the DML commands INSERT, UPDATE, and DELETE.The new features in these DML commands fall into two categories:

Single-Set Aggregates in DML Returning Clause - This allows the use of single-set aggregation functions in the RETURNING clause of DML statements. This can result in significant performance gains in transactions that process many rows of the same table - such as in batch processes.

Virtual Spreadsheets, Upsert, Through SQL Interrow Calculations - The SQL interrow calculation feature supports upsert operations (MERGE), enabling easy INSERT and UPDATE of calculated models and forecasts on virtual spreadsheets.

In the next few pages we will examine these new features and look at some simple examples.

Single-Set Aggregates in DML Returning Clause

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

Single-set Aggregates in the INSERT Statement

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

INSERT INTO <table or expression> <alias> (column,?)
<values_clause>|<subquery>
RETURNING <exprs> INTO <data_items>;

Where:

Table or expression -- This is a valid table, materialized view, single-table view, or an expression based on one of the above

Alias -- A valid alias for the table or expression

Column,? -- A comma-separated list of column names or a single column name

Values clause -- A valid values clause to be used to provide inserted values

Subquery -- a valid subquery to generate inserted 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 INSERT 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 INSERT 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.

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

Set serveroutput on
Variable tot_sal;
begin
INSERT INTO emp select * from emp
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 echo on
SQL> @first_example
SQL> set serveroutput on
SQL> variable tot_sal number;
SQL> begin
  2  INSERT INTO emp select * from emp
  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   $58,050.00 

PL/SQL procedure successfully completed.

This is a very useful new 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.

Single-set Aggregates in the UPDATE Statement

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

UPDATE [ONLY] <table or DML expression> <alias> 
SET column=expr|subquery,?
[WHERE where_clause]
[RETURNING <exprs> INTO <data_items>];

Where:

Table or DML expression -- This is a valid table, materialized view, updateable single-table view, or an expression based on one of the above

Alias -- A valid alias for the table or expression

Column -- A single column name

Subquery - a valid subquery to generate update 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.

An example UPDATE using the RETURNING clause and a single-set aggregate is shown below.

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 running of the above code would produce output similar to:

SQL> set echo on
SQL> @second_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.

This type of functionality would be used to return summary information after updates.

Single-set Aggregates in the DELETE Statement

The general format for the DELETE statement when using a RETURING clause is for a single table delete as shown below.

DELETE <table or expression> <alias> 
WHERE <where_clause>
RETURNING <exprs> INTO <data_items>;

Where:

Table or expression -- This is a valid table, materialized view, single-table view, or an expression based on one of the above

Alias -- A valid alias for the table or expression

Where_clause -- This is a valid where clause which may include a subquery

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 DELETE 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 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 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.

An example DELETE using a subquery in the WHERE statement and the RETURNING clause with a single-set aggregate would be:

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;
/

An execution of the above script would result in the following output:

SQL> @third_example
SQL> set serveroutput on
SQL> variable tot_sal number;
SQL> begin
  2  delete emp a where a.rowid > (
  3  select min (x.rowid) from emp x
  4  where x.empno=a.empno)
  5  RETURNING sum(a.sal) INTO :tot_sal;
  6  dbms_output.put_line('Total Company Payroll now: ' || 
     to_char(:tot_sal,'$999,999.00'));
  7  end;
  8  /
Total Company Payroll now:   $31,927.50 

PL/SQL procedure successfully completed.

This concludes the coverage of the three DML statements and their use of the RETURNING clause with single-row aggregates.

---

The above is an excerpt from the bestselling Oracle10g book Oracle Database 10g New Features by Mike Ault, Madhu Tumma and Daniel Liu, published by Rampant TechPress.