Oracle 分页步骤

Oracle 分页步骤

--Oracle分页
定义好的游标

create or replace package chj1_package as
type chji1_cursor is ref cursor;
end chj1_package;

--一步
select t1.*,rownum from (select * from chengji) t1
--二步
select t1.*,rownum rn from (select * from chengji) t1 where rownum <=2
--三步
select * from
(select t1.*,rownum rn from (select * from chengji) t1 where rownum <=2)
where rn>1
--建立一个存储过程
--输入表名 每页显示的条数  当前页
--返回总的条数、页数、结果集
create or replace procedure proc_fenye
(
tableName in varchar2,
everyPageSize in number,
nowPage in number,
allRowCount out number,--总条数
allPageCount out number,--总页数
fy_cursor out chj1_package.chji1_cursor--返回集
)
is--定义部分
v_sql varchar2(1000);
v_begin number:=(nowPage-1)*everyPageSize+1;--开始条数
v_end number:=nowPage*everyPageSize;--结束条数

begin--执行部分
v_sql:='select * from
(select t1.*,rownum rn from (select * from '|| tableName ||') t1 where rownum <='|| v_end ||')
where rn>'|| v_begin ||'';
open fy_cursor for v_sql;

--计算allRowCount和allPageCount
v_sql:='select count(*) from '|| tableName ||'';

--执行sql 并把值赋给allRowCount
execute immediate v_sql into allRowCount;

--根据allRowCount的值计算allPageCount总页数
if mod(allRowCount,everyPageSize)=0 then
allPageCount:=allRowCount/everyPageSize;
else
allPageCount:=allRowCount/everyPageSize+1;
end if;
--close fy_cursor;
end;