分页存储过程

 

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

create or replace procedure fy
(tablename in varchar2,
pagesize in number,
pagenow in number,
totlerows out number,
pagecount out number,
p_cursor out testpackage.test_cursor)is
v_sql varchar2(1000);
v_begin number:=(pagenow-1)*pagesize+1;
v_end number:=pagenow*pagesize;
begin
v_sql:='select t1.* from (select t2.*,rownum r from '|| tablename||' t2 where rownum<'|| v_end ||') t1 where t1.r>'|| v_begin;
--把游标和上面sql关联起来
open p_cursor for v_sql;
--计算totlerows
v_sql:='select count(*) from '|| tablename ;
execute immediate v_sql into totlerows;
--计算pagecount
if mod(totlerows,pagesize)=0 then
pagecount:=totlerows/pagesize;
else
pagecount:=totlerows/pagesize+1;
end if;
end;