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.
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”? Bulk Processing in PL/SQL 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.
We often need to perform other steps prior to execution of our data manipulation language (DML) statements.
Suppose that, for example, in the case of the increase_salary procedure, I need to check employees for eligibility for the increase in salary and if they are ineligible, send an e-mail notification.
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?The UPDATE statement executes for each of those employees, applying the same percentage increase to all.In such a simple scenario, a cursor FOR loop is not needed at all.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.Actual results will vary, depending on the version of Oracle Database you are running and the specifics of your application logic.