My procedure might then look like the version in Listing 3.Code Listing 3: increase_salary procedure with eligibility checking added PROCEDURE increase_salary ( department_id_in IN employees.department_id%TYPE, increase_pct_in IN NUMBER) IS l_eligible BOOLEAN; BEGIN FOR employee_rec IN (SELECT employee_id FROM employees WHERE department_id = increase_salary.department_id_in) LOOP check_eligibility (employee_rec.employee_id, increase_pct_in, l_eligible); IF l_eligible THEN UPDATE employees emp SET emp.salary = emp.salary emp.salary * increase_salary.increase_pct_in WHERE emp.employee_id = employee_rec.employee_id; END IF; END LOOP; END increase_salary; I can no longer do everything in SQL, so am I then resigned to the fate of “slow-by-slow processing”? The bulk processing features of PL/SQL are designed specifically to reduce the number of context switches required to communicate from the PL/SQL engine to the SQL engine.Take another look at the increase_salary procedure.The SELECT statement identifies all the employees in a department.
I will use the code in Listing 4 to explain how these features affect context switches and how you will need to change your code to take advantage of them.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.One variable, l_employee_ids, will hold the IDs of all employees in the department.
The other, l_eligible_ids, will hold the IDs of all those employees who are eligible for the salary increase.
The SQL engine executes the SQL statement and returns information back to the PL/SQL engine (see Figure 1).