BULK COLLECT MECHANISM

declare --- Nested table
TYPE T1 is table of Siddharth%rowtype;
V1 T1:=T1();
Cursor c1 is select * from Siddharth;
begin
Open C1;
Fetch C1  bulk collect into V1;
close C1;
for i in V1.first..V1.last Loop
dbms_output.put_line(V1(i).NAME||V1(i).ID);
end loop;
end;
/

declare --VARRAY
TYPE T1 is Varray(20) of Siddharth%rowtype;
V1 T1:=T1();
Cursor c1 is select * from Siddharth;
begin
Open C1;
Fetch C1  bulk collect into V1;
close C1;
for i in V1.first..V1.last Loop
dbms_output.put_line(V1(i).NAME||V1(i).ID);
end loop;
end;
/


declare ---Varray
Type T1 is Varray(20) of Siddharth%rowtype;
V1 T1;
begin
Select * bulk Collect into V1 from Siddharth;
for i in V1.first..V1.last Loop
dbms_output.put_line(V1(i).NAME ||V1(i).ID);
end loop;
end;
/

declare --- Nested table
TYPE T1 is table of number(10);
V1 T1:=T1(10,20,30,40,50,60,70,80,90);
begin
dbms_output.put_line(V1(1));
dbms_output.put_line(V1(2));
end;
/

declare ---Associated Array(Index by Table)
Type T1 is table of Siddharth%rowtype index by binary_integer;
V1 T1;
begin
Select * bulk Collect into V1 from Siddharth;
for i in V1.first..V1.last Loop
dbms_output.put_line(V1(i).NAME ||V1(i).ID);
end loop;
end;
/


Declare--Measuring the time Performance
Type T1 is table of Siddharth%rowtype index by binary_integer;
V1 T1;
Z1 Varchar2(30);
Z2 Varchar2(30);
Cursor C1 is select * from Siddharth;
begin
Z1:=dbms_utility.get_time;
dbms_output.put_line(Z1);
open C1;
fetch  C1 bulk collect into V1;
close C1;
for i in V1.first..V1.last Loop
dbms_output.put_line(V1(i).NAME||V1(i).ID);
end loop;
Z2:=dbms_utility.get_time;
dbms_output.put_line(Z2);
dbms_output.put_line(Z1-Z2);
end;
/


DECLARE
Type T1 is Table of Siddharth%rowtype index by binary_integer;
V1 T1;
V2 T1;
Cursor C1 is select * from Siddharth;
BEGIN
Select * bulk Collect into V1 from Siddharth;
for i in V1.first..V1.last loop
UPDATE Siddharth Set Id=id+1 where name='Siddharth' returning id, name, salary bulk Collect into V2;
end Loop;
for i in V2.first..V2.last loop
dbms_output.put_line(V2(i).NAME);
end loop;
end;
/

DECLARE
Type T1 is Table of Siddharth%rowtype index by binary_integer;
V2 T1;
BEGIN
UPDATE Siddharth Set Id=id+1 where name='Siddharth' returning id,name,salary bulk Collect into V2;
for i in V2.first..V2.last loop
dbms_output.put_line(V2(i).NAME||V2(i).id);
end loop;
end;
/




DECLARE
Type T1 is Table of Siddharth%rowtype index by binary_integer;
V2 T1;
BEGIN
UPDATE Siddharth Set Id=0 where name='siddharth' returning id,name,salary  bulk Collect into V2;
for i in V2.first..V2.last loop
dbms_output.put_line(V2(i).NAME);
end loop;
end;
/



DECLARE
TYPE T1 IS RECORD (DEPARTMENT   REDDY.DEPT%TYPE,
                   ROLL_NUMBER  REDDY.ROLL_NO%TYPE);
TYPE T2 IS TABLE OF T1 INDEX BY BINARY_INTEGER;                
X1 T2;
BEGIN
X1(1).DEPARTMENT:='CHEMSITRY';
X1(1).ROLL_NUMBER:=10;
X1(2).DEPARTMENT:='PHYSICS';
X1(2).ROLL_NUMBER:=20;
FOR I IN X1.FIRST..X1.LAST LOOP
DBMS_OUTPUT.PUT_LINE(X1(I).DEPARTMENT||X1(I).ROLL_NUMBER);
END LOOP;
END;
/