-- 分页存储过程：

-- 编写一个分页存储过程，要求：

--输入：表名，每页显示记录数，当前页；

--输出：总记录数，总页数，结果集；

create or replace package mypackage as

type rescursor is ref cursor;

end mypackage;

create or replace procedure fenye

(ptable in varchar2,

pnum in number,

ppage in number,

anum out number,

apage out number,

aresult out mypackage.rescursor)

is

v_sql varchar2(111);

v_pagebegin number:=pnum*(ppage-1)+1;

v_pageend number:=pnum*ppage;

begin

v_sql:='select * from (select a1.*,rownum rn from (select * from' || ptable ||

') a1 where rownum<='|| v_pageend ||') where rn>'|| v_pagebegin;

open aresult for v_sql;

v_sql:='select count(*) from'|| ptable;

execute immediate v_sql into anum;

apage:=(anum+pnum-1)/pnum;

close aresult;

end;

ITPub博客

分页存储过程