Bulk Mechanism
IT
IS USED TO REDUCE THE TIME TAKEN TO RETREVE THE DATA
àBulk
is one of the method to improve the performance of the applications
2 types:
1:Bulk
Collect
2:Bulk binding
1:Bulk Collect In this
clause we are used to fetch the data from database into collection.
àselect
……into…clause
àcursor
….fetch…..statement
àdml
….returning
bulk collect practise
=====================
declare
type t1 is table of jacmel.jmplan_temp%rowtype index by
binary_integer;
v1 t1;
begin
select * bulk collect into v1 from jacmel.jmplan_temp;
for i in v1.first..v1.last
loop
dbms_output.put_line(v1(i).SEGMENT1||','||v1(i).ATTR1||','||v1(i).CUSTOMER_NUMBER);
end loop;
end;
Example for bulk collect by 2 variables
---------------------------------------
DECLARE
type t1
IS
TABLE OF
VARCHAR2(50) INDEX BY binary_integer;
v1 t1;
v2 t1;
CURSOR c1
IS
SELECT
SEGMENT1,attr1 FROM jacmel.jmplan_temp;
BEGIN
OPEN c1;
FETCH c1 bulk
collect INTO v1,v2;
CLOSE c1;
FOR i IN
v1.first..v1.last
LOOP
dbms_output.put_line(v1(i)||','||v1(i));
END LOOP;
END;
example with out
bulk collect method
declare
vrow VARCHAR2(30);
cursor c1 is select distinct name from
wsh_deliverable_trips_v;
z1 number(10);
z2 number(10);
begin
z1:=dbms_utility.get_time;
open c1;
loop
fetch c1 into vrow;
exit when c1%notfound;
end loop;
close c1;
z2:=dbms_utility.get_time;
dbms_output.put_line(z1);
dbms_output.put_line(z2);
dbms_output.put_line(z2-z1);
end;
/
example with bulk
method
declare
type t1 is table of varchar2(30) index by binary_integer;
v1 t1;
cursor c1 is select distinct name from
wsh_deliverable_trips_v;
z1 number(10);
z2 number(10);
begin
z1:=dbms_utility.get_time;
open c1;
loop
fetch c1 bulk collect into v1;
exit when c1%notfound;
end loop;
close c1;
z2:=dbms_utility.get_time;
dbms_output.put_line(z1);
dbms_output.put_line(z2);
dbms_output.put_line(z2-z1);
end;
/
