Total Pageviews

Saturday, April 6, 2013


BULK COLLECT In Pl/Sql With Examples :

BULK COLLECT In Pl/Sql
Ø  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
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
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
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;
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