oracle高效分页存储过程代码

create or replace package p_page is
   -- author  : pharaohs
   -- created : 2006-4-30 14:14:14
   -- purpose : 分页过程
   type type_cur is ref cursor      --定义游标变量用于返回记录集
  
   procedure pagination(
   pindex in number                 --分页索引
   psql in varchar2                 --产生dataset的sql语句
   psize in number                  --页面大小
   pcount out number                --返回分页总数
   v_cur out type_cur               --返回当前页数据记录
   )

   procedure pagerecordscount(
   psqlcount in varchar2            --产生dataset的sql语句
   prcount   out number             --返回记录总数
   )

end p_page
/
create or replace package body p_page is

procedure pagination(
   pindex in number
   psql in varchar2
   psize in number 
   pcount out number
   v_cur out type_cur
  )
  as

   v_sql varchar2(1000)
   v_count number 
   v_plow number
   v_phei number
  begin
   ------------------------------------------------------------取分页总数
   v_sql := ' select count() from ('  || psql || ' )' 
    ute immediate v_sql into v_count
   pcount := ceil(v_count/psize)
   ------------------------------------------------------------显示任意页内容
   v_phei := pindex  psize + psize
   v_plow := v_phei - psize + 1
   --psql := ' select rownum rn t. from zzda t'               --要求必须包含rownum字段
   v_sql := ' select  from ('  || psql || ' ) where rn between '  || v_plow || '  and '  || v_phei 

   open v_cur for v_sql
 
  end pagination
 
  --
 
  procedure pagerecordscount(
   psqlcount in varchar2
   prcount   out number
   )
   as
 
    v_sql varchar2(1000)
    v_prcount number
  
   begin
 
    v_sql := ' select count() from ('  || psqlcount || ' )' 
     ute immediate v_sql into v_prcount
    prcount := v_prcount                   --返回记录总数                                                    
  
   end pagerecordscount
 
  --
end p_page
/