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