oracle 存储过程返回数组的方法:
8 i& p7 j, N8 U' }' _9 ^6 g 1.建立包
1 D8 m/ E6 N1 d5 b( o create or replace package test is
7 Y% \5 E. t4 C# s4 q4 t( X TYPE filename_array IS TABLE OF varchar2(1);- u* q8 u' B7 n" A/ ^2 t; T/ e
filename filename_array;
8 ^& p" t& |" [: n3 l; K end test;8 w' r% t3 [3 R6 ~, K
2. 建立存储过程4 E! L. p3 n. H e/ b
create or replace procedure test_array(v_cfjg out test.filename_array ) is
& h3 ]9 q4 v- Q3 Z/ |0 r begin DECLARE i number;
' x' K0 l# e2 y% U D_cfjg dic_cfjg%rowTYPE;
$ e% m# t( L, E2 D -- D_nr dic_cfjg%rowTYPE;: @ L; k6 f- Z2 t
cursor c1 is SELECT * FROM dic_cfjg;
/ W) ~4 P# }! G; \1 p; h BEGIN
2 a! j+ H7 r3 L6 d6 G3 j i:=0;
9 i8 s+ c' P8 e3 R: F v_cfjg := test.filename_array(); --数组初始化
0 v3 D% g- y) r" [, X0 s8 {+ H open c1;9 m X2 R# K) h0 |: w4 f
LOOP fetch c1 into D_cfjg;
1 @! M7 i+ ^! F! f- `: K6 n: y EXIT WHEN c1%NOTFOUND ;
7 d7 `% ~8 J- z i:=i+1;' Y9 H" F; v, Z6 c
v_cfjg.EXTEND;. ]9 E. a) D8 ~( |- x: b9 B
-- DBMS_OUTPUT.PUT_LINE(TO_CHAR(D_cfjg.dm));: v8 ]% Y" d* v& Y4 Y
v_cfjg(v_cfjg.count):=D_cfjg.dm;; o# h$ {" ?( n0 F7 S/ m
DBMS_OUTPUT.PUT_LINE(v_cfjg(v_cfjg.count));
5 S# n+ `/ q0 j. m+ v -- 测试
; o& h" R& a. U7 p' h4 l8 d0 U5 N -- FETCH C1 INTO D_cfjg;- A* r* K/ |2 [* E5 V
-- EXIT WHEN c1%NOTFOUND ;" s5 [' j4 g+ z/ P0 d
END LOOP;
) R0 R; S2 d6 U6 i7 u end;* h' A/ N0 A; c
EXCEPTION. x+ I9 o! J, y" E3 t2 q. s( U
WHEN TOO_MANY_ROWS THEN. J- | p$ V2 y4 P5 ?( J& p2 }' f
DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');' Z E# ~* t3 u# L% v4 p( b- R
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(sqlerrm);
7 e+ D. c% ~+ M end test_array; |