一、使用存储过程返回数据集 ' {& c' \2 Q* _
Oracle中存储过程返回数据集是经由过程ref cursor类型数据的参数返回的,而返回数据的参数应该是out或in out类型的。0 G6 D* X# ~* m8 m& V2 B- c: K
因为在界说存储过程时无法直接指定参数的数据类型为:ref cursor,而是首先经由过程以下体例将ref cursor进行了重界说:6 J% P" P( v2 N& V0 T
create or replace package FuxjPackage is+ E. U6 U- b) v9 |- A+ Q
type FuxjResultSet is ref cursor;! U. i1 b) b1 k% t
--还可以界说其他内容
8 q ?) q$ g, O' ? end FuxjPackage;
1 `. H/ w0 R5 D9 W 再界说存储过程:
, U) |- V9 b. r6 ~/ b create or replace procedure UpdatefuxjExample (sDM in char,sMC in char, pRecCur in out FuxjPackage.FuxjResultSet)
+ b$ z8 _& E' W+ I% w as
, {$ C' ] @0 a1 Q begin
1 P! S# \- t) I6 w8 ^ update fuxjExample set mc=sMC where dm=sDM;
2 k5 m. n: @6 g' @( N if SQL%ROWCOUNT=0 then
4 m1 M4 B1 K& t8 y2 b4 P. C& x/ o rollback;
4 @* Q9 R7 ?( @1 ~5 k; S1 u2 _6 ^( x open pRecCur for p- W( q3 J0 q6 I" x
select '0' res from dual;
0 V i1 q2 g4 h- x' }! I/ w else
% ]+ y6 {2 w! b commit;
" U) Z U5 G% A7 b& S8 e- ` open pRecCur for& H; P; `8 H! V+ R& {6 @) \
select '1' res from dual;
8 t% Q8 ~, m8 E! Q$ n end if;
% {! T t3 O* `+ M$ @9 l end;/ M$ Y" P6 c- Q, S6 x) M/ n- f. _ b
和
/ F; x( m9 {0 n2 V, Y2 u5 @ create or replace procedure InsertfuxjExample (sDM in char,sMC in char, pRecCur in out FuxjPackage.FuxjResultSet)* U. S" `# O% |1 P J
as
( k: B& Y6 H" s' K! i begin9 y' [7 r4 P3 m$ q; o% C
insert into FuxjExample (dm,mc) values (sDM,sMC);
8 ~" F4 K3 V; Y commit;3 y8 z2 l& Q; W9 O+ s' x* M, T, t; f3 B$ k
open pRecCur for
; C1 {/ |1 K4 @# D- L select * from FuxjExample;
1 y3 T" a: l3 r1 f: A0 a' g1 A9 r- }( k7 F6 q7 |( ]/ Z9 M
end; |