转 ORACLE高效分页存储过程代码

 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 || ')';
  execute 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 || ')';
   execute immediate v_sql into v_prcount;
   Prcount := v_prcount;                  --返回记录总数                                                     
 
  end PageRecordsCount;
 
 --**************************************************************************************
end p_page;
/