oracle 存储过程返回数组的方法:
5 \! F) y% Z o" L) d6 _$ V 1.建立包 W' s- b1 T6 q* R* P& W$ \
create or replace package test is
( c! ~" o/ O1 p" x" u: Z0 e TYPE filename_array IS TABLE OF varchar2(1);
* A" Z5 m& ~7 Z filename filename_array;
/ ^1 |3 @8 k, f3 K6 ?) c+ t end test;+ j7 y7 j4 T" Q# u, D: \
2. 建立存储过程
. b; z' S; r! I6 g, b/ a0 ] create or replace procedure test_array(v_cfjg out test.filename_array ) is
# S6 t4 v- _5 |2 d$ E } begin DECLARE i number;
) f& N- c9 @6 Z, H- h9 Q1 D5 O, I. P D_cfjg dic_cfjg%rowTYPE;
. f. V- ^2 w( h- p' L1 g -- D_nr dic_cfjg%rowTYPE;
7 }& u9 U8 d2 p! n- [. } cursor c1 is SELECT * FROM dic_cfjg;4 J; f0 e$ {, r; F! @
BEGIN9 l& g% A1 K6 `1 J: P; ]( L9 `( b
i:=0;
5 p8 ]/ g. D, V$ Q; |5 o v_cfjg := test.filename_array(); --数组初始化
. y% a1 r" p% ?* t. G9 z7 P open c1;, U6 W/ o& v3 B$ ~; i, j9 H. S
LOOP fetch c1 into D_cfjg;2 j1 v& A$ M/ ?/ F, f8 g- B
EXIT WHEN c1%NOTFOUND ;) G3 o' H3 `( Y& g- N0 e
i:=i+1; x; }% F: N5 K( n% h2 W g
v_cfjg.EXTEND;
# V' `6 |1 _* i8 F1 n$ K* m -- DBMS_OUTPUT.PUT_LINE(TO_CHAR(D_cfjg.dm));% ~7 L) P4 w% O
v_cfjg(v_cfjg.count):=D_cfjg.dm;
7 e( v, P+ a. }1 B- `" H DBMS_OUTPUT.PUT_LINE(v_cfjg(v_cfjg.count));' l; p7 c D' g9 p5 V& b. H
-- 测试
+ I9 M$ o3 H, W, T# S- k _ -- FETCH C1 INTO D_cfjg;
& D. ?! W8 O% C, _& B% N -- EXIT WHEN c1%NOTFOUND ;/ V8 m; |2 m7 a
END LOOP;
/ x, Z7 l5 t- d B% Z end;9 x/ m/ {0 u7 m; l
EXCEPTION
' L- w8 [& v4 e5 a" J, k WHEN TOO_MANY_ROWS THEN
2 A6 z! N, |! i DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');
% ?3 w+ N. a, n$ t8 g WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(sqlerrm);
# s& `/ _; E' `. Z2 ] end test_array; |