–Oracle分页定义好的游标 create or replace package chj1_package astype chji1_cursor is ref cursor;end chj1_package; –一
–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;
，
转载请注明原文链接：Oracle 分页步骤
