Oracle 10g New Features: DML Enhancements

articles: 

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.

Comments

At the start of the article, you announce "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.". However, I am not able to find these SQL interrow calculations that support Merge operations. Have they somehow vanished?

I tried the insert with a select statement and returning clause. E.g.

INSERT INTO emp select * from emp
RETURNING sum(sal) INTO :tot_sal;

It does NOT work, Oracle kept indicating that the statement was not formed correctly. A colleague did a bit more searching and found that it the feature was in Oracle 10g beta but did not make it into the production release. This is supported by the Oracle 10g documentation which shows the returning clause with the insert statement only being applicable when using the values clause as the source.

You are correct, it does not work nor is it suppored in 11g (as of 11.1.0.7). This web page should be updated with the correct syntax, otherwise "ORA-00933: SQL command not properly ended" will be thrown.

You can use SQL%ROWCOUNT for this:

create table HR.X1 (a number);
insert into HR.X1 values (1);
insert into HR.X1 values (2);
insert into HR.X1 values (3);
insert into HR.X1 values (4);
insert into HR.X1 values (5);
insert into HR.X1 values (6);

create table HR.X2 (a number);

set serveroutput on
declare
  n number;
begin
  insert into HR.X2 (a) 
  select a a_num from HR.X1 where a> 3;
  dbms_output.put_line(SQL%ROWCOUNT);
end;
/