Oracle数据库存储过程

会计考友 · 发表于 2012-8-4 14:06:19

1、Oracle存储过程实例
CREATE OR REPLACE PROCEDURE proc_page
(
   p_tblName IN VARCHAR,
   p_fields IN VARCHAR,
   p_order IN VARCHAR,
   p_pageSize IN INT := 10,
   p_pageIndex IN INT DEFAULT 1,
   p_rowCount OUT INT,
   p_returnDesc OUT VARCHAR,
   p_sel_cur OUT sys_refcursor /*在oracle中不能像mssql那样直接返回结果集查询结果必须以游标形式返回*/
)
AS
   /*AS 到 Begin部分用于声明我们在存储过程中可能用到的变量*/
   v_sqlCount VARCHAR(200);
   v_sql VARCHAR(300);
   v_fields VARCHAR(300);
BEGIN
   IF(length(trim(p_tblName)) = 0) THEN
            p_returnDesc := '表名必须输入!';
            RETURN;
   END IF;

   v_sqlCount := 'select count(*) from '||p_tblName;
   /**动态执行sql语句并将执行结果保存到一个变量中*/
   execute immediate v_sqlCount INTO p_rowCount;

   /**执行分页查询*/
   IF(TRIM(p_fields)='*') Then
            v_fields := p_tblName||'.'||p_fields;
   END IF;
   IF(TRIM(p_fields)!='*') Then
            v_fields := p_fields;
   END IF;

   v_sql := 'select * from (select '||v_fields||',rownum rn from '||p_tblName||' where rownum  ';
   v_sql := v_sql || (p_pageIndex-1)*p_pageSize;
   DBMS_OUTPUT.put_line(v_sql);
   OPEN p_sel_cur FOR v_sql;  /**将查询结果放入游标中*/
END;
2、MySQl存储过程实例----（对比Oracle）
drop procedure if exists proc_page;
/**更改执行标识符，默认情况是分号，此处我们将其更改为$$*/
delimiter $$
CREATE PROCEDURE proc_page
(
p_tblName VARCHAR(255),
p_fields VARCHAR(255),
p_order VARCHAR(255),
p_pageSize INT,
p_pageIndex INT,
p_where VARCHAR(300),
OUT p_returnDesc VARCHAR(500),
OUT p_rowCount INT
)
BEGIN
   /** 参数检查*/
IF(LENGTH(LTRIM(RTRIM(p_tblName))) = 0) THEN
  SET p_returnDesc :=  '请输入表名!';
END IF;
/**统计记录总数*/
  SET @count_sql  := concat('select count(*)  into @t_rowcount  from ',p_tblName);
IF(LENGTH(LTRIM(RTRIM(p_where))) > 0) THEN
  SET @count_sql := concat(@count_sql,'  where  ',p_where);
END IF;
/**执行动态语句*/
PREPARE stmt_count From @count_sql;
EXECUTE stmt_count;
DEALLOCATE PREPARE stmt_count;
/**将用户变量的值赋给输出变量*/
SET p_rowCount := @t_rowcount;
/**获取分页数据*/
SET @sql := concat('select  ',p_fields,'  from ',p_tblName);
IF(LENGTH(LTRIM(RTRIM(p_where))) > 0) THEN
  SET @sql := concat(@sql,'  where  ',p_where);
END IF;
SET @sql := concat(@sql,' limit ',(p_pageIndex-1)*p_pageSize,' , ',p_pageSize);

PREPARE stmt_page FROM @sql;
execute stmt_page;
DEALLOCATE PREPARE stmt_page;
END;
$$
delimiter ;

		自动登录	找回密码
密码			立即注册

[考试辅导] Oracle数据库存储过程