概述

网上有很多实现分页的存储过程，刚好项目有这方面需求，顺便做个总结，记录下做备忘！

下面基于Oracle11g环境。

需求：编写一个存储过程，要求可以输入表名、每页显示记录数和当前页，返回总记录数、总页数和结果集

1、oracle的分页写法

select t1.*,rownum rn from (select * from emp) t1;
select t1.*,rownum rn from (select * from emp) t1 where rownum<=10;

在分页时，大家可以把下面的sql语句当作一个模板使用

--查询第5行到第10行的数据(错误写法)
select rownum, empno from (select rownum rn , empno from emp) a where a.rownum > 5 and a.rownum < 10;
--查询第5行到第10行的数据(正确写法)
select rownum, empno from (select rownum rn , empno from emp where rownum<10) a where a.rn>5;

2、编写存储过程

开发一个包，在该包中，定义类型test_cursor，是个游标

create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;

开始编写分页的过程

create or replace procedure fenye(tableName in varchar2, --表名
 pageSize in number, --一页显示记录数
 pageNow in number, --当前页
 myrows out number, --总记录数
 myPageCount out number, --总页数
 p_cursor out testpackage.test_cursor --返回的记录集
 ) is
 --定义部分
 --定义sql语句 字符串
 v_sql varchar2(1000);
 --定义两个整数
 v_begin number := (pageNow - 1) * pageSize + 1;
 v_end number := pageNow * pageSize;
begin
 --执行部份
 v_sql := 'select * from (select t1.*,rownum rn from (select * from ' || tableName || ') t1 where rownum<=' || v_end || ') where rn>=' ||v_begin;
 --把游标和sql关联
 open p_cursor for v_sql;
 --计算myrows和myPageCount
 --组织一个sql
 v_sql := 'select count(*) from ' || tableName;
 --执行sql，并把返回的值赋给myrows;
 execute immediate v_sql
 into myrows;
 --计算myPageCount
 if mod(myrows, PageSize) = 0 then
 myPageCount := myrows / PageSize;
 else
 myPageCount := myrows / PageSize + 1;
 end if;
 --关闭游标
 close p_cursor;
end;

这里还是有些瑕疵的，还得再改进下，后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~