--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 --三步
select * from
(select t1.*,rownum rn from (select * from chengji) t1 where rownum 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 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;
，