1、Oracle存储过程实例
7 \: y4 C/ E+ z) r0 } JCREATE OR REPLACE PROCEDURE proc_page
- {% `% Q. t7 s% i4 m, r4 p6 M, {(
% j) r8 W5 F X* y1 q* _ p_tblName IN VARCHAR,9 f' I/ }! A' [# Q' n' p5 C7 `' j0 b. x
p_fields IN VARCHAR,6 v. p9 S- w; s2 ^" ?5 _
p_order IN VARCHAR,5 E0 {7 i; Z- J) u( ~$ U" H; p
p_pageSize IN INT := 10,: ^# H' P c k' j* |" T
p_pageIndex IN INT DEFAULT 1," p/ q* p% M' R7 v. o" J Y% Q
p_rowCount OUT INT,
) b% a$ \( k7 H. ~) _# t p_returnDesc OUT VARCHAR,. ~ l0 l, H. g! i% o6 r1 J! \
p_sel_cur OUT sys_refcursor /*在oracle中不能像mssql那样直接返回结果集查询结果必须以游标形式返回*/
7 U7 h; F& E; n) h/ Q7 w, V6 W, V0 F" I# ]3 p
AS1 t. I, ~6 n/ e H" K8 x
/*AS 到 Begin部分用于声明我们在存储过程中可能用到的变量*/
( \! `7 A" u& n; t; { v_sqlCount VARCHAR(200);
, v S* v/ e' k a v_sql VARCHAR(300);% M) b% X% x+ x0 f/ {7 ~
v_fields VARCHAR(300);
+ I; s+ x$ S& I6 h- yBEGIN
$ D8 Q' b$ r, j6 m# B IF(length(trim(p_tblName)) = 0) THEN
6 O0 k6 v& w2 ]* y! S& J1 b: B; I p_returnDesc := '表名必须输入!';& _# x' Q4 A! s; N8 L- y
RETURN;
0 j E4 I1 `4 h; c: M END IF;' H: w3 ]; O% j+ Y0 a- ~
0 J/ E& K. t# P0 m4 M8 c
, T& L0 x3 l/ B; N! ]5 R
v_sqlCount := 'select count(*) from '||p_tblName;
( K# d( c, w% w, W& V /**动态执行sql语句并将执行结果保存到一个变量中*/
% _- R/ @2 d X" n execute immediate v_sqlCount INTO p_rowCount;& e& C# M! w7 D& T
8 \+ E/ Z9 g* z- P1 w% f' V/ G% W7 J' G5 A
/**执行分页查询*/" @# C3 v7 m4 `& A& n6 L1 ?7 P5 m
IF(TRIM(p_fields)='*') Then1 F# `" `! B8 }2 M0 m; I
v_fields := p_tblName||'.'||p_fields;
D( ]1 v O2 h0 R# j" i+ U( U& E; E END IF;* X7 b& y+ \/ F
IF(TRIM(p_fields)!='*') Then9 E- N! \- j' B/ u. R
v_fields := p_fields;
& k) W- I& f; I2 v6 d END IF;# \1 f1 |, I5 I: w7 ~5 \4 @6 _) P
% {# r( U' d8 b! c3 }0 c- ^6 C* \
5 q1 C* v' S# g, o; x# f) }2 u- W
v_sql := 'select * from (select '||v_fields||',rownum rn from '||p_tblName||' where rownum ';3 z2 i) [3 r k/ A$ B' ^
v_sql := v_sql || (p_pageIndex-1)*p_pageSize;: L% q3 M6 w h2 q
DBMS_OUTPUT.put_line(v_sql);9 W( ^% v5 L" D
OPEN p_sel_cur FOR v_sql; /**将查询结果放入游标中*/
8 l3 k5 j2 I( I3 X" kEND;
/ s/ W+ R. B6 L! N; p2、MySQl存储过程实例----(对比Oracle) ?' d9 l7 t% ^; e( t4 y( K' K
drop procedure if exists proc_page;
/ L q; A7 L# G% n) f( f" k/**更改执行标识符,默认情况是分号,此处我们将其更改为$$*/
$ m5 A E$ M# @! S5 a" Cdelimiter $$/ O- o) v$ {( z6 @& e& }! r D
CREATE PROCEDURE proc_page
# \- m; N& H J9 K+ M(/ f) v' T: s1 \! W: A
p_tblName VARCHAR(255),
" M& K0 Z6 |+ W4 y1 X' L6 C9 |; Kp_fields VARCHAR(255),8 H+ \) ^5 U3 a. D! R& p6 u- \
p_order VARCHAR(255),
: g# ^2 r* {+ V7 @4 a( rp_pageSize INT,3 r% B% p$ C6 s, c& ^. e) l* B
p_pageIndex INT,
+ o" ]4 ^4 q5 h2 M: S6 gp_where VARCHAR(300),$ B o6 k' P K9 n$ n' t/ x3 K7 n
OUT p_returnDesc VARCHAR(500),
5 h% b% G U# A- l" U4 _OUT p_rowCount INT
4 c) Y" {% M! Q* J- V2 z8 u# V)
% J' i6 e4 E& r* \6 H' M1 T! c- SBEGIN, h6 z# O1 [9 w8 U0 H! \5 u0 Z
/** 参数检查*/% e' q, u0 q2 n5 m
IF(LENGTH(LTRIM(RTRIM(p_tblName))) = 0) THEN
\2 v7 V* {4 R a SET p_returnDesc := '请输入表名!';2 x! L5 r( N9 f- o" j) q+ k
END IF;* B, H# B {; v T, v
/**统计记录总数*/
9 ?! x' _- K# w( Q: G, \1 o4 j SET @count_sql := concat('select count(*) into @t_rowcount from ',p_tblName);8 _% j: b5 r, G% v7 s1 S& r
IF(LENGTH(LTRIM(RTRIM(p_where))) > 0) THEN9 d& U5 \7 P& |; u: R# n
SET @count_sql := concat(@count_sql,' where ',p_where);
5 Z5 W7 d& s# d* z& SEND IF;# z* T: u( G' E+ k- c
/**执行动态语句*/6 p7 h7 ~: E8 d" C% M3 g* E, s
PREPARE stmt_count From @count_sql;& w$ ~" Q' Q3 d* r
EXECUTE stmt_count;0 C6 ?4 j: G3 \
DEALLOCATE PREPARE stmt_count; a$ {8 [2 H7 @2 K* f7 D& r0 V
/**将用户变量的值赋给输出变量*/
& c1 S* {, A( B$ u2 zSET p_rowCount := @t_rowcount;; v8 J# d$ A$ ]* y6 q
/**获取分页数据*/
7 z, I. S: D& M+ D% J* `2 n$ H1 xSET @sql := concat('select ',p_fields,' from ',p_tblName);& h" ~8 w* \4 r* L9 \4 U6 G# r: f3 e
IF(LENGTH(LTRIM(RTRIM(p_where))) > 0) THEN% p N# ?# J2 i
SET @sql := concat(@sql,' where ',p_where);
0 I ^# l- _) O/ [& D' V, aEND IF;' i6 b2 u; J' B9 v
SET @sql := concat(@sql,' limit ',(p_pageIndex-1)*p_pageSize,' , ',p_pageSize);
Y4 a. b. B ~. l
; ^/ b* u9 D9 P" m* W
& z: D9 u0 B$ W) O4 Z! B$ VPREPARE stmt_page FROM @sql;6 f8 }# @" h* V. {8 x+ }9 {
execute stmt_page;* m; V2 R- k2 o
DEALLOCATE PREPARE stmt_page;
+ N% x+ _+ `! I: ?1 o) CEND;
; J, l9 k: o# v$$
- |, M5 z$ j/ |% U% \delimiter ; |