Oracle 分頁方法研究

• 1、Oracle 中的三大分頁方法
  • 1.1、透過分析函式分頁
  • 1.2、透過 ROWNUM 分頁
  • 1.3、透過 ROWID 分頁
• 2、Oracle 分頁解決方案淺析
  • 2.1、純後端程式碼完成分頁
  • 2.2、透過儲存過程來分頁
  • 2.3、兩個通用的分頁儲存過程
• 3、總結

1、Oracle 中的三大分頁方法

本人最近總結了一下 Oracle 中的分頁寫法，從純粹的 SQL 寫法上來看，所謂分頁就是巢狀子查詢，無非就是不同的分頁方法巢狀的子查詢層數不同而已。Oracle 中一共有三種分頁寫法，分別是：巢狀一層子查詢的分析函式分頁、巢狀兩層子查詢的 ROWNUM 分頁和巢狀三層子查詢的 ROWID 分頁。

1.1、透過分析函式分頁

按員工年齡排序，每頁顯示 3 個員工，取第 1 頁的資料。只巢狀一層子查詢，寫法簡潔，容易理解，但一般沒人用這種方法。只需要在子查詢中的分析函式內部排序即可實現排序功能。

SELECT t2.staff_name,t2.birthday FROM( SELECT t1.staff_name,t1.birthday,ROW_NUMBER() OVER(ORDER BY t1.birthday) rn FROM demo.t_staff t1
) t2 WHERE t2.rn >= ((1-1)*3+1) AND t2.rn <= (1*3);

1.2、透過 ROWNUM 分頁

按員工年齡排序，每頁顯示 3 個員工，取第 1 頁的資料。巢狀兩層子查詢，寫法比較靈活，一般都是用這種方法。只需要在子查詢內部排序即可實現排序功能。

SELECT t3.staff_name,t3.birthday FROM( SELECT t2.*,ROWNUM rn FROM( SELECT t1.staff_name,t1.birthday FROM demo.t_staff t1 ORDER BY t1.birthday
  ) t2 WHERE ROWNUM <= (1*3)
) t3 WHERE t3.rn >= ((1-1)*3+1);

透過 ROWNUM 分頁的一種變通寫法（相對來說更好理解）：

SELECT t3.staff_name,t3.birthday FROM( SELECT t2.*,ROWNUM rn FROM( SELECT t1.staff_name,t1.birthday FROM demo.t_staff t1 ORDER BY t1.birthday
  ) t2
) t3 WHERE t3.rn >= ((1-1)*3+1) AND t3.rn <= (1*3);

1.3、透過 ROWID 分頁

按員工年齡排序，每頁顯示 3 個員工，取第 1 頁的資料。寫法複雜，不太靈活，不易理解，很少有人用這種方法。必須在最內層子查詢和最外層查詢中都排序才可實現排序功能。

SELECT t4.staff_name,t4.birthday FROM demo.t_staff t4 WHERE t4.ROWID IN( SELECT t3.rid FROM( SELECT t2.rid,ROWNUM rn FROM( SELECT t1.ROWID rid FROM demo.t_staff t1 ORDER BY t1.birthday
    ) t2 WHERE ROWNUM <= (1*3)
  ) t3 WHERE t3.rn >= ((1-1)*3+1)
) ORDER BY t4.birthday;

2、Oracle 分頁解決方案淺析

Oracle 中的三大分頁方法應用最廣泛的還是第二種，也就是基於 ROWNUM 的分頁方法。由於實現分頁的語法是固定的，所以一般專案中都是會提供一個公用的分頁模版方法，然後其它需要分頁的業務方法再呼叫這個方法來完成分頁功能的。

分頁的實現過程就是拼接 SQL 語句的過程，但選擇在那個地方來完成拼接也是有講究的。一般來說在服務端拼接是一個比較好的選擇，這種方案主要好處就是靈活、簡單、易維護。另一種比較常見的做法是透過儲存過程來分頁，然後在服務端呼叫儲存過程，這種方案理論上分頁效率比較高，但實現過程相對複雜，也沒有純服務端程式碼那麼好維護。

2.1、純後端程式碼完成分頁

純後端程式碼完成分頁在定義、呼叫、效能、理解、維護等方面有不少小的技巧值得推敲。前幾天我結合自己這些年來的分頁經驗和一個在公司幹了十多年的技術專家交流了這個問題，最終我們一致認為還是傳遞整個內層子查詢的方式最好（主要是可以規避掉一大堆小的坑）。拼接格式如下：

SELECT t3.* FROM(
  SELECT t2.*,ROWNUM rn FROM( :subquery ) t2 WHERE ROWNUM <= (:pageIndex*:pageSize)
) t3 WHERE t3.rn >= ((:pageIndex-1)*:pageSize+1)

我們以前都有嘗試過將子查詢分拆成多個部分，然後分別傳遞的方式，不過一旦專案深入之後問題總比想象的要多得多。譬如引數過多導致呼叫難度增加，為了實現分頁不得不將寫好的整條語句拆成幾個部分多餘浪費時間，出問題時除錯的複雜度也增加了，多表分頁也相對難以處理，經驗不足的程式設計師常常沒耐心看懂現有程式碼進而又捏造了一個所謂的改進版（事實上這種情況還很多）……

不過即便是整個子查詢傳進來，也仍然會有不同的處理方式。譬如我上文提到的那個專家說他們就曾嘗試過把傳遞進來子查詢切分成多個部分再重新組合，但後來發現複雜的子查詢極難寫對，徒增了團隊裡新人的挫敗感……

外層查詢中的那個星號是比較關鍵的一點，儘管我們都知道查詢中出現星號往往是不好的，但分頁時依然拘泥這一點的話，必然會到導致複雜的拼接。複雜的拼接往往不好寫，呼叫時也容易出錯，時不時還得回頭去看內部的實現再推匯出該如何呼叫，這個過程顯然是比較浪費時間的。

2.2、透過儲存過程來分頁

我本人大部分時候還是透過儲存過程來實現分頁的，不過對很多人來說寫儲存過程甚至呼叫儲存過程都是比較難的，我覺得主要原因還是因為相關知識點不熟、寫的少。下面列出了寫分頁儲存過程和呼叫儲存過程的相關參考連線：

• 《.Net程式設計師學用Oracle系列(7)：檢視、函式、儲存過程、包》：儲存過程
• 《.Net程式設計師學用Oracle系列(26)：PLSQL 之型別、變數和結構》：變數
• 《.Net程式設計師學用Oracle系列(27)：PLSQL 之遊標、異常和事務》：遊標
• 《.Net程式設計師學用Oracle系列(16)：訪問資料庫(ODP.NET)》：甲骨文提供的驅動

下面是一個呼叫 Oracle 分頁儲存過程的 C# 方法：

/// <summary> /// 呼叫儲存過程，執行分頁 /// </summary> /// <param name="tableName">表名</param> /// <param name="queryFields">查詢（欄位）列表</param> /// <param name="queryWhere">查詢條件</param> /// <param name="orderBy">排序子句</param> /// <param name="pageIndex">頁索引（頁碼）</param> /// <param name="pageSize">頁大小（每頁資料條數）</param> /// <param name="pageCount">總頁數</param> /// <param name="rowCount">總行數</param> /// <param name="resultSet">結果集</param> public void ExecutePaging( string tableName, string queryFields, string queryWhere, string orderBy, int pageIndex, int pageSize, ref int pageCount, ref int rowCount, ref DataTable resultSet) {
    OracleParameter[] ps = { new OracleParameter(":tableName", OracleDbType.Varchar2, 1000), new OracleParameter(":queryFields", OracleDbType.Varchar2, 1000), new OracleParameter(":queryWhere", OracleDbType.Varchar2, 2000), new OracleParameter(":orderBy", OracleDbType.Varchar2, 200), new OracleParameter(":pageIndex", OracleDbType.Int32), new OracleParameter(":pageSize", OracleDbType.Int32), new OracleParameter(":pageCount", OracleDbType.Int32), new OracleParameter(":rowCount", OracleDbType.Int32), new OracleParameter(":resultSet", OracleDbType.RefCursor)
    };

    ps[0].Value = tableName;
    ps[1].Value = queryFields;
    ps[2].Value = queryWhere;
    ps[3].Value = orderBy;
    ps[4].Value = pageIndex;
    ps[5].Value = pageSize;
    ps[6].Direction = ParameterDirection.Output;
    ps[7].Direction = ParameterDirection.Output;
    ps[8].Direction = ParameterDirection.Output;

    resultSet = OracleHelper.ProcQuery("sp_dynamic_paging", ps); // 呼叫儲存過程 pageCount = Verifier.VerifyInt(ps[6].Value);
    rowCount = Verifier.VerifyInt(ps[7].Value);
}

2.3、兩個通用的分頁儲存過程

下面這個儲存過程是從我曾負責過的一個專案中抽取出來的，也是我第一次嘗試寫儲存過程分頁，100%原創，中間改版過幾次，為方便閱讀註釋內容已被我去掉，現在的這個版本中的i_queryFields引數是不接受星號的：

CREATE OR REPLACE PROCEDURE sp_paging(
  i_tableName VARCHAR2, -- 表名
  i_queryFields VARCHAR2, -- 查詢（欄位）列表
  i_queryWhere VARCHAR2, -- 查詢條件
  i_orderBy VARCHAR2, -- 排序子句
  i_pageIndex NUMBER, -- 當前頁索引
  i_pageSize NUMBER, -- 頁大小
  o_rowCount OUT NUMBER, -- 總行數
  o_pageCount OUT NUMBER, -- 總頁數
  o_resultSet OUT SYS_REFCURSOR -- 結果集
) IS
  v_count_sql VARCHAR2(2000);
  v_select_sql VARCHAR2(4000); BEGIN -- 拼接查詢總行數的語句
    v_count_sql := 'SELECT COUNT(1) FROM '||i_tableName;

    -- 拼接查詢條件 IF i_queryWhere IS NOT NULL THEN
      v_count_sql := v_count_sql||' WHERE 1=1 '||i_queryWhere; END IF;

    -- 計算總行數
    EXECUTE IMMEDIATE v_count_sql INTO o_rowCount;
    --DBMS_OUTPUT.PUT_LINE(v_count_sql||';');

    -- 計算總頁數（CEIL 向上取整）
    o_pageCount := CEIL(o_rowCount / i_pageSize);

    -- 如果有記錄，且當前頁索引合法，則繼續查詢 IF o_rowCount >= 1 AND i_pageIndex >= 1 AND i_pageIndex <= o_pageCount THEN

      -- 當記錄總數小於或等於頁大小時，查詢所有記錄 IF o_rowCount <= i_pageSize THEN
        v_select_sql := 'SELECT '||i_queryFields||' FROM('||i_tableName||')'; IF i_queryWhere IS NOT NULL THEN
          v_select_sql := v_select_sql||' WHERE 1=1 '||i_queryWhere; END IF; IF i_orderBy IS NOT NULL THEN
          v_select_sql := v_select_sql||' order by '||i_orderBy; END IF;

      -- 查詢第一頁
      ELSIF i_pageIndex = 1 THEN
        v_select_sql := 'SELECT '||i_queryFields||' FROM('||i_tableName||')'; IF i_queryWhere IS NOT NULL THEN
          v_select_sql := v_select_sql||' WHERE 1=1 '||i_queryWhere; END IF; IF i_orderBy IS NOT NULL THEN
          v_select_sql := v_select_sql||' order by '||i_orderBy; END IF;

        v_select_sql := 'SELECT '||i_queryFields||' FROM('||v_select_sql||') WHERE ROWNUM<='||i_pageSize;

      -- 查詢指定頁 ELSE v_select_sql := 'SELECT '||i_queryFields||' FROM('||i_tableName||')'; IF i_queryWhere IS NOT NULL THEN
          v_select_sql := v_select_sql||' WHERE 1=1 '||i_queryWhere; END IF; IF i_orderBy IS NOT NULL THEN
          v_select_sql := v_select_sql||' order by '||i_orderBy; END IF;

        v_select_sql := 'SELECT '||i_queryFields||' FROM(SELECT ROWNUM rn,'||i_queryFields||' FROM('||v_select_sql
          ||')) WHERE rn>'||((i_pageIndex-1)*i_pageSize)||' AND rn<='||(i_pageIndex*i_pageSize); END IF;

      --DBMS_OUTPUT.PUT_LINE(v_select_sql||';');
      OPEN o_resultSet FOR v_select_sql; ELSE OPEN o_resultSet FOR 'SELECT * FROM '||i_tableName||' WHERE 1!=1'; END IF; END;

下面這個儲存過程摘自《劍破冰山——Oracle開發藝術》一書，有刪改：

CREATE OR REPLACE PROCEDURE sp_dynamic_paging(
  i_tableName VARCHAR2, -- 表名
  i_queryFields VARCHAR2, -- 查詢列表
  i_queryWhere VARCHAR2, -- 查詢條件
  i_orderBy VARCHAR2, -- 排序
  i_pageSize NUMBER, -- 頁大小
  i_pageIndex NUMBER, -- 頁索引
  o_rowCount OUT NUMBER, -- 返回總條數
  o_pageCount OUT NUMBER, -- 返回總頁數
  o_resultSet OUT SYS_REFCURSOR -- 返回分頁結果集
) IS v_startRows INT; -- 開始行
  v_endRows INT; -- 結束行 
  v_pageSize INT;
  v_pageIndex INT;
  v_queryFields VARCHAR2(2000);
  v_queryWhere VARCHAR2(2000);
  v_orderBy VARCHAR2(200);
  v_count_sql VARCHAR2(1000); -- 接收統計資料條數的 SQL 語句
  v_select_sql VARCHAR2(4000); -- 接收查詢分頁資料的 SQL 語句
BEGIN
  -- 如果沒有表名，則直接返回異常訊息
  -- 如果沒有欄位，則表示查詢全部欄位 IF i_queryFields IS NOT NULL THEN v_queryFields:=i_queryFields; ELSE v_queryFields:=' * '; END IF;

  -- 可以沒有查詢條件 IF i_queryWhere IS NOT NULL THEN v_queryWhere := ' WHERE 1=1 AND'||i_queryWhere||' '; ELSE v_queryWhere := ' WHERE 1=1 '; END IF;

  -- 可以沒有排序條件 IF i_orderBy IS NULL THEN v_orderBy:=' '; ELSE v_orderBy:='ORDER BY '||i_orderBy; END IF;

  -- 如果未指定查詢頁，則預設為首頁 IF i_pageIndex IS NULL OR i_pageIndex<1 THEN v_pageIndex:=1; ELSE v_pageIndex:=i_pageIndex; END IF;

  -- 如果未指定每頁記錄數，則預設為 10 條 IF i_pageSize IS NULL THEN v_pageSize:=10; ELSE v_pageSize:=i_pageSize; END IF;

  -- 構造查詢總條數的語句
  v_count_sql:='SELECT COUNT(1) FROM '||i_tableName||v_queryWhere; --DBMS_OUTPUT.PUT_LINE(v_count_sql||';'); -- 構造查詢資料的語句
  v_select_sql:='(SELECT '||v_queryFields||' FROM '||i_tableName||v_queryWhere||v_orderBy||') t2'; -- 查詢總條數
  EXECUTE IMMEDIATE v_count_sql INTO o_rowCount;

  -- 得到總頁數 IF MOD(o_rowCount,i_pageSize)=0 THEN o_pageCount:=o_rowCount/i_pageSize; ELSE o_pageCount:=FLOOR(o_rowCount/i_pageSize)+1; END IF;

  -- 如果當前頁大於最大頁數，則取最大頁數 IF i_pageIndex>o_pageCount THEN v_pageIndex:=o_pageCount; END IF;

  -- 設定開始結束的記錄數
  v_startRows:=(v_pageIndex-1)*v_pageSize+1;
  v_endRows:=v_pageIndex*v_pageSize;

  -- 進行完成的動態 SQL 語句拼接
  v_select_sql:='SELECT t3.* FROM'||'(SELECT t2.*,ROWNUM rn FROM'||v_select_sql ||' WHERE ROWNUM<='||v_endRows||') t3 WHERE t3.rn>='||v_startRows; --DBMS_OUTPUT.PUT_LINE(v_select_sql||';'); OPEN o_resultSet FOR v_select_sql; END;

下面這段 PL/SQL 程式碼用於測試上面兩個儲存過程：

DECLARE v_tableName VARCHAR2(1000);
  v_queryFields VARCHAR2(1000);
  v_queryWhere VARCHAR2(1000);
  v_orderBy VARCHAR2(200);
  v_pageSize INT := 3;
  v_pageIndex INT;
  v_rowCount INT := 0;
  v_pageCount INT := 0;
  v_resultSet SYS_REFCURSOR;
BEGIN
  v_tableName:='t_staff';
  v_queryFields:='staff_name,birthday';
  v_orderBy:='birthday';
  v_pageIndex:=1;

  sp_dynamic_paging(
    i_tableName => v_tableName,
    i_queryFields => v_queryFields,
    i_queryWhere => v_queryWhere,
    i_orderBy => v_orderBy,
    i_pageSize => v_pageSize,
    i_pageIndex => v_pageIndex,
    o_rowCount => v_rowCount,
    o_pageCount => v_pageCount,
    o_resultSet => v_resultSet
  );
END;

3、總結

本文主要講述了 Oracle 中的三種分頁方法和常見的兩種分頁解決方案，並給出了兩個通用的分頁儲存過程原始碼。主要是對我個人所掌握的 Oracle 分頁方法和技術做了個全面的回顧。

本文連結：http://www.cnblogs.com/hanzongze/p/oracle-paging-1.html
版權宣告：本文為部落格園博主 韓宗澤 原創，作者保留署名權！歡迎透過轉載、演繹或其它傳播方式來使用本文，但必須在明顯位置給出作者署名和本文連結！個人部落格，能力有限，若有不當之處，敬請批評指正，謝謝！