Bulk Binds (Writing data in bulk)
Bulk binds improve performance of DML statements by
minimizing the number of switches between the PL/SQL and SQL engines.
You may have a piece of code, which has multiple update,
delete or insert statements on the same table. This results in multiple calls
to the SQL engine for carrying out the transaction. By using bulk binds, you
can carry out mass scale data manipulation at one shot. The altered data has to
be stored in a PL/SQL collection in the code.
The FORALL statement is used for doing the bulk-processing
job at one go. This statement is similar to the FOR-LOOP statement except that
LOOP/END LOOP key words are not used. The FORALL statement needs a range to
work on, along with whatever DML activity is to be carried.
Syntax:
forall <counter> in <range begin> .. <range end>
update/insert/delete
Below is a simple example without Bulk Binds. Notice the
number of times the update is performed. Every SQL statement present in the
PL/SQL code results in a call to the SQL engine for processing.
create or replace procedure updsal is
cursor cr_emp is
select empno, job, sal
from amemp
where job in ('MANAGER', 'PRESIDENT', 'DBA');
begin
for rec in cr_emp loop
... some checks on the employee
...
if rec.job = 'MANAGER' then
update amemp
set sal = sal * 1.1
where empno = rec.empno;
else
update amemp
set sal = sal * 1.2
where empno = rec.empno;
end if;
end loop;
end;
The above code is changed as shown below, using the bulk
binding option. Notice the single update call to the backend.
create or replace procedure updsal is
cursor cr_emp is
select empno, job, sal
from amemp
where job in ('MANAGER', 'PRESIDENT', 'DBA');
type amemp_tab1 is table of amemp.empno%type
index by binary_integer;
type amemp_tab2 is table of amemp.sal%type
index by binary_integer;
empnum amemp_tab1;
empsal amemp_tab2;
cnt number := 0;
begin
for rec in cr_emp loop
... some checks on the employee
...
cnt := cnt + 1;
empnum(cnt) := rec.empno;
if rec.job = 'MANAGER' then
empsal(cnt) := rec.sal * 1.1;
else
empsal(cnt) := rec.sal * 1.2;
end if;
end loop;
forall i in 1 .. cnt
update amemp
set sal = empsal(i)
where empno = empnum(i);
end;
/
Below is an example that uses both bulk binding and
collection together.
declare
type emp_t is table of amemp.empno%type;
lst emp_t;
begin
select empno bulk collect
into lst
from amemp
where sal < 2000;
..some checks here...
forall i in lst.first .. lst.last
update amemp
set sal = sal * 1.1
where empno = lst(i);
end;
/
Bulk collects updated value return
You can even use bulk collects to return a value to the
calling procedure using the RETURNING clause, without any additional fetch.
Below is an example that updates the salary as and stores the updated
information in a collection for further processing.
declare
type no_t is table of amemp.empno%type;
type sal_t is table of amemp.sal%type;
lno no_t;
lsal sal_t;
begin
select empno bulk collect
into lno
from amemp
where job = 'DBA';
..
..
forall i in lno.first .. lno.last
update amemp
set sal = sal * 1.1
where empno = lno(i)
returning sal bulk collect into lsal;
..
for i in lno.first .. lno.last loop
dbms_output.put_line(lno(i) || '/' || lsal(i));
end loop;
end;
/
In the next installment, we will cover the above features
with more collection examples and performance issues that may be encountered in
heavy processing.
»
See All Articles by Columnist Amar Kumar Padhi