分页存储过程-自己处理的

CREATE OR REPLACE Procedure  Getpager(

                                         p_pagesize       Int, --每页记录数

                                         p_pageno         Int, --当前页码,默认从 1 开始

                                         p_sqlcolumns        Varchar2,  --选择列

                                         p_sqlfrom        Varchar2, --表名OR连接查询

                                         p_sqlwhere       Varchar2, --查询子句

                                         p_sqlorderby     Varchar2, --排序子句

                                         p_outrecordcounts Out Int,  --返回总记录数

                                         p_outpagecounts   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_sqlcolumns || ' from ' || p_sqlfrom || ' ' ||  p_Sqlwhere || ' ' ;

  dbms_output.put_line(p_sqlselect);

  ----取记录总数

  v_sql := 'select count(1) from '  || p_sqlfrom || ' ' || p_sqlwhere || ' ';

  Execute Immediate v_sql

    Into v_Count;

  p_outrecordcounts := v_count;

  ----取总页数

  p_outpagecounts := p_outrecordcounts / p_pagesize;

  If p_outpagecounts = 0 Then

    p_outpagecounts := 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 *

            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_Outpagecounts:=-1;

     

End  Getpager;

 

测试过程如下：

 

 

begin

  -- Call the procedure

  getpager(p_pagesize => :p_pagesize,

           p_pageno => :p_pageno,

           p_sqlcolumns => :p_sqlcolumns,

           p_sqlfrom => :p_sqlfrom,

           p_sqlwhere => :p_sqlwhere,

           p_sqlorderby => :p_sqlorderby,

           p_outrecordcounts => :p_outrecordcounts,

           p_outpagecounts => :p_outpagecounts,

           p_counts => :p_counts);

end;

 

 

赋值如下：

 

 

 

结果集如下：