Use the BULK COLLECT clause to fetch multiple rows into one or more collections with a single context switch.
Use the FORALL statement when you need to execute the same DML statement repeatedly for different bind variable values.
The UPDATE statement executes for each of those employees, applying the same percentage increase to all.
SQL SELECT * FROM employee; ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION ---- ---------- ---------- --------- --------- ---------- ---------- --------------- 01 Jason Martin 25-JUL-96 25-JUL-06 10000 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 10000 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 10000 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 10000 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 10000 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 10000 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 10000 New York Manager 08 James Cat 17-SEP-96 15-APR-02 10000 Vancouver Tester 8 rows selected.The FOR UPDATE clause is an optional part of a SELECT statement. The FOR UPDATE clause specifies that the cursor should be updatable, and enforces a check during compilation that the SELECT statement meets the requirements for an updatable The use of the FOR UPDATE clause is not mandatory to obtain an updatable JDBC Result Set.I can simplify this procedure to nothing more than the code in Listing 2.Code Listing 2: Simplified increase_salary procedure without FOR loop PROCEDURE increase_salary ( department_id_in IN employees.department_id%TYPE, increase_pct_in IN NUMBER) IS BEGIN UPDATE employees emp SET emp.salary = emp.salary emp.salary * increase_salary.increase_pct_in WHERE emp.department_id = increase_salary.department_id_in; END increase_salary; Now there is just a single context switch to execute one UPDATE statement. Of course, in most real-world scenarios, life—and code—is not so simple.The explanation has everything to do with how the runtime engines for both PL/SQL and SQL communicate with each other—through a .
Context Switches and Performance Almost every program PL/SQL developers write includes both PL/SQL and SQL statements.
Taking advantage of PL/SQL’s elegant cursor FOR loop and the ability to call SQL statements natively in PL/SQL, I come up with the code in Listing 1.
Code Listing 1: increase_salary procedure with FOR loop PROCEDURE increase_salary ( department_id_in IN employees.department_id%TYPE, increase_pct_in IN NUMBER) IS BEGIN FOR employee_rec IN (SELECT employee_id FROM employees WHERE department_id = increase_salary.department_id_in) LOOP UPDATE employees emp SET emp.salary = emp.salary emp.salary * increase_salary.increase_pct_in WHERE emp.employee_id = employee_rec.employee_id; END LOOP; END increase_salary; the PL/SQL engine will “switch” over to the SQL engine 100 times, once for each row being updated.
In this article, I will cover the two most important of these features: BULK COLLECT and FORALL.
might mean—how much impact do these features really have?
Take another look at the increase_salary procedure.