Oracle 通用分页存储过程

1.创建游标类型

 

create or replace package PDTypes as
  TYPE ref_cursor IS REF CURSOR;
end;

2.创建分页存储过程

 

PROCEDURE sp_Pager(vp_sql         in varchar2, --查询sql语句
                   vp_ordercolumn in varchar2, --排序字段   
                   vp_orderstyle  in varchar2, --排序方式  
                   vp_currpage    in number,   --当前页
                   vp_pagesize    in number,   --每页显示的记录数
                   vp_totalrow    out number,  --总记录数
                   vp_totalpage   out number,  --总页数
                   vp_cur         out PDTypes.ref_cursor) is
  v_sql         varchar2(4000) := ''; --sql语句
  v_startrecord number(8); --开始记录数
  v_endrecord   number(8); --结束记录数
  v_pagesize    number;
  v_currpage    number;
begin
  v_currpage := vp_currpage;
  v_pagesize := vp_pagesize;

  --总记录数
  v_sql := 'select to_number(count(*)) from (' || vp_sql || ') m where 1=1';

  execute immediate v_sql
    into vp_totalrow;

  --验证页面大小   
  if vp_pagesize < 0 then
    v_pagesize := 0;
  end if;

  --根据页面大小计算总页数   
  if mod(vp_totalrow, v_pagesize) = 0 then
    vp_totalpage := vp_totalrow / v_pagesize;
  else
    vp_totalpage := Floor(vp_totalrow / v_pagesize) + 1;
  end if;

  --验证页号
  if vp_currpage < 1 then
    v_currpage := 1;
  end if;

  if vp_currpage > vp_totalpage then
    v_currpage := vp_totalpage;
  end if;

  --实现分页查询
  v_startrecord := (v_currpage - 1) * v_pagesize + 1;
  v_endrecord   := v_currpage * v_pagesize;
  v_sql         := 'select * from (select a.*,rownum r from ' ||
                   '(select * from (' || vp_sql || ') m ';
  if length(trim(vp_ordercolumn)) > 0 then
    v_sql := v_sql || ' order by ' || vp_ordercolumn || ' ' ||
             vp_orderstyle;
  end if;
  v_sql := v_sql || ') a) b where r>=' || v_startrecord || ' and r<=' ||
           v_endrecord;

  dbms_output.put_line(v_sql);
  open vp_cur for v_sql;
end;