Oracle分頁預存程序)

create or replace package PKG_Tools
   is
   type ResultData is ref cursor;
   procedure sp_Page(p_PageSize int,          --每頁記錄數
                    p_PageNo int,            --當前頁碼,從 1 開始
                      p_SqlSelect varchar2,    --查詢語句,含排序部分
                      p_SqlCount varchar2,     --擷取記錄總數的查詢語句
                     p_OutRecordCount out int,--返回總記錄數
                      p_OutCursor out ResultData);
  
  
  end PKG_Tools;
   /

create or replace package body PKG_Tools
   is
   procedure sp_Page(p_PageSize int,          --每頁記錄數
                     p_PageNo int,            --當前頁碼,從 1 開始
                     p_SqlSelect varchar2,    --查詢語句,含排序部分
                     p_SqlCount varchar2,     --擷取記錄總數的查詢語句
                     p_OutRecordCount out int,--返回總記錄數
                     p_OutCursor out ResultData)
     as
         v_sql varchar2(3000);
         v_count int;
         v_heiRownum int;
         v_lowRownum int;
    begin
     ----取記錄總數
      execute immediate p_SqlCount into v_count;
      p_OutRecordCount := v_count;
     ----執行分頁查詢
      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
  
   OPEN p_OutCursor FOR  v_sql;
 
  end sp_Page;
 
  end PKG_Tools;
  /