ORACLE高效分頁預存程序代碼

create or replace procedure P_Pagination
(
  Pindex in number,       --頁號  從1開始
  Psql in varchar2,       --查詢語句
  Psize in number,        --每頁分多少行
  Pcount out number,      --共有多少頁
  v_cur out Sys_Refcursor --返回資料集
 )
 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 ;
  v_Plow := v_Phei - Psize + 1;
  v_sql := 'select * from (select rownum rn,t.* from (' || Psql || ')t) where rn between ' || v_Plow || ' and ' || v_Phei ;

  open v_cur for v_sql;

 End P_Pagination;