oracle存储过程返回数组的方法

作者:网络 来源:佚名 更新时间:2008-11-17 15:07:42 点击:

  oracle 存储过程返回数组的方法:

  1.建立包

create or replace package test is
type filename_array is table of varchar2(1);
filename filename_array;
end test;

  2. 建立存储过程

create or replace procedure test_array(v_cfjg out test.filename_array ) is
begin declare i number;
d_cfjg dic_cfjg%rowtype;
-- d_nr dic_cfjg%rowtype;
cursor c1 is select * from dic_cfjg;
begin
i:=0;
v_cfjg := test.filename_array(); --数组初始化

  open c1;

loop fetch c1 into d_cfjg;
exit when c1%notfound ;
i:=i+1;
v_cfjg.extend;
-- dbms_output.put_line(to_char(d_cfjg.dm));
v_cfjg(v_cfjg.count):=d_cfjg.dm;
dbms_output.put_line(v_cfjg(v_cfjg.count));
-- 测试
-- fetch c1 into d_cfjg;
-- exit when c1%notfound ;
end loop;
end;
exception
when too_many_rows then
dbms_output.put_line('too_many_rows');
when others then dbms_output.put_line(sqlerrm);
end test_array;