oracle集合类型应用举例

--condition--
create table t1(id int);
create table t2(id int);
create table t3(id int);

create or replace package aa_test is

-- Author : tom
-- Created : 2010-5-10 17:04:24
-- Purpose :

type tom_re is record(
id t1.id%type);

type tom_insert_tab is table of tom_re index by binary_integer;

tom_insert_array tom_insert_tab;

procedure tom_insert(iv_tom_insert_array in tom_re);

procedure tom_print;

procedure tom_print1;

procedure tom_print2;

procedure tom_print3;

end aa_test;


create or replace package body aa_test is

procedure tom_insert(iv_tom_insert_array in tom_re) is
begin
insert into t3 (id) values (iv_tom_insert_array.id);
end tom_insert;

procedure tom_print is
cursor cur_tom is
select * from t1;
begin
open cur_tom;
loop
fetch cur_tom bulk collect
into tom_insert_array; --这种情况，只循环一遍--

for i in 1 .. tom_insert_array.count loop
dbms_output.put_line('----' || tom_insert_array(i).id);
end loop;

exit when cur_tom%notfound;

end loop;

close cur_tom;
dbms_output.put_line('----' || tom_insert_array.count);
tom_insert_array.delete; --清空数组--
dbms_output.put_line('----' || tom_insert_array.count);
end tom_print;

procedure tom_print1 is
cursor cur_tom is
select * from t1;
begin
open cur_tom;

fetch cur_tom bulk collect
into tom_insert_array; --这种情况，只循环一遍--

for i in 1 .. tom_insert_array.count loop
dbms_output.put_line('----' || tom_insert_array(i).id);
end loop;

close cur_tom;
end tom_print1;

procedure tom_print2 is
cursor cur_tom is
select * from t1;
v_id integer;
begin
open cur_tom;
loop
fetch cur_tom
into v_id; --循环多遍--
exit when cur_tom%notfound;
dbms_output.put_line('--v_id--' || v_id); --这种情况需要放exit后面--
end loop;

close cur_tom;
end tom_print2;


procedure tom_print3 is
cursor cur_tom is
select * from t1;
begin
open cur_tom;

fetch cur_tom bulk collect
into tom_insert_array; --这种情况，只循环一遍--

for i in 1 .. tom_insert_array.count loop
tom_insert(tom_insert_array(i));
dbms_output.put_line('--insert times:--'|| i);
end loop;
close cur_tom;

commit;
end tom_print3;

end aa_test;