Exploit the Bulk Bind of PL/SQL

After a long time, let me write a bit of Oracle this week. Improve the performance of multi-row SQL operations with bulk/array processing in PL/SQL. For code snippets of the form

cursor emp_cur is
select employee_id
from employee where department_id = dept_in;

begin
for rec in emp_cur loop
update employee set salary = salary + 1000
where employee_id = rec.employee_id;
end loop;
end;

Bulk processing of PL/SQL can be used to improve its performance to greater levels. Use the FORALL and BULKCOLLECT of PL/SQL that will scale the application. The code snippet can be changed to

select employee_id
bulk collect into collection_var
from employee where department_id = dept_in;

begin
forall i in collection_var.first .. collection_var.last
update employee set salary = salary + 1000
where employee_id = collection_var(i);
end;

The context switch happening in executing PL/SQL and SQL code is reduced in this. The Save Exceptions and SQL%BULK_EXCEPTION is another good introduction on excellent exception handling on these bulk processing.