Oracle分页存储过程

CREATE OR REPLACE Procedure Soa_Getpager(p_Pagesize       Int, --每页记录数
                                         p_Pageno         Int, --当前页码,从 1 开始
                                         p_Sqlcols        Varchar2, --选择列
                                         p_Sqlfrom        Varchar2, --表名OR连接查询
                                         p_Sqlwhere       Varchar2, --查询子句
                                         p_Sqlorderby     Varchar2, --排序
                                         p_Outrecordcount Out Int, --返回总记录数
                                         p_Outpagecount   Out Int, --返回总页数
                                         Result           Out Sys_Refcursor) As
  v_Sql       Varchar2(3000);
  p_Sqlselect Varchar2(3000);
  v_Count     Int;
  v_Heirownum Int;
  v_Lowrownum Int;
Begin

  ----拼接SQL查询语句
  p_Sqlselect := 'select ' || p_Sqlcols || ' from ' || p_Sqlfrom || ' ' ||
                 p_Sqlwhere || ' ' || p_Sqlorderby;
  Dbms_Output.Put_Line(p_Sqlselect);
  ----取记录总数
  v_Sql := 'select count(1) from (' || p_Sqlselect || ')';
  Execute Immediate v_Sql
    Into v_Count;
  p_Outrecordcount := v_Count;
  ----取总页数
  p_Outpagecount := p_Outrecordcount / p_Pagesize;
  If p_Outpagecount = 0 Then
    p_Outpagecount := 1;
  End If;

  ----执行分页查询
  v_Heirownum := p_Pageno * p_Pagesize;
  v_Lowrownum := v_Heirownum - p_Pagesize + 1;

  v_Sql := 'SELECT *
            FROM (
                  SELECT A.*, rownum rn
                  FROM  ( ' || p_Sqlselect || ') A
                  WHERE rownum <= ' || To_Char(v_Heirownum) || '
                 ) B
            WHERE rn >= ' || To_Char(v_Lowrownum);
  --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn

  Dbms_Output.Put_Line(v_Sql);

  Open Result For v_Sql;

End Soa_Getpager;