Wednesday, October 15, 2014

Oracle PLSQL - 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 multiple 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

Best Blogger TipsGet Flower Effect