Oracle中返回结果集的存储过程
! E3 m6 ]& L4 y7 f
3 d- B& a' J; J& [' V1 m2 X$ i h Oracle不像SQLServer那样在存储过程中用Select就可以返回结果集,而是通过Out型的参数进行结果集返回的。实际上是利用REF CURSOR3 v/ V! E8 B# `% P- c" ]
7 D! @; b1 `7 U+ j% B --procedure返回记录集:
: |# i/ W- @/ ^' B5 p9 e# K; P5 T
2 ?) f$ v- p, t2 V0 M ----------------------声明一个Package--------------8 H; `" Y3 N1 g+ N% x, O5 |2 ~, J
$ F/ ~. k8 r# d/ g
CREATE OR REPLACE PACKAGE pkg_test; h: f- h# T/ J( b# d' q
8 P5 T1 q3 a$ ]; |' L AS
9 K# \/ ]( k! |0 A$ V v
* n. F6 ]2 J- a+ O3 ^# { TYPEmyrctypeIS REF CURSOR;' M1 X$ B, L5 H) _- H* M
. b( E6 [0 ]0 p/ T& X+ j1 ~ ` PROCEDURE get_r(p_id NUMBER,p_rc OUT myrctype); --Package中声明名为get 的Procedure(只有接口没内容)
+ S+ Y* |* R/ Y ; D$ U Y% O- f. s5 n. b$ e0 a
END pkg_test;% L0 S& C9 \( v" `% Y: w' q
, k" R+ Z& ^4 `
-----------------声明Package Body,即上面Package中的内容,包括Procedure get---------------------; y1 {6 B Q" J
5 P" I, N5 @$ J/ G; \. k CREATE OR REPLACE PACKAGE BODY pkg_test
. ?/ y) J6 y0 x# O- F
& Q8 k1 L& C! k0 G AS N4 q0 @& z* \4 v
2 q2 b& t+ \/ R5 w PROCEDURE get_r(p_id NUMBER,p_rc OUT myrctype)
6 D0 W) B6 ^5 A 3 o- |2 E. w" \+ P& N" A# l
IS s/ b# ]1 K& C4 v, g9 ?5 g) G
( W/ j6 j. L: L. }% _) J; \
sqlstr VARCHAR2 (500);- V8 f1 `% z9 b
2 y" F. m$ o5 D6 F; P
BEGIN) T) j8 O7 p3 J6 H
n6 F/ _* i8 L8 ~% D# j# I
IF p_id = 0 THEN
1 K6 z' U- g$ }% l' d+ z
' ]( V% X1 J- e+ s+ K OPEN p_rc FOR9 R0 X9 B: ?$ x6 |
+ J. B6 |$ b0 s _/ t8 M0 @
SELECT ID, NAME, sex, address, postcode, birthday
! N3 A# ^; R& }% M* j0 o 3 ?! v, l$ }: C) P% L* L
FROM student;
. m6 Q/ N4 [, T6 _! z6 ]* ^6 j" T
& Y( z+ a. p& o1 r; u ELSE6 ]& C! `. c( O9 K; e# @
, \! c( c6 G( z6 Q' e
sqlstr :=
- S+ i8 q. g$ G2 A ( ^, n4 }$ i: C5 U9 [4 t E$ ^0 I
'select id,name,sex,address,postcode,birthday3 f5 p+ Z9 t6 u3 u" t8 i- x Q! c
4 q) G& Q7 g; t; d
from student where id=:w_id'; --w_id是个参数,* t& I9 j( K& x. K
/ r1 M; W( x' `% r! q$ g n --以下 p_rc是个REF CURSOR游标类型,而且是OUT型参数,即可返回一个记录集了。USING p_id就是替换上面SQL中:w_id值拉:)
H4 P+ S5 e3 C7 c- o7 s
' M- Y: |& P( g* y; l8 l OPEN p_rc FOR sqlstr USING p_id;
/ U% C) z* d. W8 d3 Y8 y1 c8 `
2 ` Q- T: A% G. d9 F. J' V END IF;3 W; m8 ?! ^% Q
' L. x+ n" X0 F) `* P, a END get;
2 E/ l) B, @9 O5 b
2 U5 @- p: Q) r; Y3 m0 \& r END pkg_test;) G1 v# V5 r d0 X, O. n
, m8 H) u6 M4 w9 Y8 s! A
--function返回记录集的例子,原理和上面相同,而是用function的return值来返回记录集。
& `" `( K# D8 F1 ^7 N% ? 8 c- n& n9 M: g1 j
函数返回记录集:" \' D, H t6 p" f3 w1 B& s8 @3 W: B
- w6 ^; e: r$ d
建立带ref cursor定义的包和包体及函数:+ `/ e+ b6 a0 m. O @$ D
+ _$ U" h1 e9 B( f! `, ~ CREATE OR REPLACE
2 ~% S2 c2 {: t5 ~+ |% A8 I, p
. i$ p% A0 }. U2 p package pkg_test as
# }4 {+ e6 r0 J8 V# y
! v1 F' i7 U% E) D type myrctype is ref cursor;! [0 o# T* a% _
% C( G8 O* t$ f2 Y) }2 r" n function get_r(intID number) return myrctype;+ U. Z# D* w+ N5 _
5 \, Q8 g; o( h, j" T' F) J end pkg_test;1 B3 ~4 t! M. z' R3 Z+ Z
! `- [/ k: B* R3 q
/5 k U3 C3 S, Q6 U* n" `
2 n: F$ k0 F( G# Q CREATE OR REPLACE+ R/ E# }2 N. [: ]$ M! [
2 [; S$ \) }# Q. S6 E, I6 ^1 v package body pkg_test as
, C1 J. i' n* `8 r- y # r0 r; N, \& q
--函数体
4 \, A& ]3 t4 ], X4 e4 g9 F ' L2 l4 p' S" ]
function get_r(intID number) return myrctype is
; ?7 g, \; R& a% x. J
4 [7 u* h' p' [. F: X rc myrctype; --定义ref cursor变量
8 J) Z; s4 W. U, X % m7 t1 n, U* q, \8 |6 [4 o# l
sqlstr varchar2(500);
V" t. S4 u( ~9 f, l$ Q9 N8 h
; |+ m9 g& e$ ?& p& z4 U begin
# G/ `: Z( p; _0 i7 f- M( Q& `
; k5 Z. E% @+ b; E% E if intID=0 then
0 V2 n6 o2 p: N/ C # @: m7 D/ {5 L- W ~" a4 v
--静态测试,直接用select语句直接返回结果/ Z& c0 \3 n/ A6 J) H
- d6 G9 M7 ]# ~/ G& q; ^. \
open rc for select id,name,sex,address,postcode,birthday from student;3 |3 V# K" z& z
& x+ A" Y+ Z8 q+ t else
f l' n: G' I, e 9 \6 V5 `) i+ f# a8 Z: U( F
--动态sql赋值,用:w_id来申明该变量从外部获得
7 C7 s, L2 P1 M* v/ g5 c: C
/ s4 N+ |. w7 P- G$ S3 M4 x sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id';
, e3 f) V9 i2 R% M2 M" e" ?9 ?+ L2 J
$ d& y* H- d( D6 I5 M --动态测试,用sqlstr字符串返回结果,用using关键词传递参数* h, P) P! d4 ]) f% D1 W
) i" a' P8 b- l5 R% y0 O7 D- P open rc for sqlstr using intid;
# m- C$ P0 ~: R4 h( `! { w/ x 2 Z* ~* K9 ]# @8 J7 i
end if;$ X; d; {+ Z5 S- F+ h1 u
/ E6 R O/ g, B# k' |2 O
return rc;
/ E( N2 M1 g7 d' i
, M% m; J2 u$ l' n! ] end get;* h- r% ^7 d! @' P# n& N) j4 [
5 c$ C+ V v: c2 I ~( [
end pkg_test; |