Oracle大資料量分頁通用預存程序

type refCursorType is REF CURSOR;  --遊標類型定義，用於返回資料集

/*****************************************************************
 *功能描述: 大資料量分頁通用預存程序
 *創 建 人: 夏春濤 xchuntao@163.com qq:23106676
 *建立時間: 2005-06-29
 *****************************************************************/
procedure sp_Page(p_PageSize int,          --每頁記錄數
                  p_PageNo int,            --當前頁碼,從 1 開始
                  p_SqlSelect varchar2,    --查詢語句,含排序部分
                  p_OutRecordCount out int,--返回總記錄數
                  p_OutCursor out refCursorType)
as
    v_sql varchar2(3000);
    v_count int;
    v_heiRownum int;
    v_lowRownum int;
begin
  ----取記錄總數
  v_sql := 'select count(*) from (' || p_SqlSelect || ')';
  execute immediate v_sql 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;

/*****************************************************************
 *功能描述: 大資料量分頁通用預存程序(重載1，推薦使用)
 *創 建 人: 夏春濤
 *建立時間: 2005-07-01
 *****************************************************************/
procedure sp_Page(p_PageSize int,          --每頁記錄數
                  p_PageNo int,            --當前頁碼,從 1 開始
                  p_SqlSelect varchar2,    --查詢語句,含排序部分
                  p_SqlCount varchar2,     --擷取記錄總數的查詢語句
                  p_OutRecordCount out int,--返回總記錄數
                  p_OutCursor out refCursorType)
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;

/*****************************************************************
 *功能描述: 大資料量分頁通用預存程序(重載2)
 *創 建 人: 夏春濤
 *建立時間: 2005-07-01
 *****************************************************************/
procedure sp_Page(p_PageSize int,        --每頁記錄數
                  p_PageNo int,          --當前頁碼,從 1 開始
                  p_SqlSelect varchar2,  --查詢語句,含排序部分
                  p_OutCursor out refCursorType)
as
    v_sql varchar2(3000);
    --v_count int;
    v_heiRownum int;
    v_lowRownum int;
begin
/*
  ----取記錄總數
  v_sql := 'select count(*) from (' || p_SqlSelect || ')';
  execute immediate v_sql 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;