分页存储过程的编写代码

首先要通过包创建一个游标，代码如下:

create or replace package mypackage as

type mycursor_cursor is ref cursor;

end mypackage;

其次才是编写分页的存储过程，代码如下:

create or replace procedure myproc(

tableName in varchar2,

pageSize in number,

pageNow in number,

myRows out number,

myPageCount out number,

myc_cursor out mypackage.mycursor_cursor) is

v_sql_str varchar2(1000);

v_begin_page number :=(pageNow-1)*pageSize+1;

v_end_page number := pageNow*pageSize;

begin

v_sql_str :='select * from (select t1.*,rownum rn from (select * from '|| tableName ||') t1 where rownum<='||v_end_page||') where rn>='||v_begin_page;

open myc_cursor for v_sql_str;

v_sql_str:= 'select count(*) from '|| tableName;

execute immediate v_sql_str into myRows;

if mod(myRows,pageSize)=0 then 

myPageCount:= myRows/pageSize;

else

myPageCount:= myRows/pageSize+1;

end if;

end myproc;