Oracle数据库存储过程

会计考友

1、Oracle存储过程实例
CREATE OR REPLACE PROCEDURE proc_page
(
& E( u& `) W# U5 p       p_tblName IN VARCHAR,
- z4 f2 [% f* N- i# i       p_fields IN VARCHAR,
6 s7 S5 X  X# O2 n4 j" C& e       p_order IN VARCHAR,
       p_pageSize IN INT := 10,
% v0 X, a' d# V( Y' n1 f& J3 v, r       p_pageIndex IN INT DEFAULT 1,
/ W% t4 R# O/ N' H' R       p_rowCount OUT INT,
       p_returnDesc OUT VARCHAR,
4 e8 D, _7 E/ [4 A; y: y1 B       p_sel_cur OUT sys_refcursor   /*在oracle中不能像mssql那样直接返回结果集查询结果必须以游标形式返回*/
+ K$ l# Q" _& t- T8 ~)
7 G8 d1 o, X% wAS
% S" b3 y  H3 Y  x6 ]+ H) n       /*AS 到 Begin部分用于声明我们在存储过程中可能用到的变量*/
* b4 m8 d# ~: u! o* h       v_sqlCount VARCHAR(200);
       v_sql VARCHAR(300);
       v_fields VARCHAR(300);
BEGIN
5 G; y, H/ ^. H  J3 h0 F       IF(length(trim(p_tblName)) = 0) THEN
( [' s0 B2 Q$ ?4 ^/ o               p_returnDesc := '表名必须输入!';
4 F5 J6 o6 N2 L8 V' m, W               RETURN;  
) y8 M' ~+ Z9 n+ ~: h# b, |" _       END IF;


       v_sqlCount := 'select count(*) from '||p_tblName;
       /**动态执行sql语句并将执行结果保存到一个变量中*/
# j0 ^4 i9 W+ }  S" X       execute immediate v_sqlCount INTO p_rowCount;
: l$ |1 w  `! o; T) ?3 ~

       /**执行分页查询*/
       IF(TRIM(p_fields)='*') Then
                v_fields := p_tblName||'.'||p_fields;
       END IF;
       IF(TRIM(p_fields)!='*') Then
0 f1 I% n' e( }  c% X) D% N% Z                v_fields := p_fields;
       END IF;

! ~! b4 h- ~2 f  p6 b/ t
       v_sql := 'select * from (select '||v_fields||',rownum rn from '||p_tblName||' where rownum  ';
0 E( g. \; x7 A" I/ V$ c       v_sql := v_sql || (p_pageIndex-1)*p_pageSize;
' N. X. ?' t( Y3 ]; V! d/ X       DBMS_OUTPUT.put_line(v_sql);
       OPEN p_sel_cur FOR v_sql;  /**将查询结果放入游标中*/
- E, W) r( T  s, i2 @3 p, xEND;
2、MySQl存储过程实例----（对比Oracle）
drop procedure if exists proc_page;
/**更改执行标识符，默认情况是分号，此处我们将其更改为$$*/
delimiter $$
CREATE PROCEDURE proc_page
" x+ y6 Q1 s& h(
, f! k8 O) F8 h/ b4 \# p7 @p_tblName VARCHAR(255),
p_fields VARCHAR(255),
$ j" v5 ^' Z+ V$ Ap_order VARCHAR(255),
p_pageSize INT,
p_pageIndex INT,
7 V) A% F3 m3 c, t- T- |p_where VARCHAR(300),
OUT p_returnDesc VARCHAR(500),
OUT p_rowCount INT
. b% S' v6 E& J  Z" B; L/ `* Z)
BEGIN
      /** 参数检查*/
2 J* z/ {1 \! Z& hIF(LENGTH(LTRIM(RTRIM(p_tblName))) = 0) THEN
  SET p_returnDesc :=  '请输入表名!';
0 y2 T  C# ~4 E1 l/ O- X" I7 A6 {END IF;
/**统计记录总数*/
0 |9 [8 Z' H+ z1 }% N# u  SET @count_sql  := concat('select count(*)  into @t_rowcount  from ',p_tblName);
& |! I5 B8 D9 fIF(LENGTH(LTRIM(RTRIM(p_where))) > 0) THEN
  SET @count_sql := concat(@count_sql,'  where  ',p_where);
/ F. l# R" i; f# R% uEND IF;
% f; e% J# M0 [$ }  g- ?0 |/**执行动态语句*/
PREPARE stmt_count From @count_sql;
EXECUTE stmt_count;
DEALLOCATE PREPARE stmt_count;
/**将用户变量的值赋给输出变量*/
4 \7 T: v/ J7 y% P* mSET p_rowCount := @t_rowcount;
/**获取分页数据*/
2 \8 Y3 ^, X" I2 W& A& v( ASET @sql := concat('select  ',p_fields,'  from ',p_tblName);
IF(LENGTH(LTRIM(RTRIM(p_where))) > 0) THEN
  SET @sql := concat(@sql,'  where  ',p_where);
3 j* \. D# t& V" |% @END IF;
0 ^0 }; @9 w+ p# FSET @sql := concat(@sql,' limit ',(p_pageIndex-1)*p_pageSize,' , ',p_pageSize);
' p4 |3 j) R1 B/ a. c: j# {
. `5 O) V' y. W6 b' O
) @3 Q9 l8 n! w: P. E8 r: D8 {PREPARE stmt_page FROM @sql;
execute stmt_page;
/ h/ @& q( }$ A3 J$ pDEALLOCATE PREPARE stmt_page;
END;
$$
, G) _8 N# m9 t* f4 c% Tdelimiter ;