Thursday, June 25, 2015

Bulk Collect In Oracle

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

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

No comments:

Post a Comment