存储过程分页

创建包：

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

分页存储过程：

create or replace procedure fenye(tableName   in varchar2,
                                  Pagesizeb   in number,
                                  pageNow     in number,
                                  myrows      out number,
                                  myPageCount out number,
                                  p_cursor    out testpackage.test_cursor) is
  -- 声明变量  定义部分
  v_sql   varchar2(1000);
  v_begin number := (pageNow - 1) * Pagesizeb + 1;
  v_end   number := pageNow * Pagesizeb;

begin
  v_sql := 'select b.* from  (select a.* ,rownum nm from (select * from ' ||
           tableName || ') a where rownum <' || v_end ||
           ') b where b.nm > ' || v_begin || '';
  open p_cursor for v_sql;

  v_sql := 'select count(*) from ' || tableName || '';
  execute immediate v_sql
    into myrows;

  if mod(myrows, Pagesizeb) = 0 then
    myPageCount := myrows / Pagesizeb;
  else
    myPageCount := myrows / Pagesizeb + 1;
  end if;

end;