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

2024-08-29 13:32:24

字体：大中小

来源：转载

供稿：网友

　　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;