oracle 存储过程返回数组的方法:
. _3 ]3 L; z: v% h9 m8 U; F 1.建立包* i/ [2 o2 c! U' _ r
create or replace package test is4 K- ~. `. K/ c) W {9 |2 e, u) k) a1 d
TYPE filename_array IS TABLE OF varchar2(1);9 _7 P) P/ p3 V8 a
filename filename_array;, ]5 J* v* {3 r* N( O- |6 X
end test;
0 j8 w! i% d) ^6 O2 _# o4 h 2. 建立存储过程
% q1 J, C+ `3 l9 i! ?$ u create or replace procedure test_array(v_cfjg out test.filename_array ) is2 e" O5 b% _3 Y
begin DECLARE i number;1 b+ i3 f) w B5 h: J5 z
D_cfjg dic_cfjg%rowTYPE;. _. p0 l- H; X0 Q& x
-- D_nr dic_cfjg%rowTYPE;
8 E j, N) ]/ A- g) ^& K/ S& Z cursor c1 is SELECT * FROM dic_cfjg;
$ Y- R5 p: M6 h2 M8 }% f$ r1 T$ o BEGIN
; ~" R, C! h2 G3 D i:=0;' k' Q& l) q P/ E. `
v_cfjg := test.filename_array(); --数组初始化
S4 z- e" v8 a open c1;
+ x6 `& z) a3 _5 i: c LOOP fetch c1 into D_cfjg;) l5 t6 y) N# L$ U) ^$ Q$ b
EXIT WHEN c1%NOTFOUND ;* B1 Q ?1 s, S3 ?6 ^2 a( {
i:=i+1;: N; I2 s+ ^4 W- e
v_cfjg.EXTEND;
4 n0 }! ?% G1 y4 _1 ^ -- DBMS_OUTPUT.PUT_LINE(TO_CHAR(D_cfjg.dm));
: \) u7 t$ l. `. `& Y& b v_cfjg(v_cfjg.count):=D_cfjg.dm;
, W% q* k) x& T- a: d DBMS_OUTPUT.PUT_LINE(v_cfjg(v_cfjg.count));# a2 H9 |) W$ x( N; K
-- 测试5 c+ Z0 l t: C- a5 d
-- FETCH C1 INTO D_cfjg;7 ^* D+ P: v- [& N6 P
-- EXIT WHEN c1%NOTFOUND ;
! f% P- Z$ D! h6 s+ f g" ?; ~ END LOOP;
4 g% T6 o. ?3 D& P+ y end;
) A+ U1 @; [. c C. v- t6 t2 P EXCEPTION0 ~# `6 J" o7 g; F# }
WHEN TOO_MANY_ROWS THEN* c2 Z$ C* l6 P( I, y9 {1 `2 K9 O6 {/ K
DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');5 A, f/ z" q l8 ?
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(sqlerrm);
( u9 u$ R, \8 x1 u1 C( Q; k X* J end test_array; |