Using Aggregates in the returning Clause

Page 207

SQL Modeling

Oracle Database 10g offers a new model clause, which offers more power and flexibility to SQL calculations. The model clause allows you to create a ''spreadsheet," or a multidimensional array, from query results. You can also apply formulas to the results and calculate new values. In essence, this functionality can replace some PC-based spreadsheets.

The model clause offers a great amount of power and flexibility, and it supports Oracle's security features. Modeling offers spreadsheet-like functionality such as symbolic cell addressing, array computation, and the ability to insert cell values and update existing cell values.

Because of the model clause's flexibility, covering its wealth of features is beyond the scope of this discussion. You will find much more information on this feature of Oracle Database 10g in the Oracle 10g Database Data Warehousing Guide.

Using Aggregates in the returning Clause

Oracle Database 10g now allows you to return a summary function in the returning clause. The following example updates the salaries of all employees and returns the average resulting salary for those employees:

 SQL> VARIABLE a NUMBER SQL> UPDATE employee   2  SET sal=sal*1.10 RETURNING AVG(sal) INTO :a; SQL> PRINT a            a     --------       655.22 

Here is an example of using this functionality within PL/SQL:

 Declare      a   number; begin      update employee set sal=sal*1.10 returning avg(sal) into a;      dbms_output.put_line(a); end; 

In this case, the query returns the average of the SAL column. Keep in mind that the returning clause only has an effect on the rows actually touched by the query, so the average is the average of the rows inserted, and does not include in that