bulk collect and for all..........

[pravatjena]

can someone explain clearly what is bulk collect and for all..........

[amitpatel66]

can someone explain clearly what is bulk collect and for all..........

Check out here .

[subashsavji]

can someone explain clearly what is bulk collect and for all..........

[code=oracle]

create type obj as object(x number)
/
create table objtable of obj
/

declare
type obj1 is table of obj;
z obj1:=obj1(obj( 1),obj(2),obj(3 ),obj(4));
type obj2 is varray(5) of integer;
x obj2:=obj2(1,2, 3);
begin
forall i in z.first..z.last
insert into objtable values(z(i));--Inserts the values in table of objects objtable.
forall i in x.first..x.last
insert into emp_table values(x(i),'am it');-----Inserts the values in emp_table
forall i1 in x.first..x.last
delete emp_table where num=x(i1);----Deletes all the records of emp_table
if sql%bulk_rowcou nt(2)=0 then
dbms_output.put _line('value does not exist');
else
dbms_output.put _line('value exist'|| sql%bulk_rowcou nt(2));
end if;
forall i2 in x.first..x.last
update emp_table set num=x(i2) where num=x(i2);
if sql%bulk_rowcou nt(2)=0 then-------------------------A
dbms_output.put _line('value does not exist');
end if;
end;
/

when we user cursor then

create table emp_tab4(empno number, ename varchar2(20),sa lary number)
/
insert into emp_tab4 values(1,'anil' ,1000)
/
insert into emp_tab4 values(2,'sunil ',2000)
/

declare
type eno is table of number;
type ename is table of varchar2(20);
x eno;
y ename;
cursor c1 is select empno,ename from emp_tab4 WHERE salary > 1000;
rows natural:=2;
cnt number:=0;
begin
select empno,ename BULK COLLECT into x , y from emp_tab4;--A
open c1;
fetch c1 BULK COLLECT into x , y;

for i in x.first..x.last loop
dbms_output.put _line(x(i)||' '||y(i));
end loop;
close c1;
open c1;
fetch c1 BULK COLLECT into x , y LIMIT rows;---------B
dbms_output.put _line('Limits the no of rows');
for i in x.first..x.last loop
dbms_output.put _line(x(i)||' '||y(i));
end loop;
close c1;
delete emp_tab4 where ename='sunil'------------------------C
returning empno BULK COLLECT into x;

for i in x.first..x.last loop
cnt:=cnt+1;
end loop;
dbms_output.put _line('the no of rows affected'||' '|| cnt);
end;
/

[/code]