René Nyffenegger's collection of things on the web  
René Nyffenegger on Oracle  Most wanted  Feedback
 Follow @renenyffenegger

Bulk Collect with PL/SQL  
In PL/SQL, using
bulk collect , it is possible to select the values from mutliple rows in one go.
Example #1
A table with two columns is created...
create table bc ( a number, b varchar2(10) );
... and filled with some values:
insert into bc values (11,'elf'); insert into bc values (12,'zwoelf'); insert into bc values (13,'dreizehn'); insert into bc values (14,'vierzehn'); commit;
An anonymous block is executed. Its declaration part defines two nestedtable types and
declares two variables, one of either type.
In the body, the select statement used bulk collect to select all rows in the table in one go into the variables.
Finally, a loop over the variables' elements prints the values.
set serveroutput on size 10000 declare type t_bc_a is table of bc.a%type; type t_bc_b is table of bc.b%type; l_bc_a t_bc_a; l_bc_b t_bc_b; begin select a, b bulk collect into l_bc_a, l_bc_b from bc; for i in l_bc_a.first .. l_bc_a.last loop dbms_output.put_line(l_bc_a(i)  ', '  l_bc_b(i)); end loop; end; / 11, elf 12, zwoelf 13, dreizehn 14, vierzehn Example #2
The 2^{nd} example is similar to the first one. The main difference is that two columns are bulk collected into
one (rather than two) variable. This variable must still be of a nestedtable type.
However, the type of the elements in the nestedtable type must now be an object type with two member variables that correspond to
the selected columns. Another difference is that the bulk collect will now be within a function that returns the values.
First, the object type is created:
create or replace type varchar_number as object ( v varchar2(10), i number ); /
The nestedtable type using
varchar_number as elements is created:
create or replace type t_varchar_number as table of varchar_number; /
A table containing some values:
create table vn ( v varchar2(10), i number ); insert into vn values ('uno',1); insert into vn values ('due',2); insert into vn values ('tre',3); commit;
The following function bulk collects the values of the table
vn and returns it as a collection.
create or replace function fct_t return t_varchar_number as ret t_varchar_number; begin select varchar_number(v,i) bulk collect into ret from vn; return ret; end; /
The function in action
set serveroutput on size 10000 declare x t_varchar_number; begin x := fct_t; for r in (select * from table(cast(x as t_varchar_number))) loop dbms_output.put_line(r.v  ', '  r.i); end loop; end; / uno, 1 due, 2 tre, 3
Cleaning up...
drop type t_varchar_number; drop type varchar_number; drop function fct_t; drop table vn; Example #3
This example demonstrates that bulk collects completly overrides (or deletes) the content of the variable selected into prior to the select.
create table bc_3 ( a number, b number ); insert into bc_3 values (1, 1); insert into bc_3 values (2, 1); insert into bc_3 values (3, 1); insert into bc_3 values (4, 2); insert into bc_3 values (5, 2); insert into bc_3 values (6, 2); insert into bc_3 values (7, 2); insert into bc_3 values (8, 3); insert into bc_3 values (9, 3); declare type numbers is table of number; n numbers; begin select a bulk collect into n from bc_3 where b=1; select a bulk collect into n from bc_3 where b=3; for i in 1 .. n.count loop dbms_output.put_line(n(i)); end loop; end; /
As the output shows, the result of the first bulk collect (
where b=1 ) is completely overwritten by
the second bulk collect (where b=3 ):
8 9 Further links
See also bulk insert.
If bulk collects are used together with a explicit cursor, the
%bulk_rowcount cursor attribute can be used to determine the
number of rows that were fetched.
