ORACLE遞迴查詢

標籤：des   style   blog   http   color   使用   io   strong   

轉自：http://www.oracle.com/technetwork/cn/articles/hartley-recursive-086819-zhs.html

遞迴資料庫處理，也稱為材料清單 或零件分解問題，適用於包括人力資源、製造業、金融市場和教育在內的多種應用領域。這類處理中所涉及的資料稱為 樹狀結構 資料或 階層 資料。Oracle 資料庫長期以來一直通過專用文法（CONNECT BY 子句）支援遞迴。Oracle Database 11g 第 2 版通過子查詢分解來支援遞迴，這就為解決下面的老問題提供了一個更好的新方法：查詢階層資料。

表運算式

首先，我們回顧一下針對第 2 版的新功能建立的 SQL 文法。這裡我們選擇教育作為說明遞迴處理的領域。我們的原始樣本使用下面的 Course 表：

                                  CNO CNAME                  CRED CLABFEE CDEPT --- ---------------------- ---- ------- ----- C11 INTRO TO CS               3     100 CIS C22 DATA STRUCTURES           3      50 CIS C33 DISCRETE MATHEMATICS      3       0 CIS C44 DIGITAL CIRCUITS          3       0 CIS C55 COMPUTER ARCH.            3     100 CIS C66 RELATIONAL DATABASE       3     500 CIS C77 COMPUTER PROGRAMMING 1    3     100 CIS P11 EMPIRICISM                3     100 PHIL P22 RATIONALISM               3      50 PHIL P33 EXISTENTIALISM            3     200 PHIL P44 SOLIPSISM                 6       0 PHIL                               

該表中的每行描述一門課程，由 CNO 列唯一標識。每門課程由一個系 (CDEPT) 開設，每門課程分配了學生修完該課程應取得的學分 (CRED)，還包含了註冊選修該課程的學生需要支付的課程學費 (CRED)。以下查詢顯示了對哲學系開設的課程按學分-課程學費組合進行查詢的結果：

                                  SELECT CRED, CLABFEE FROM COURSE WHERE CDEPT = ‘PHIL‘;
CRED CLABFEE ---- -------    3     100    3      50    3     200    6       0                               

觀察結果：查詢的輸入是一個表。查詢的輸出還是一個表 — 結果表。通過將查詢括在圓括弧內然後包括在另一個 SELECT 的 FROM 子句中，結果表本身可以是查詢的目標。這樣的查詢也可以稱為表運算式，因為它產生一個表。也可以將它稱為 子查詢，因為它是另一個查詢之內的查詢。

對子查詢的支援使得針對同一個問題可用多種方式建立查詢。如果另一個系也開設了一門學分-課程學費組合與哲學系開設的某門課程的學分-課程學費組合（即第一個查詢的輸出）相同的課程，考慮一下如何確定。查詢 1、2 和 3（在本文結尾處給出）提供了三個解決方案。執行這三個查詢都將產生以下結果集：

                                  CNO CNAME                  CRED CLABFEE CDEPT --- ---------------------- ---- ------- ----- C77 COMPUTER PROGRAMMING 1    3     100 CIS C55 COMPUTER ARCH.            3     100 CIS C11 INTRO TO CS               3     100 CIS C22 DATA STRUCTURES           3      50 CIS                               

子查詢分解

子查詢的使用可以進入另一個層面。考慮對視圖的查詢。從概念上而言，一個視圖定義一個可對其執行查詢的結果表。假設可以編寫一個運算式，從而允許一個名稱與結果表相關聯。則使用該名稱的查詢將是一個對該結果表的查詢。子查詢分解（也稱為通用資料表運算式）正是這一思想的體現。WITH 子句為子查詢塊指派一個名稱。之後可以使用指派的名稱在某個查詢中引用該查詢塊。

使用此方法，查詢 4 找到了課程學費總額最高的系。該查詢包含兩級彙總。首先，通過 GROUP BY 子句對每個系應用 SUM 函數來確定每個系的總費用。其次，根據每個系的總費用額確定總費用額最高的系。DTOTAL 是一個命名查詢，為其設計了多個引用。沒有子查詢分解時，必須在兩個後續的 FROM 子句中針對 Course 表對 SELECT 進行編碼。因為 SUM(CLABFEE) 的結果是一個匯出值，所以在子查詢中使用 TOTFEE 的列別名。對 DTOTAL 的後續引用將使用這個別名。在 Oracle Database 11g 第 2 版中，可以在查詢運算式聲明中對列重新命名（也就是說使用列別名），而不是在子查詢中對列重新命名：

                                  WITH DTOTAL                                      (CDEPT, TOTFEE) AS                                                               

第 2 版進一步擴充了 WITH 子句，通過一個叫“遞迴的 WITH 子句”的特性支援遞迴查詢。首先，我們來快速回顧一下 Oracle 的專用遞迴文法。

遞迴的“舊”方法

從樹狀結構檢索資料的過程被稱作遞迴處理。傳統上，Oracle 資料庫通過兩個專門的子句 CONNECT BY 和 START WITH 支援遞迴處理。CONNECT BY 指明要在樹狀結構序列中檢索行。該子句中指定的條件指明了父-子關係。如果 PRIOR 出現在父列前面，則表示要進行向下遍曆。如果 PRIOR 位於子列前面，則表示向上遍曆。START WITH 指定了遍曆的起始點，稱為種源。可以從任何節點進入樹，通過 START WITH 子句確定進入的節點。

為了說明遞迴，我們使用 Course 表的修改版本，其中每門課程都有另一門課程作為修課的前提條件。一門課程的直接前提課程絕不會多餘一門；但是，一門課程可以是多門課程的前提條件。這種關係是遞迴的，因為它將一個實體與另一個同類型實體相關聯。Coursex 表中表示了這種關係，如下所示。

                                  CNO PCNO CNAME                  CRED CLABFEE CDEPT --- ---- ---------------------- ---- ------- ----- C11      INTRO TO CS               3     100 CIS C33 C11  DISCRETE MATHEMATICS      3       0 CIS C22 C33  DATA STRUCTURES           3      50 CIS C44 C33  DIGITAL CIRCUITS          3       0 CIS C55 C44  COMPUTER ARCH.            3     100 CIS C66 C22  RELATIONAL DATABASE       3     500 CIS C77 C33  INTRO TO PROGRAMMING 1    3     100 CIS P11      EMPIRICISM                3     100 PHIL P22 P11  RATIONALISM               3      50 PHIL P33 P11  EXISTENTIALISM            3     200 PHIL P44      SOLIPSISM                 6       0 PHIL                               

PCNO 是建立該關係的外鍵。如果某門課程沒有前提條件，則外索引值為 NULL。

遞迴關係的一個主要特徵是可以將它表示為樹狀結構。使用該結構時，使用術語“父”和“子”來描述樹上節點之間的關係。圖 1 中，C11 是 C33 的父節點，而 C33 是 C11 的子節點。沒有父節點的節點（如 C11、P11）對應於沒有前提條件的課程。這些節點位於樹的頂端，充當根節點。沒有子節點的節點（如 C66、C55）出現在樹的底部，稱作分葉節點。

圖 1 以樹狀結構表示的遞迴關係

查詢 5 使用遞迴處理方法識別作為課程 C22 的前提條件的所有課程的課程代號和課程名稱。運行該查詢將產生以下輸出：

                                  CNO PCNO CNAME --- ---- -------------------- C22 C33  DATA STRUCTURES C33 C11  DISCRETE MATHEMATICS C11 -    INTRO TO CS                               

使用 CONNECT BY 的 SELECT 語句可以引用 LEVEL 偽列。始終從層次 1 進入樹。隨著從種源開始向各個節點遍曆，層次逐漸增加。再遍曆回種源則減少層次。

遞迴的“新”方法

通過子查詢分解進行遞迴，需要使用 WITH 子句定義一個命名子查詢，還需要一個針對這個命名子查詢的查詢。查詢 6 使用新的遞迴的 WITH 子句特性實現了與查詢 5 中顯示的 CONNECT BY 查詢相同的結果。命名子查詢包含兩個通過 UNION ALL 操作組合的查詢塊。第一個查詢塊是一個初始化子查詢（也稱錨點），其編碼是非遞迴的，包括確定調查起始點的種源。系統將首先處理這個子查詢。第二個查詢塊是遞迴子查詢，它根據與結果中已有行的關係向結果添加行。此處的技巧是定義新行與舊行的關聯方式。新行是通過將命名查詢與錨點確定的原始表進行聯結而識別的。UNION ALL 將錨點與遞迴子查詢進行組合，確保不從結果中清除重複記錄。這兩個查詢塊必須是可相容合并的；也就是說，兩個查詢塊中必須選擇相同的列數。

列表中緊跟著查詢名稱的別名構成了該命名查詢的結果表的各列。在遞迴子查詢以及對命名查詢的後續查詢中可以引用這些別名。

遞迴需要一個終止條件。每次執行遞迴子查詢時，因為它要讀取由通用資料表運算式建立的臨時視圖，所以它只能看到由該遞迴查詢的上一次迭代添加到該視圖中的行。系統不斷評估遞迴查詢，直到不再向臨時視圖添加新行為止。

我們現在從概念上來瞭解此過程是如何針對查詢 6 工作的。首先，執行初始話子查詢來產生臨時視圖。這個子查詢的執行向臨時視圖（此處命名為 C）添加以下行：

                                  C22 C33  DATA STRUCTURES                               

執行完初始化查詢後，通過合并臨時視圖的內容來執行遞迴子查詢。因此，執行以下查詢：

                                  SELECT X.CNO, X.PCNO, X.CNAME FROM                                      (SELECT CNO, PCNO, CNAME       FROM COURSEX       WHERE CNO = ‘C22‘) C, COURSEX X WHERE C.PCNO = X.CNO;                                                               

執行該查詢將向臨時視圖添加以下行：

                                  C33 C11  DISCRETE MATHEMATICS                               

再次執行遞迴子查詢，合并臨時視圖中新添加的行。因此，執行以下查詢：

                                  SELECT X.CNO, X.PCNO, X.CNAME FROM                                      (SELECT X.CNO, X.PCNO, X.CNAME       FROM (SELECT CNO, PCNO, CNAME             FROM COURSEX              WHERE CNO = ‘C22‘) C, COURSEX X       WHERE C.PCNO = X.CNO) C, COURSEX X WHERE C.PCNO = X.CNO;                                                               

執行該查詢將向臨時視圖添加以下行：

                                  C11 -    INTRO TO CS                               

通過合并臨時視圖中新添加的行，再次執行遞迴子查詢。這次，該查詢不產生結果。因為之前沒有向臨時視圖添加任何行，該操作完成。這個事件是終止條件。

遍曆方向

遞迴子查詢中指定的條件指明了父子關係。使用命名查詢 (C) 限定父列 (CNO)，從而指示遍曆方向向下。遍曆的起始點由初始子查詢中的種源確定。也可以向上遍曆樹來訪問儲存在父節點和祖先節點中的資訊。使用命名查詢限定子列 (PCNO)，從而指示遍曆方向向上。

LEVEL 偽列只能與 CONNECT BY 子句一起使用。但是，通過在查詢中另外引入別名也能達到同樣的效果。這一方法將在查詢 7 中示範，查詢 7 中使用一個名為 LVL 的別名來標識距種源的層次或距離。執行該查詢將產生以下結果：

                                  LVL CNO PCNO CNAME --- --- ---- --------------------   1 C22 C33  DATA STRUCTURES   2 C33 C11  DISCRETE MATHEMATICS   3 C11      INTRO TO CS                                 

該查詢的種源為 C22，因此結果表中對應行的 LVL 值為 1。如上面的圖 1 中所示，課程 C33 是 C22 的父節點，因此對該行來說 LVL 的值為 2。課程 C11 是 C33 的父節點，因此我們已經從種源向上移動了一層，結果表最後一行的 LVL 值反映了這點。

遞迴與迴圈

階層資料會引發的一個特殊情況是迴圈，當後代也是祖先時會出現這一情況。如果檢測到存在迴圈，則 CONNECT BY 會報告在遞迴查詢中存在一個錯誤。在 Oracle Database 10g 中，通過指定 NOCYCLE 可以使系統返回查詢的結果。如果不指定這個參數，由於資料中存在迴圈，查詢將失敗。CONNECT_BY_ISCYCLE 偽列指示當前行是否包含本身也是自己的祖先的子節點。

下面的 HAS_A_CYCLE 表包含一個迴圈：C33 和 C22 互為前提條件，並且每個都是另一個的父節點。

                                  CNO PCNO --- ---- C11 C22 C11 C33 C22 C22 C33                               

執行沒有 NOCYCLE 參數的遞迴查詢將導致以下錯誤：

                                  ORA-01436: CONNECT BY loop in user data                               

通過子查詢分解進行的遞迴使用 CYCLE 子句標記處理過程中的迴圈。在這個子句中可以引用命名查詢的各列，系統也可以使用命名查詢的各列來檢測迴圈。使用遞迴子查詢分解時，迴圈的概念也更加廣泛。如果某一行的祖先的迴圈列的值與當前行中迴圈列的值相同，則存在迴圈。用於檢測迴圈的列並不僅限於定義遞迴關係的列。

SET 子句在結果中產生了一個稱作迴圈標記的列，設定該列的值來指示是否針對當前行檢測到了迴圈。如果檢測到迴圈，將停止對該行的子行的搜尋。如果未檢測到迴圈，則將迴圈標記設定為指定的預設值。迴圈標記的值必須是單個字元。與 CONNECT BY 子句一樣，如果未在查詢中包括迴圈檢測，也就是說，沒有 CYCLE 子句，一旦發現迴圈就會出現錯誤。查詢 8 包括了 CYCLE 子句，用於檢測迴圈並繼續處理過程。在下面的結果表中可以看到，迴圈標記作為一列是可訪問的，但是它不在該命名查詢的範圍內。

                                  CNO PCNO CYCLEMARKER --- ---- ----------- C11      N C22 C11  N C33 C22  N C22 C33  Y                               

搜尋順序

對遞迴處理的另一個增強是可以指定遍曆順序。既可指定 DEPTH FIRST，也可指定 BREADTH FIRST，二者都是層序遍曆。在 DEPTH FIRST 遍曆中，先返回一個節點的子節點，然後再返回該節點的同級節點（即具有相同父節點的節點）。在 BREADTH FIRST 遍曆中，先返回該層次中的所有行，然後再下行至下個層次。因此，某節點的同級節點在其子節點之前返回。根據 BY 關鍵字後面列出的列中的值對同級節點進行排序。可以是升序 (ASC)，也可以是降序 (DESC)。

使用 SET 子句顯示搜尋過程中訪問節點的順序。還引進了一個列別名，可在在最終查詢中用來顯示結果或對結果排序。雖然 Oracle 遞迴處理過程中 LEVEL 概念的增加或減少反映了離開種源或接近種源，但 SET 子句中的別名值在整個遍曆中還是不斷增加。查詢 9 說明了按同級節點的課程學費值進行的 DEPTH FIRST 搜尋和對同級節點的排序。執行該查詢將產生以下結果。

                                  CNO          PCNO CLABFEE XX ------------ ---- ------- -- C11                   100  1    C33       C11        0  2       C77    C33      100  3       C22    C33       50  4          C66 C22      500  5       C44    C33        0  6          C55 C44      100  7                               

觀察同級節點 C77、C22 和 C44 的順序。它們依據各自課程學費的值出現在輸出中。在這三門課程中，課程 C77 的課程學費最高，因此在指定的降序序列中它首先出現。

為了建議使用 BREADTH FIRST 遍曆，我們做了下列假設：

• 沒有前提條件的課程為一年級課程。
• 有一個前提條件的課程為二年級課程。
• 有多個前提條件的課程為三年級/四年級的課程。

課程表的 BREADTH FIRST 遍曆將產生課程的排序，可以反映該學校的學士項目大學排名。將查詢 9 中的搜尋修改為 BREADTH FIRST 將產生以下輸出：

                                  CNO          PCNO CLABFEE XX ------------ ---- ------- -- C11                   100  1    C33       C11        0  2       C77    C33      100  3       C22    C33       50  4       C44    C33        0  5          C66 C22      500  6          C55 C44      100  7                               

遍曆網路

用於遍曆樹狀結構的方法同樣可用於遍曆網路。網路結構是由多對多關係構成的。例如，如果允許某門課程有多個必備前提條件，同時允許一門課程作為多門課程的前提條件，則需要一個單獨的表來表示這種關係。可以使用以前的 CONNECT BY 文法或新的子查詢分解文法遍曆這樣的表。

總結

Oracle Database 11g 第 2 版新增的遞迴的 WITH 子句特性為處理階層資料提供了新方法。還提供了更加強大的迴圈檢測功能，可以選擇使用 DEPTH FIRST 或 BREADTH FIRST 遍曆來處理資料。

在本文中，我們通過一些非常簡單的用例簡要介紹了這些特性。更多詳細資料，請參閱“參考資料”部分。

查詢 1：使用存在性測試的關聯子查詢 SELECT * FROM COURSE C1 WHERE CDEPT <> ‘PHIL‘ AND EXISTS (SELECT * FROM COURSE C2 WHERE C2.CLABFEE = C1.CLABFEE AND C2.CRED = C1.CRED AND C2.CDEPT = ‘PHIL‘) 查詢 2：返回多列的子查詢 SELECT * FROM COURSE WHERE CDEPT <> ‘PHIL‘ AND (CRED, CLABFEE) IN (SELECT CRED, CLABFEE FROM COURSE WHERE CDEPT = ‘PHIL‘); 查詢 3：使用表運算式聯結 SELECT C1.* FROM COURSE C1, (SELECT CRED, CLABFEE FROM COURSE WHERE CDEPT = ‘PHIL‘) C2 WHERE C1.CLABFEE = C2.CLABFEE AND C1.CRED = C2.CRED AND C1.CDEPT <> ‘PHIL‘; 查詢 4：子查詢分解樣本 WITH DTOTAL AS (SELECT CDEPT, SUM(CLABFEE) AS "TOTFEE" FROM COURSE GROUP BY CDEPT) SELECT CDEPT FROM DTOTAL WHERE TOTFEE = (SELECT MAX(TOTFEE) FROM DTOTAL); 查詢 5：使用 CONNECT BY 的遞迴處理 SELECT CNO, PCNO, CNAME FROM COURSEX CONNECT BY CNO = PRIOR PCNO START WITH CNO = ‘C22‘; 查詢 6：使用遞迴的 WITH 子句 WITH C (CNO, PCNO, CNAME) AS (SELECT CNO, PCNO, CNAME -- initialization subquery FROM COURSEX WHERE CNO = ‘C22‘ -- seed UNION ALL SELECT X.CNO, X.PCNO, X.CNAME -- recursive subquery FROM C, COURSEX X WHERE C.PCNO = X.CNO) SELECT CNO, PCNO, CNAME FROM C; 查詢 7：在遞迴性子查詢中報告層次 WITH C (LVL, CNO, PCNO, CNAME) AS ((SELECT 1, CNO, PCNO, CNAME FROM COURSEX WHERE CNO = ‘C22‘) UNION ALL (SELECT C.LVL+1, X.CNO, X.PCNO, X.CNAME FROM C, COURSEX X WHERE C.PCNO = X.CNO)) SELECT LVL, CNO, PCNO, CNAME FROM C; 查詢 8：在遞迴性子查詢中處理迴圈 WITH C (CNO, PCNO) AS (SELECT CNO, PCNO -- initialization subquery FROM HAS_A_CYCLE WHERE CNO = ‘C11‘ -- seed UNION ALL SELECT X.CNO, X.PCNO -- recursive subquery FROM C, HAS_A_CYCLE X WHERE X.PCNO = C.CNO) CYCLE CNO SET CYCLEMARKER TO ‘Y‘ DEFAULT ‘N‘ SELECT CNO, PCNO, CYCLEMARKER FROM C; 查詢 9：使用 search 子句的遞迴 WITH C (LVL, CNO, PCNO, CLABFEE) AS (SELECT 0, CNO, PCNO, CLABFEE FROM COURSEX WHERE CNO = ‘C11‘ UNION ALL SELECT C.LVL+1, X.CNO, X.PCNO, X.CLABFEE FROM C, COURSEX X WHERE C.CNO = X.PCNO) SEARCH DEPTH FIRST BY CLABFEE DESC SET XX SELECT LPAD(‘ ‘, LVL*3) || CNO AS "CNO", PCNO, CLABFEE, XX FROM C ORDER BY XX;

參考資料

• Oracle/SQL A Professional Programmer‘s Guide，Tim Hartley 和 Tim Martyn（McGraw-Hill 1992 年出版）
• SQL:1999 Understanding Relational Language Concepts，Jim Melton 和 Alan Simon（Morgan Kaufmann 2002 年出版）
• SQL 語言參考中的 Oracle Database 11g 第 2 版新增特性