oracle 分页存储过程

包头：

create or replace package JT_P_page is
  -- Author : ujnjt
  -- Created : 2007-8-5 16:43:50
  -- Purpose : 分页功能

  -- Public type declarations
  type type_cur is ref cursor; --定义游标变量用于返回记录集

  procedure Pagination(Pindex in number, --要显示的页数索引，从0开始
                       Psql in varchar2, --产生分页数据的查询语句
                       Psize in number, --每页显示记录数
                       Pcount out number, --返回的分页数
                       Prowcount out number, --返回的记录数
                       v_cur out type_cur --返回分页数据的游标
                       );
end JT_P_page;


包体：

create or replace package body JT_P_page is

  procedure Pagination(Pindex in number, --要显示的页数索引，从0开始
                       Psql in varchar2, --产生分页数据的查询语句
                       Psize in number, --每页显示记录数
                       Pcount out number, --返回的分页数
                       Prowcount out number, --返回的记录数
                       v_cur out type_cur --返回分页数据的游标
                       ) AS
    v_sql VARCHAR2(1000);
    v_Pbegin number;
    v_Pend number;

  begin
    v_sql := 'select count(*) from (' || Psql || ')';
    execute immediate v_sql into Prowcount; --计算记录总数
    Pcount := ceil(Prowcount / Psize); --计算分页总数

    --显示任意页内容
    v_Pend := Pindex * Psize + Psize;
    v_Pbegin := v_Pend - Psize + 1;
  
    --Psql := 'select rownum as rn , t.* from pay_en_voucher t'; --要求必须包含rownum字段

    v_sql := 'select * from (' || Psql || ') where rn between ' || v_Pbegin || ' and ' || v_Pend;
             
    open v_cur for v_sql;
  end Pagination;
  
end JT_P_page;


 

调用方法：

 

-- Created on 2007-8-5 by ujnjt

declare
  psqlContent varchar2(1000);
  pageIndex number;
  pageCount number; --返回的分页数
  pageSize number; --每一页的记录数
  rowscount number; -- 返回的记录总数
  return_cur JT_P_PAGE.type_cur;
  curr_id pay_en_voucher.id%type;
  curr_rn number;
begin
  psqlContent := 'select rownum as rn , t.id from pay_en_voucher t';
  pageIndex := 0;
  pageSize := 20;
  JT_P_PAGE.Pagination(pageIndex,
                       psqlContent,
                       pageSize,
                       pageCount,
                       rowscount,
                       return_cur);
  if return_cur%isopen then
    close return_cur;
  end if;
  
  for Counter in 0 .. pageCount - 1 loop
  pageIndex := Counter;
  JT_P_PAGE.Pagination(pageIndex, psqlContent, pageSize, pageCount, rowscount, return_cur);
  if return_cur%isopen then
    dbms_output.put_line('第' || (pageIndex + 1) || '页数据开始');
    loop
      fetch return_cur
        into curr_rn, curr_id;
      if (return_cur%notfound) then
        exit;
      end if;
      dbms_output.put_line(curr_rn);
      dbms_output.put_line(curr_id);
      dbms_output.put_line('--------');
    end loop;
    dbms_output.put_line('第' || (pageIndex + 1) || '页数据结束');
    close return_cur;
    dbms_output.put_line('=========================================');
  end if;
end loop;
end;