1、Oracle 中的三大分頁方法

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

1.1、通過分析函數(shù)分頁

按員工年齡排序，每頁顯示 3 個(gè)員工，取第 1 頁的數(shù)據(jù)。只嵌套一層子查詢，寫法簡潔，容易理解，但一般沒人用這種方法。只需要在子查詢中的分析函數(shù)內(nèi)部排序即可實(shí)現(xiàn)排序功能。

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 個(gè)員工，取第 1 頁的數(shù)據(jù)。嵌套兩層子查詢，寫法比較靈活，一般都是用這種方法。只需要在子查詢內(nèi)部排序即可實(shí)現(xiàn)排序功能。

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 分頁的一種變通寫法（相對(duì)來說更好理解）：

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 個(gè)員工，取第 1 頁的數(shù)據(jù)。寫法復(fù)雜，不太靈活，不易理解，很少有人用這種方法。必須在最內(nèi)層子查詢和最外層查詢中都排序才可實(shí)現(xiàn)排序功能。

SELECT t4.staff_name,t4.birthdayFROM demo.t_staff t4WHERE 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 中的三大分頁方法應(yīng)用最廣泛的還是第二種，也就是基于 ROWNUM 的分頁方法。由于實(shí)現(xiàn)分頁的語法是固定的，所以一般項(xiàng)目中都是會(huì)提供一個(gè)公用的分頁模版方法，然后其它需要分頁的業(yè)務(wù)方法再調(diào)用這個(gè)方法來完成分頁功能的。

分頁的實(shí)現(xiàn)過程就是拼接 SQL 語句的過程，但選擇在那個(gè)地方來完成拼接也是有講究的。一般來說在服務(wù)端拼接是一個(gè)比較好的選擇，這種方案主要好處就是靈活、簡單、易維護(hù)。另一種比較常見的做法是通過存儲(chǔ)過程來分頁，然后在服務(wù)端調(diào)用存儲(chǔ)過程，這種方案理論上分頁效率比較高，但實(shí)現(xiàn)過程相對(duì)復(fù)雜，也沒有純服務(wù)端代碼那么好維護(hù)。

2.1、純后端代碼完成分頁

純后端代碼完成分頁在定義、調(diào)用、性能、理解、維護(hù)等方面有不少小的技巧值得推敲。前幾天我結(jié)合自己這些年來的分頁經(jīng)驗(yàn)和一個(gè)在公司干了十多年的技術(shù)專家交流了這個(gè)問題，最終我們一致認(rèn)為還是傳遞整個(gè)內(nèi)層子查詢的方式最好（主要是可以規(guī)避掉一大堆小的坑）。拼接格式如下：

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

我們以前都有嘗試過將子查詢分拆成多個(gè)部分，然后分別傳遞的方式，不過一旦項(xiàng)目深入之后問題總比想象的要多得多。譬如參數(shù)過多導(dǎo)致調(diào)用難度增加，為了實(shí)現(xiàn)分頁不得不將寫好的整條語句拆成幾個(gè)部分多余浪費(fèi)時(shí)間，出問題時(shí)調(diào)試的復(fù)雜度也增加了，多表分頁也相對(duì)難以處理，經(jīng)驗(yàn)不足的程序員常常沒耐心看懂現(xiàn)有代碼進(jìn)而又捏造了一個(gè)所謂的改進(jìn)版（事實(shí)上這種情況還很多）……

不過即便是整個(gè)子查詢傳進(jìn)來，也仍然會(huì)有不同的處理方式。譬如我上文提到的那個(gè)專家說他們就曾嘗試過把傳遞進(jìn)來子查詢切分成多個(gè)部分再重新組合，但后來發(fā)現(xiàn)復(fù)雜的子查詢極難寫對(duì)，徒增了團(tuán)隊(duì)里新人的挫敗感……

外層查詢中的那個(gè)星號(hào)是比較關(guān)鍵的一點(diǎn)，盡管我們都知道查詢中出現(xiàn)星號(hào)往往是不好的，但分頁時(shí)依然拘泥這一點(diǎn)的話，必然會(huì)到導(dǎo)致復(fù)雜的拼接。復(fù)雜的拼接往往不好寫，調(diào)用時(shí)也容易出錯(cuò)，時(shí)不時(shí)還得回頭去看內(nèi)部的實(shí)現(xiàn)再推導(dǎo)出該如何調(diào)用，這個(gè)過程顯然是比較浪費(fèi)時(shí)間的。

2.2、通過存儲(chǔ)過程來分頁

我本人大部分時(shí)候還是通過存儲(chǔ)過程來實(shí)現(xiàn)分頁的，不過對(duì)很多人來說寫存儲(chǔ)過程甚至調(diào)用存儲(chǔ)過程都是比較難的，我覺得主要原因還是因?yàn)橄嚓P(guān)知識(shí)點(diǎn)不熟、寫的少。下面列出了寫分頁存儲(chǔ)過程和調(diào)用存儲(chǔ)過程的相關(guān)參考連接：

下面是一個(gè)調(diào)用 Oracle 分頁存儲(chǔ)過程的 C# 方法：

/// <summary>/// 調(diào)用存儲(chǔ)過程，執(zhí)行分頁/// </summary>/// <param name="tableName">表名</param>/// <param name="queryFields">查詢（字段）列表</param>/// <param name="queryWhere">查詢條件</param>/// <param name="orderBy">排序子句</param>/// <param name="pageIndex">頁索引（頁碼）</param>/// <param name="pageSize">頁大?。宽摂?shù)據(jù)條數(shù)）</param>/// <param name="pageCount">總頁數(shù)</param>/// <param name="rowCount">總行數(shù)</param>/// <param name="resultSet">結(jié)果集</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); // 調(diào)用存儲(chǔ)過程
    pageCount = Verifier.VerifyInt(ps[6].Value);
    rowCount = Verifier.VerifyInt(ps[7].Value);
}

2.3、兩個(gè)通用的分頁存儲(chǔ)過程

下面這個(gè)存儲(chǔ)過程是從我曾負(fù)責(zé)過的一個(gè)項(xiàng)目中抽取出來的，也是我第一次嘗試寫存儲(chǔ)過程分頁，100%原創(chuàng)，中間改版過幾次，為方便閱讀注釋內(nèi)容已被我去掉，現(xiàn)在的這個(gè)版本中的i_queryFields參數(shù)是不接受星號(hào)的：

CREATE OR REPLACE PROCEDURE sp_paging(
  i_tableName VARCHAR2, -- 表名
  i_queryFields VARCHAR2, -- 查詢（字段）列表
  i_queryWhere VARCHAR2, -- 查詢條件
  i_orderBy VARCHAR2, -- 排序子句
  i_pageIndex NUMBER, -- 當(dāng)前頁索引
  i_pageSize NUMBER, -- 頁大小
  o_rowCount OUT NUMBER, -- 總行數(shù)
  o_pageCount OUT NUMBER, -- 總頁數(shù)
  o_resultSet OUT SYS_REFCURSOR -- 結(jié)果集
) IS
  v_count_sql VARCHAR2(2000);
  v_select_sql VARCHAR2(4000);  BEGIN
    -- 拼接查詢總行數(shù)的語句
    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;

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

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

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

      -- 當(dāng)記錄總數(shù)小于或等于頁大小時(shí)，查詢所有記錄      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;

下面這個(gè)存儲(chǔ)過程摘自《劍破冰山——Oracle開發(fā)藝術(shù)》一書，有刪改：

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, -- 返回總條數(shù)
  o_pageCount OUT NUMBER, -- 返回總頁數(shù)
  o_resultSet OUT SYS_REFCURSOR -- 返回分頁結(jié)果集
)IS
  v_startRows INT; -- 開始行
  v_endRows INT; -- 結(jié)束行 
  v_pageSize INT;
  v_pageIndex INT;
  v_queryFields VARCHAR2(2000);
  v_queryWhere VARCHAR2(2000);
  v_orderBy VARCHAR2(200);
  v_count_sql VARCHAR2(1000); -- 接收統(tǒng)計(jì)數(shù)據(jù)條數(shù)的 SQL 語句
  v_select_sql VARCHAR2(4000); -- 接收查詢分頁數(shù)據(jù)的 SQL 語句
BEGIN
  -- 如果沒有表名，則直接返回異常消息
  -- 如果沒有字段，則表示查詢?nèi)孔侄?nbsp; 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;

  -- 如果未指定查詢頁，則默認(rèn)為首頁  IF i_pageIndex IS NULL OR i_pageIndex<1 THEN
    v_pageIndex:=1;  ELSE
    v_pageIndex:=i_pageIndex;  END IF;

  -- 如果未指定每頁記錄數(shù)，則默認(rèn)為 10 條  IF i_pageSize IS NULL THEN
    v_pageSize:=10;  ELSE
    v_pageSize:=i_pageSize;  END IF;

  -- 構(gòu)造查詢總條數(shù)的語句
  v_count_sql:='SELECT COUNT(1) FROM '||i_tableName||v_queryWhere;
  --DBMS_OUTPUT.PUT_LINE(v_count_sql||';');

  -- 構(gòu)造查詢數(shù)據(jù)的語句
  v_select_sql:='(SELECT '||v_queryFields||' FROM '||i_tableName||v_queryWhere||v_orderBy||') t2';

  -- 查詢總條數(shù)
  EXECUTE IMMEDIATE v_count_sql INTO o_rowCount;

  -- 得到總頁數(shù)  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;

  -- 如果當(dāng)前頁大于最大頁數(shù)，則取最大頁數(shù)  IF i_pageIndex>o_pageCount THEN
    v_pageIndex:=o_pageCount;  END IF;



本文鏈接：http://www.cnblogs.com/hanzongze/p/oracle-paging-1.html
版權(quán)聲明：本文為博客園博主 韓宗澤 原創(chuàng)，作者保留署名權(quán)！歡迎通過轉(zhuǎn)載、演繹或其它傳播方式來使用本文，但必須在明顯位置給出作者署名和本文鏈接！個(gè)人博客，能力有限，若有不當(dāng)之處，敬請(qǐng)批評(píng)指正，謝謝！

http://www.cnblogs.com/hanzongze/p/oracle-paging-1.html