|
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...
... 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');
An anonymous block is executed. Its declaration part
defines two nested-table 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
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;
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 2nd 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 nested-table type.
However, the type of the elements in the nested-table 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:
The nested-table 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 vnand 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
|
Friday 22 June 2012
BULK COLLECT
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment