Oracle学习笔记（一）通用分页存储过程

前言

最近在学习Oracle的存储过程，其中一个很实用的点就是利用存储过程实现一个通用的分页模型。在此记录下我的学习笔记。

 

SQL代码

CREATE OR REPLACE PROCEDURE P_PagingCursor
/******************************************
功能描述:
  通用分页存储过程
输入：
  i_table in VARCHAR2,			--要进行分页查询的表名
  i_page in INTEGER,			--当前页数
  i_pagesize in INTEGER,		--每页要查询的记录数
  i_conditions in VARCHAR2	        --查询条件
输出:
  o_cursor out sys_refcursor,	        --游标类型，返回分页结果集
  o_rows out INTEGER，	                --查询共有多少条记录
  o_result out INT    	                --成功返回>0,失败返回<=0 
创建:
  Apple_Coco
********************************************/
(
	i_table in VARCHAR2,
	i_page in INTEGER,
	i_pagesize in INTEGER,
	i_conditions in VARCHAR2,
	o_cursor out sys_refcursor,
	o_rows out INTEGER,
	o_result out INTEGER    --成功返回>0,失败返回<=0
) IS
	--定义需要的变量
	v_sql VARCHAR2(4000);
	v_sql_select VARCHAR2(4000);
	v_start INTEGER;
	v_end INTEGER;
BEGIN
    --初始化操作标识
    o_Result := 0;
    IF(NVL(i_table, 'null') = 'null') THEN
	    o_Result := -3;  --要查询的表名有误
	    Return;
    ELSIF(i_page <= 0) THEN
	    o_Result := -2;  --当前页数有误
	    Return;
    ELSIF(i_pagesize <= 0) THEN
	    o_Result := -1;  --每页要查询的记录数有误
	    Return;
    END IF;
			
    --计算v_start和v_end是多少
    v_start := i_pagesize * (i_page-1) + 1;
    v_end := i_pagesize * i_page;
    v_sql := 'select t2.* from (select t1.*, rownum rn from (select * from '||i_table||' where '||NVL(i_conditions, '1 = 1')||') t1 where '||NVL(i_conditions, '1 = 1')||' and rownum <= '||v_end||') t2 where '||NVL(i_conditions, '1 = 1')||' and rn >= '||v_start;
		
    --打开游标，让游标指向结果集
    open o_cursor for v_sql;
    
    --查询结果集共有多少条记录
    v_sql_select := 'select count(*) from '||i_table||' where '||NVL(i_conditions, '1 = 1');
    execute immediate v_sql_select into o_rows;
    
    o_Result := 1;

EXCEPTION
  WHEN OTHERS THEN 
    o_result := -4;
    ROLLBACK; RETURN;
END P_PagingCursor;

 

后记

在下一篇，我会介绍如何用Spring Data JPA调用有返回结果集的存储过程。