存储过程分页

来源：互联网发布：游戏录制视频软件编辑：程序博客网时间：2024/04/29 23:04

创建包：

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;