oracle存储过程分页

本文中的代码是我的同事陈为的作品：

 

设计原理是将hibernate的分页方法提炼出来，写成存储过程，并将查询结果以游标的形式返回。

 

第一步：

CREATE OR REPLACE PACKAGE pack AS
  TYPE cur IS REF CURSOR;
END pack;

  

存储过程：

create or replace procedure cutpage(
      -----------------------------------------
      --name: 公用方法
      --description: 分页查询
      --version: v1.0
      --created date: 2008-10-07
      --author:
      --last updated:
      --last updated by:
      -----------------------------------------
  Psql        in     varchar2,        --产生数据集SQL语句  如需排序，请在语句内写好
  Psize       in     number,          --每页大小
  CurrentPage in     number,          --当前页码
  ProwCount   in out number,          --返回值：行数
  PageCount   out    number,          --返回分页总数
  Rcursor     out    pack.cur
)
as
  v_sql          varchar2(4000);      --中间sql变量
  v_currentpage  number;              --当前页码
  v_Plow         number;              --结果集的下限
  v_Phei         number;              --结果集的上限
  v_Psize        number;

begin
  v_sql :=Psql;
  if(v_sql is null) then
      return;
  end if;
  -------------------------------------------变量赋值
  if CurrentPage is null then
     v_currentpage := 1;
  else
     v_currentpage := CurrentPage;
  end if;

  if Psize is null then
     v_Psize :=10;
  else
     v_Psize:=Psize;
  end if;
  
  -------------------------------------------取分页总数和总的记录数
  if ProwCount is null then
      v_sql := 'select count(*) from (' || Psql || ')';
      execute immediate v_sql into ProwCount;
      PageCount := ceil(ProwCount/v_Psize);
  else
      PageCount := ceil(ProwCount/v_Psize);
  end if;
  -------------------------------------------返回选择页码的数据集
  
  if v_currentpage = 1 then
      v_sql := 'select * from ('||Psql||') 
                where rownum <= '||v_Psize||'';
  else
      v_Phei := v_currentpage * v_Psize;
      v_Plow := v_Phei - v_Psize + 1;
      v_sql := 'select *  from ( select  row_.*, rownum rownum_ 
        from ('||Psql||') row_ 
        where rownum <= ' || v_Phei || ' ) where rownum_ > ' || v_Plow || '';
  end if;
  
  --dbms_output.put_line(v_sql);
  -------------------------------------------绑定游标
  open rCursor for v_sql;

  Exception
        when others then
            --异常处理
            rollback;
end cutpage;
 

增加了排序功能的存储过程：

 

create or replace procedure cutpage2(
      -----------------------------------------
      --name: 公用方法
      --description: 分页查询
      --version: v1.0
      --created date: 2008-10-07
      --author:
      --last updated:
      --last updated by:
      -----------------------------------------
  Psql        in     varchar2,        --产生数据集SQL语句
  Psize       in     number,          --每页大小
  CurrentPage in     number,          --当前页码
  Pfield      in     varchar2,        --排序字段
  Porder      in     varchar2,        --排序顺序
  ProwCount   in out number,          --返回值：行数
  PageCount   out    number,          --返回分页总数
  Rcursor     out    pack.cur
)
as
  v_sql          varchar2(4000);      --中间sql变量
  v_currentpage  number;              --当前页码
  v_porder       varchar2(50);        --排序顺序
  v_rowcount     number;              --行数
  v_Plow         number;              --结果集的下限
  v_Phei         number;              --结果集的上限
  v_Psize        number;
  v_order        varchar2(100):='';

begin
  v_sql :=Psql;
  if(v_sql is null) then
      return;
  end if;
  -------------------------------------------变量赋值
  if CurrentPage is null then
     v_currentpage := 1;
  else
     v_currentpage := CurrentPage;
  end if;

  v_porder := Porder;
  v_rowcount := ProwCount;

  if Psize is null then
     v_Psize :=15;
  else
     v_Psize:=Psize;
  end if;

  if v_porder is null then
     v_porder := 'asc';
  end if;
  
  if Pfield is not null then
      v_order := 'order by '||Pfield||' '||v_porder||'';
  end if;
  
  -------------------------------------------取分页总数和总的记录数
  if v_rowcount is null then
      v_sql := 'select count(*) from (' || Psql || ')';
      execute immediate v_sql into ProwCount;
      PageCount := ceil(ProwCount/v_Psize);
  else
      PageCount := ceil(v_rowcount/v_Psize);
  end if;
  -------------------------------------------返回选择页码的数据集
  
  if v_currentpage = 1 then
      v_sql := 'select * from ('||Psql||' '||v_order||') 
                where rownum <= '||v_Psize||'';
  else
      v_Phei := v_currentpage * v_Psize;
      v_Plow := v_Phei - v_Psize + 1;
      v_sql := 'select *  from ( select  row_.*, rownum rownum_ 
        from ('||Psql||' '||v_order||') row_ 
        where rownum <= ' || v_Phei || ' ) where rownum_ > ' || v_Plow || '';
  end if;
  
  --dbms_output.put_line(v_sql);
  -------------------------------------------绑定游标
  open rCursor for v_sql;

  Exception
        when others then
            --异常处理
            rollback;
end cutpage2;