PLSQL Bulk collect and Forall

Bulk Collect and Forall

Bulk Collect :- Bulk collect is using for avoiding the context switching.Bulk collect fetch records in bunch from SQL engine.

Context Switching :- If any SQL statement is there in PLSQL block then PLSQL engine send that SQL to SQL engine for execution and get the result from there. If you are using cursor then PLSQL engine is ask for records from SQL engine on every fetch, this is called context switching.

Example:-

declare
         TYPE rec_type IS TABLE of departments%ROWTYPE INDEX BY BINARY_INTEGER;
         rec_dept rec_type;
begin
         SELECT * BULK COLLECT INTO rec_dept FROM departments;
         FORALL i IN rec_dept.FIRST .. rec_dept.LAST
         INSERT INTO departments_tmp VALUES vrt_dept (i);
end;
/

commit;

select * from departments_tmp;


declare
type c_dept_data is record (DEPARTMENT_ID departments.DEPARTMENT_ID%type
                           ,DEPARTMENT_NAME departments.DEPARTMENT_name%type
                           ,LOCATION_ID departments.LOCATION_ID%type);
TYPE rt_dept IS TABLE of c_dept_data%ROWTYPE 
INDEX BY BINARY_INTEGER;
vrt_dept rt_dept;
begin
SELECT DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID 
BULK COLLECT INTO vrt_dept 
FROM departments;
FORALL i IN vrt_dept.FIRST .. vrt_dept.LAST
INSERT INTO departments_tmp
(DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID) VALUES 
(vrt_dept(i).DEPARTMENT_ID,vrt_dept(i).DEPARTMENT_NAME,vrt_dept(i).LOCATION_ID);
end;
/



declare
CURSOR c_dept_data IS 
SELECT DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID 
FROM departments ;
TYPE rt_dept IS TABLE of c_dept_data%ROWTYPE 
INDEX BY BINARY_INTEGER;
vrt_dept rt_dept;
begin
SELECT DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID 
BULK COLLECT INTO vrt_dept 
FROM departments;
FORALL i IN vrt_dept.FIRST .. vrt_dept.LAST
INSERT INTO departments_tmp
(DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID) VALUES 
(vrt_dept(i).DEPARTMENT_ID,vrt_dept(i).DEPARTMENT_NAME,vrt_dept(i).LOCATION_ID);
end;
/

commit;

select * from departments_tmp;

declare
TYPE rt_deptid IS TABLE of departments.DEPARTMENT_ID%TYPE INDEX BY BINARY_INTEGER;
vrt_deptid rt_deptid;
TYPE rt_deptname IS TABLE of departments.DEPARTMENT_NAME%TYPE INDEX BY BINARY_INTEGER;
vrt_deptname rt_deptname;
TYPE rt_locid IS TABLE of departments.LOCATION_ID%TYPE INDEX BY BINARY_INTEGER;
vrt_locid rt_locid;
begin
SELECT DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID BULK COLLECT 
INTO vrt_deptid,vrt_deptname,vrt_locid FROM departments;
FORALL i IN vrt_deptid.FIRST .. vrt_deptid.LAST
INSERT INTO departments_tmp(DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID) VALUES 
(vrt_deptid(i),vrt_deptname(i),vrt_locid(i));
end;
/

commit;

select * from departments_tmp;

declare
cursor c1 is select deptno from dept;
vdept dept.deptno%type;
begin
open c1;
loop
fetch c1 into vdept;
dbms_output.put_line(vdept);
--delete from emp2 where deptno=vdept;
end loop;
close c1;
end;
/ 

drop table emp2;
create table emp2 as select * from emp;

create table temp_object1 as select * from temp_object where 1=2;
select count(*) from temp_object1;



declare
CURSOR c_dept_data IS SELECT * FROM temp_object ;
TYPE rt_dept IS TABLE of c_dept_data%ROWTYPE INDEX BY BINARY_INTEGER;
vrt_dept rt_dept;
begin
SELECT * BULK COLLECT INTO vrt_dept FROM temp_object;
FORALL i IN vrt_dept.FIRST .. vrt_dept.LAST
INSERT INTO temp_object1 VALUES vrt_dept (i);
end;
/


declare
CURSOR c_dept_data IS SELECT * FROM temp_object ;
c_dept_data1 c_dept_data%rowtype; 
begin
open c_dept_data;
loop
fetch c_dept_data into c_dept_data1;
exit when c_dept_data%notfound;
INSERT INTO temp_object1 VALUES (c_dept_data1.OWNER          
,c_dept_data1.OBJECT_NAME    
,c_dept_data1.SUBOBJECT_NAME 
,c_dept_data1.OBJECT_ID      
,c_dept_data1.DATA_OBJECT_ID 
,c_dept_data1.OBJECT_TYPE    
,c_dept_data1.CREATED        
,c_dept_data1.LAST_DDL_TIME  
,c_dept_data1.TIMESTAMP      
,c_dept_data1.STATUS         
,c_dept_data1.TEMPORARY      
,c_dept_data1.GENERATED      
,c_dept_data1.SECONDARY      
,c_dept_data1.NAMESPACE      
,c_dept_data1.EDITION_NAME);
end loop;
close c_dept_data;
end;
/

desc temp_object1;