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.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-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、通過 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-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-1)*:pageSize+1)

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

不過即便是整個子查詢傳進來，

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

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

2.2、通過存儲過程來分頁

我本人大部分時候還是通過存儲過程來實現分頁的，不過對很多人來說寫存儲過程甚至調用存儲過程都是比較難的，

我覺得主要原因還是因為相關知識點不熟、寫的少。下面列出了寫分頁存儲過程和調用存儲過程的相關參考連接：

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

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

/// /// 調用存儲過程，執行分頁 /// /// 表名 /// 查詢（欄位）列表 /// 查詢準則 /// 排序子句 /// 頁索引（頁碼） /// 頁大小（每頁數據條數） /// 總頁數 /// 總行數 /// 結果集 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 '||((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;="">

下面這個存儲過程摘自《劍破冰山——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_pageIndexo_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_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

100%原創，中間改版過幾次，為方便閱讀注釋內容已被我去掉，現在的這個版本中的i_queryFields參數是不接受星號的：

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

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

3、總結

本文連結：http://www.cnblogs.com/hanzongze/p/oracle-paging-1.html