BULK COLLECT
Ø This is used for array
fetches
Ø With this you can retrieve
multiple rows of data with a single roundtrip.
Ø This reduces the number of
context switches between the pl/sql and sql engines.
Ø Reduces the overhead of
retrieving data.
Ø You can use bulk collect
in both dynamic and static sql.
Ø You can use bulk collect
in select, fetch into and returning into clauses.
Ø SQL engine automatically
initializes and extends the collections you reference in the bulk collect
clause.
Ø Bulk collect operation
empties the collection referenced in the into clause before executing the
query.
Ø You can use the limit
clause of bulk collect to restrict the no of rows retrieved.
Ø You can fetch into
multible collections with one column each.
Ø Using the returning clause
we can return data to the another collection.
BULK COLLECT IN FETCH
Ex:
DECLARE
Type t is table of
dept%rowtype;
nt t;
Cursor c is select
*from dept;
BEGIN
Open c;
Fetch c bulk collect
into nt;
Close c;
For i in nt.first..nt.last
loop
dbms_output.put_line('Dname
= ' || nt(i).dname || ' Loc = ' ||
nt(i).loc);
end loop;
END;
Output:
Dname = ACCOUNTING Loc = NEW YORK
Dname = RESEARCH Loc = DALLAS
Dname = SALES Loc = CHICAGO
Dname = OPERATIONS Loc = BOSTON
BULK COLLECT IN SELECT
Ex:
DECLARE
Type t is table of
dept%rowtype;
Nt t;
BEGIN
Select * bulk collect
into nt from dept;
for i in nt.first..nt.last
loop
dbms_output.put_line('Dname = ' || nt(i).dname || ' Loc = ' ||
nt(i).loc);
end loop;
END;
Output:
Dname = ACCOUNTING Loc = NEW YORK
Dname = RESEARCH Loc = DALLAS
Dname = SALES Loc = CHICAGO
Dname = OPERATIONS Loc = BOSTON
LIMIT IN BULK COLLECT
You can use this to limit
the number of rows to be fetched.
Ex:
DECLARE
Type t is table of
dept%rowtype;
nt t;
Cursor c is select
*from dept;
BEGIN
Open c;
Fetch c bulk collect
into nt limit 2;
Close c;
For i in nt.first..nt.last
loop
dbms_output.put_line('Dname = ' || nt(i).dname || ' Loc = ' ||
nt(i).loc);
end loop;
END;
Output:
Dname = ACCOUNTING Loc = NEW YORK
Dname = RESEARCH Loc = DALLAS
MULTIPLE FETCHES IN INTO CLAUSE
Ex1:
DECLARE
Type t is table of dept.dname%type;
nt t;
Type t1 is table of dept.loc%type;
nt1 t;
Cursor c is select dname,loc from
dept;
BEGIN
Open c;
Fetch c bulk collect into nt,nt1;
Close c;
For i in nt.first..nt.last loop
dbms_output.put_line('Dname =
' || nt(i));
end loop;
For i in nt1.first..nt1.last loop
dbms_output.put_line('Loc = '
|| nt1(i));
end loop;
END;
Output:
Dname = ACCOUNTING
Dname = RESEARCH
Dname = SALES
Dname = OPERATIONS
Loc = NEW YORK
Loc = DALLAS
Loc = CHICAGO
Loc = BOSTON
Ex2:
DECLARE
type t is table of dept.dname%type;
type t1 is table of dept.loc%type;
nt t;
nt1 t1;
BEGIN
Select dname,loc bulk collect into nt,nt1
from dept;
for i in nt.first..nt.last loop
dbms_output.put_line('Dname = ' ||
nt(i));
end loop;
for i in nt1.first..nt1.last loop
dbms_output.put_line('Loc = ' ||
nt1(i));
end loop;
END;
Output:
Dname = ACCOUNTING
Dname = RESEARCH
Dname = SALES
Dname = OPERATIONS
Loc = NEW YORK
Loc = DALLAS
Loc = CHICAGO
Loc = BOSTON
RETURNING CLAUSE IN BULK
COLLECT
You can use this to return
the processed data to the ouput variables or typed variables.
Ex:
DECLARE
type t is table of number(2);
nt t := t(1,2,3,4);
type t1 is table of varchar(2);
nt1 t1;
type t2 is table of student%rowtype;
nt2 t2;
BEGIN
select name bulk collect into nt1 from
student;
forall v in nt1.first..nt1.last
update student set no = nt(v) where name =
nt1(v) returning
no,name,marks
bulk collect into nt2;
for v in nt2.first..nt2.last loop
dbms_output.put_line('Marks = '
|| nt2(v));
end loop;
END;
Output:
Marks = 100
Marks = 200
Marks = 300
Marks = 400
POINTS TO REMEMBER
Ø Cursor name can be up to
30 characters in length.
Ø Cursors declared in
anonymous blocks or subprograms closes automatically when that block terminates
execution.
Ø %bulk_rowcount and
%bulk_exceptions can be used only with forall construct.
Ø Cursor declarations may
have expressions with column aliases.
Ø These expressions are
called virtual columns or calculated columns.
No comments:
Post a Comment