中等复杂度分页存储过程

==========我的版本==================================
create or replace package CutPage is
       type  ref_cur is ref cursor ;
       procedure CutPage(
             table_name in varchar2 , --表名
             query_partition in varchar2 , --查询条件
             order_partition in varchar2 , --排序条件
             pageSize in Integer ,  --每页显示记录数
             pageNo in Integer  ,   --第几页
             real_records out Integer ,   --返回的总记录数
             real_pageNo out integer ,    --真实的页数
             all_cur out ref_cur          --记录集
       ) ;
       procedure CutPage2(
             p_sql in varchar2 , --查询集合
             p_query_partition in varchar2 , --查询条件
             p_order_partition in varchar2 , --排序条件
             p_page_size in Integer ,  --每页显示记录数
             p_page_no in Integer  ,   --第几页
             p_all_records out Integer ,   --返回的总记录数
             p_all_pageNo out integer ,    --真实的页数
             r_cur out ref_cur          --记录集
       ) ;
end CutPage ;

create or replace package body CutPage is
       procedure CutPage(
             table_name in varchar2 , --表名
             query_partition in varchar2 , --查询条件
             order_partition in varchar2 , --排序条件
             pageSize in Integer ,  --每页显示记录数
             pageNo in Integer  ,   --第几页
             real_records out Integer ,   --总记录数
             real_pageNo out integer ,    --真实的页数
             all_cur out ref_cur          --记录集
       ) is
             rownum_begin Integer ; -- 起始ROWNUM
             rownum_end   Integer ; -- 结束ROWNUM   
             all_records   Integer ; -- 全部条数
             all_pageno    Integer ; -- 全部页数
             sql_str1      varchar2(2000) ;--SQL查询语句
             sql_str2      varchar2(2000) ;--SQL查询语句
            
             ErrorParaments Exception ;  --参数异常
       begin
             if table_name is null then
             raise ErrorParaments ;
             end if ;
            
             rownum_begin := pageSize * (pageNo - 1) ;
             rownum_end := pageSize * (pageNo - 1) + pageSize ;
            
             --统计总条数 动态执行SQL
             sql_str1 := 'select count(rownum) from ' || table_name;
             execute immediate sql_str1 into all_records ;
            
             --统计总页数 算法
             if all_records = 0 then
                all_pageno := 0 ;
             elsif all_records <= pageSize then
                all_pageno := 1 ;
             elsif all_records mod pageSize = 0 then
                all_pageno := all_records / pageSize ;
             else
                all_pageno := (all_records - (all_records mod pageSize ))/ pageSize + 1 ;
             end if ;
            
             --赋值打印分页信息
             dbms_output.put_line('全部 :' || all_records);
             dbms_output.put_line('全部页数:' || all_pageno);
             real_records := all_records ;
             real_pageNo := all_pageno ;
            
             --组装分页SQL 语句
             sql_str2 :=  'select info.* from '||
                          '(select '||table_name||'.* , rownum as r_num  from '|| table_name ||' where '|| query_partition ||
                          ' and rownum <= ' || rownum_end || ' order by '|| order_partition ||')info' ||
                          ' where info.r_num > '|| rownum_begin  ;
             dbms_output.put_line(sql_str2);
            
             open all_cur for sql_str2 ;
       exception
             when ErrorParaments then
                  dbms_output.put_line('参数输入格式不对 或者 格式异常');
       end CutPage ;
      
       procedure CutPage2(
             p_sql in varchar2 , --查询集合
             p_query_partition in varchar2 , --查询条件
             p_order_partition in varchar2 , --排序条件
             p_page_size in Integer ,  --每页显示记录数
             p_page_no in Integer  ,   --第几页
             p_all_records out Integer ,   --返回的总记录数
             p_all_pageNo out integer ,    --真实的页数
             r_cur out ref_cur          --记录集
       ) is
             v_sql varchar2(1000) ; --查询总记录数
             ErrorParaments Exception ;  --参数异常
            
             rownum_begin Integer ; -- 起始ROWNUM
             rownum_end   Integer ; -- 结束ROWNUM   
            
             str_query_partition varchar2(1000) ;
             str_order_partition varchar2(1000) ;
            
       begin
      
             rownum_begin := p_page_size * (p_page_no - 1) ;
             rownum_end := p_page_size * (p_page_no - 1) + p_page_size ;
            
             v_sql := 'select count(rownum) from '|| p_sql;
             execute immediate v_sql into p_all_records ;
             p_all_pageNo := ceil(p_all_records / p_page_size) ;
            
             dbms_output.put_line('全部 :' || p_all_records);
             dbms_output.put_line('全部页数:' || p_all_pageNo);
            
             if p_query_partition is null then
                str_query_partition := '1 = 1';
             else
                str_query_partition := p_query_partition ;
             end if ;
            
             if p_order_partition is null then
                str_order_partition := ' ';
             else
                str_order_partition := p_order_partition ;
             end if ;
            
             v_sql :=  'select * from '||
                       '(select t.* , rownum as r_num from '|| p_sql ||
                       't where '||str_query_partition ||' and ' ||
                       ' rownum < '||rownum_end ||str_order_partition||
                       ')info  where info.r_num >= '|| rownum_begin ;
            dbms_output.put_line(v_sql);
           
            open r_cur for v_sql ;

       exception
             when ErrorParaments then
                   dbms_output.put_line('参数输入格式不对 或者 格式异常');
       end CutPage2 ;
end Cutpage ;

==========老版本================================
create or replace procedure prc_query(
                      p_tablename in varchar2,--表明
                      p_strwhere in varchar2, --条件
                      p_ordercolumn in varchar2, --排序的列
                      p_orderstyle in varchar2, --排序方式
                      p_pageSize in out number ,--每页显示的记录条数
                      p_curPage in out number, --当前页
                      p_totalRecords out number ,--总记录数
                      p_totalpages out number, --总页数
                      v_cur out find.cur)--返回的结果集
is
  v_sql varchar2(1000) :=''; --sql语句
  v_startrecord number(4);--开始显示的记录条数
  v_endrecord number(4);--结束显示的记录条数
begin
   v_sql := 'select to_number(count(*)) from '||p_tablename || ' where 1=1 ';
   if p_strwhere is not null or p_strwhere <> '' then
     v_sql := v_sql || p_strwhere;
   end if;
   execute immediate v_sql into p_totalRecords;
   --验证页面记录大小
   if p_pageSize <0 then
       p_pageSize:=0;
   end if;
   --根据页面的大小计算总页数
  /* if mod(p_totalRecords,p_pageSize)=0 then
      p_totalpages:=p_totalRecords/p_pageSize;
   else
      p_totalpages:=p_totalRecords/p_pageSize+1;
   end if;*/
  
   p_totalRecords:= ceil(p_totalRecords/p_pageSize);
  
   --验证页号
   if p_curPage <1 then
     p_curPage :=1;
   end if;
   if p_curPage > p_totalpages then
      p_curPage:=p_totalpages;
   end if;

   --实现分页查询
   v_startrecord := (p_curPage-1)*p_pageSize+1;
   v_endrecord := p_curPage * p_pageSize;
   v_sql:='select * from (select A.*,rownum r from ' ||
             '(select * from '|| p_tablename;
   if p_strwhere is not null or p_strwhere <> '' then
     v_sql := v_sql || ' where 1=1 ' || p_strwhere;
   end if;
   if p_ordercolumn is not null or p_ordercolumn <> '' then
     v_sql := v_sql || ' order by '|| p_ordercolumn || ' ' || p_orderstyle;
   end if;
   v_sql := v_sql || ')A where rownum <= ' || v_endrecord || ') B where r>= '
                           || v_startrecord;
   dbms_output.put_line(v_sql);
   open v_cur for v_sql;
end;

------------浩----------------
/*分页存储过程：
1、参数说明：
   tablename：表名
   strwhere：查询条件
   pagesize：每页显示记录数
   pageno：  第几页
   ordercol：需要排序的列
   totalrecords：总记录数(输出)
   totalpage：总页数(输出)
   v_cur out 引用游标(输出参数)*/
create or replace package devidpage
is
type cur is ref cursor;
 procedure devidpages
(
   tablename varchar2,
   strwhere varchar2,
   pagesize number,
   pageno number,
   ordercol varchar2,
   totalrecords out number,
   totalpage out int,
   v_cur out cur
);
end devidpage;

create or replace package body devidpage
is
 procedure devidpages
(
   tablename varchar2,
   strwhere varchar2,
   pagesize number,
   pageno number,
   ordercol varchar2,
   totalrecords out number,
   totalpage out int,
   v_cur out cur
)
is
sqlstr varchar2(2000);
sqlstr1 varchar2(2000);
c_cur cur;
begin
if strwhere is null and ordercol is null then
sqlstr1:='select count(*) from '||tablename||'';
sqlstr:='select * from(select t.*,rownum ru from'||' '|| tablename ||' '||'t )where ru>('||pageno||'-1)*'||pagesize ||'and ru<='||pagesize||'*'||pageno||'';
elsif strwhere is not null and ordercol is null then
sqlstr1:='select count(*) from '||tablename||' where '||strwhere||'';
sqlstr:='select * from(select t.*,rownum ru from'||' '|| tablename ||' '||'t where '||strwhere||')where ru>('||pageno||'-1)*'||pagesize ||'and ru<='||pagesize||'*'||pageno||'';
elsif strwhere is null and ordercol is not null then
sqlstr1:='select count(*) from '||tablename||'';
sqlstr:='select * from(select t.*,rownum ru from'||' '|| tablename ||' '||'t )where ru>('||pageno||'-1)*'||pagesize ||'and ru<='||pagesize||'*'||pageno||' order by '||ordercol;
else
sqlstr1:='select count(*) from '||tablename||' where '||strwhere||'';
sqlstr:='select * from(select t.*,rownum ru from'||' '|| tablename ||' '||'t where '||strwhere||' order by '||ordercol||')where ru>('||pageno||'-1)*'||pagesize ||'and ru<='||pagesize||'*'||pageno||'';
end if;
open v_cur for sqlstr;
open c_cur for sqlstr1;
begin
loop
fetch c_cur into totalrecords;
exit when c_cur%notfound;
end loop;
end;
select ceil(totalrecords/pagesize) into totalpage from dual;
end devidpages;
end devidpage;