BULK Collect

--BULK Collect Example----

SET SERVEROUTPUT ON
declare
cursor c1 is
select ename, sal, job from emp;

TYPE name is table of emp.ename%type;
TYPE sal  is table of emp.sal%type;
TYPE job  is table of emp.job%type;

names name ;
sals sal;
jobs job;

begin

open c1;
fetch c1 bulk collect into names, sals, jobs;
close c1;

FOR i in 1.. names.count LOOP
dbms_output.put_line (names (i));
dbms_output.put_line (sals (i));
dbms_output.put_line (jobs (i));
dbms_output.put_line ('------------------------');
END LOOP;
dbms_output.put_line (names (11));
dbms_output.put_line (sals (12));
dbms_output.put_line (jobs (13));

END;

OUTPUT :

SMITH
800
CLERK
------------------------
ALLEN
1600
SALESMAN
------------------------
WARD
1250
SALESMAN
------------------------
JONES
2975
MANAGER
------------------------
MARTIN
1250
SALESMAN
------------------------
BLAKE
2850
MANAGER
------------------------
CLARK
2450
MANAGER
------------------------
SCOTT
3000
ANALYST
------------------------
KING
5000
PRESIDENT
------------------------
TURNER
1500
SALESMAN
------------------------
ADAMS
1100
CLERK
------------------------
JAMES
950
CLERK
------------------------
FORD
3000
ANALYST
------------------------
MILLER
1300
CLERK
------------------------
ADAMS
950
ANALYST
PL/SQL procedure successfully completed.

/*Advantage of above example is user can point to a particular record
directly provided by cursor After closing the cursor and loop we can fetch a particular record.*/