分页存储过程01

CREATE OR REPLACE Procedure DEV.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, --返回总页数

                                         p_counts          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 || ' ' ;

  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;

  p_Sqlselect :=p_Sqlselect || ' order by '||p_Sqlorderby;

  ----执行分页查询

  v_Heirownum := p_Pageno * p_Pagesize;

  v_Lowrownum := v_Heirownum - p_Pagesize + 1;

  v_Sql := 'SELECT ' || p_Sqlcols||' 

            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 p_counts For v_Sql;

 exception when others then p_Outpagecount:=-1;

End Soa_Getpager;

测试过程如下：

表中共 2753行数据，每页可以存放 10行数据库 。所以输出 275行

点击cursor 栏可以查看输出结果