Using the RETURNING Clause with Bulk Operations
You've now seen BULK COLLECT put to use for both implicit and explicit query cursors. You can also use BULK COLLECT inside a FORALL statement, in order to take advantage of the RETURNING clause.
The RETURNING clause, introduced in Oracle8, allows you to obtain information (such as a newly updated value for a salary) from a DML statement. RETURNING can help you avoid additional queries to the database to determine the results of DML operations that just completed.
Suppose that Congress has passed a law requiring that a company pay its highest-compensated employee no more than 50 times the salary of its lowest-paid employee. I work in the IT department of the newly merged company Northrop-Ford-Mattel-Yahoo-ATT, which employs a total of 250,000 workers. The word has come down from on high: the CEO is not taking a pay cut, so we need to increase the salaries of everyone who makes less than 50 times his 2004 total compensation package of $145 million—and decrease the salaries of all upper management except for the CEO. After all, somebody's got to make up for this loss in profit.
Wow! I have lots of updating to do, and I want to use FORALL to get the job done as quickly as possible. However, I also need to perform various kinds of processing on the employee data and then print a report showing the change in salary for each affected employee. That RETURNING clause would come in awfully handy here, so let's give it a try.
See the onlyfair.sql file on the O'Reilly site for all of the steps shown here, plus table creation and INSERT statements.
First, I'll create a reusable function to return the compensation for an executive:
/* File on web: onlyfair.sql */FUNCTION salforexec (title_in IN VARCHAR2) RETURN NUMBERIS CURSOR ceo_compensation IS SELECT salary + bonus + stock_options + mercedes_benz_allowance + yacht_allowance FROM compensation WHERE title = title_in; big_bucks NUMBER;BEGIN OPEN ceo_compensation; FETCH ceo_compensation INTO big_bucks; RETURN big_bucks;END;In the main block of the update program, I declare a number of local variables and the following query to identify underpaid employees and overpaid employees who are not lucky enough to be the CEO:
DECLARE big_bucks NUMBER := salforexec ('CEO'); min_sal NUMBER := big_bucks / 50; names name_varray; old_salaries number_varray; new_salaries number_varray; CURSOR affected_employees (ceosal IN NUMBER) IS SELECT name, salary + bonus old_salary FROM compensation WHERE title != 'CEO' AND ((salary + bonus < ceosal / 50) OR (salary + bonus > ceosal / 10)) ;At the start of my executable section, I load all of this data into my collections with a BULK COLLECT query:
OPEN affected_employees (big_bucks);FETCH affected_employees BULK COLLECT INTO names, old_salaries;Then I can use the names collection in my FORALL update:
FORALL indx IN names.FIRST .. names.LAST UPDATE compensation SET salary = GREATEST( DECODE ( GREATEST (min_sal, salary), min_sal, min_sal, salary / 5), min_sal ) WHERE name = names (indx) RETURNING salary BULK COLLECT INTO new_salaries;I use DECODE to give an employee either a major boost in yearly income or an 80% cut in pay to keep the CEO comfy. I end it with a RETURNING clause that relies on BULK COLLECT to populate a third collection: the new salaries.
Finally, because I used RETURNING and don't have to write another query against the compensation table to obtain the new salaries, I can immediately move to report generation:
FOR indx IN names.FIRST .. names.LASTLOOP DBMS_OUTPUT.PUT_LINE ( RPAD (names(indx), 20) || RPAD (' Old: ' || old_salaries(indx), 15) || ' New: ' || new_salaries(indx) );END LOOP;Here, then, is the report generated from the onlyfair.sql script:
John DayAndNight Old: 10500 New: 2900000Holly Cubicle Old: 52000 New: 2900000Sandra Watchthebucks Old: 22000000 New: 4000000Now everyone can afford quality housing and health care. And tax revenue at all levels will increase, so public schools can get the funding they need.
|
