簡介

遞歸 SQL 是用于查詢數據層次結構的一種非常強大的方式。組織結構（部門、子部門、子子部門，等等）、討論論壇（發貼、響應、對響應的響應，等等）、原料帳單、產品分類以及文檔層次結構都是層次型數據的例子。

IBM? DB2? Universal Database? (UDB)是實現了遞歸 SQL 的幾種關系數據庫產品中的一種。通常，可以將 DB2 方法看作一種高度強大和靈活的實現。DB2 在遞歸優勢上的一個體現就是在單個的 DB2 表中查詢多個層次結構的能力。（要了解更多這方面的細節，請參考在 DB2 開發者園地（DB2 Developer Domain）上由 Srini Venigalla 撰寫的文章 使用 DB2 v7.2 中的 SQL UDF 擴大遞歸機會 。

如果您要將數據從一個 RDBMS 移植到另一個 RDBMS，那么重要的是要知道遞歸 SQL 的實現因產品而異。特別地，在 Oracle 與 DB2 UDB 之間的差異 這一部分，我將解釋在將項目從 Oracle 移植到 DB2 并且涉及遞歸 SQL 時經常會出現的一個問題。

最根本的問題就是，在 Oracle 和 DB2 中，查詢的默認排序次序各不相同。乍一看來這并不重要，因為通常應用程序并不十分依賴于默認的排序次序（沒有使用 ORDER BY 子句）。然而在實際中，需要用 Oracle 提供的默認排序次序來解決許多問題，例如顯示討論的線索。很多應用程序都是基于 Oracle 的排序次序的假設，因而當要將那些應用程序移植到 DB2 UDB 時，要理解這一點。

當然，除了解釋這個問題之外，我還會給出針對 DB2 中這一難題的解決方案的要點。要看這方面的內容，參見 在 DB2 UDB 中仿效 Oracle 的行為這一部分。

為了給讀者提供有關一般遞歸，尤其是遞歸 SQL 的一些背景信息，我將從簡要地介紹 DB2 遞歸 SQL 開始我們的話題。


遞歸 SQL 如何工作？

遞歸通常表現為三個基本的步驟：

  1. 初始化。
  2. 遞歸，或者在整個層次結構中重復對邏輯的迭代。
  3. 終止。

在初始步驟中，要準備好工作區域，并用初始值設置好變量。遞歸由工作區域中的商業邏輯操作以及隨后對下一遞歸的調用組成，這里采用一種嵌套的方式。最后，終止步驟用于限定遞歸。打個比方，可以理解為對嵌套級數進行計數，當達到某一特定級數時便停止執行。

這一原理也可以應用到 DB2 中的遞歸 SQL。遞歸 SQL 是一種可以分為三個執行階段的查詢：

  1. 創建初始結果集。
  2. 基于現有的結果集進行遞歸。
  3. 查詢完畢，返回最終的結果集。

初始的結果集建立在對基本表的常規 SQL 查詢的基礎上，這是公共表表達式（CTE）的第一部分。公共表表達式是用于支持遞歸的手段，它的第二部分對自己進行調用并將其與基本表相連接。從該 CTE 中進行選擇的查詢便是終止步驟。

下面的例子演示了這一過程。DEPARTMENT是一個包含了有關某個部門的信息的表：

												
														CREATE TABLE departments (deptid INT,
			  deptname VARCHAR(20),
			  empcount INT,
			  superdept INT)

這個表的內容代表了一個層次結構。下面的 圖 1就是一個例子：


圖 1. 一個表層次結構的例子
Table hierarchy

對于一個給定的部門，該部門包括所有的子部門，要獲得該部門的雇員人數，需要一個遞歸查詢：

												
														WITH temptab(deptid, empcount, superdept) AS
   (    SELECT root.deptid, root.empcount, root.superdept
            FROM departments root
            WHERE deptname='Production'
     UNION ALL
        SELECT sub.deptid, sub.empcount, sub.superdept
            FROM departments sub, temptab super
            WHERE sub.superdept = super.deptid
   )
SELECT sum(empcount) FROM temptab

在這個例子中，CTE 被稱作 temptab，隨著查詢的繼續執行，temptab 會逐漸變大。下面給出了所有的遞歸元素：

  1. 在 temptab 中建立初始結果集。它包含了部門“Production”的雇員人數： 
    SELECT root.deptid, root.empcount, root.superdept
            FROM departments root
            WHERE deptname='Production'

  2. 當在 temptab 中針對于各個子部門加入每一行記錄時，便發生了遞歸。該遞歸每一次執行的結果都通過 UNION ALL 加入到 temptab 中： 
    SELECT sub.deptid, sub.empcount, sub.superdept
            FROM departments sub, temptab super
            WHERE sub.superdept = super.deptid

  3. 最后的查詢就是從 CTE 中提取出所需的信息。在本例中，進行的是總計操作： 
    SELECT sum(empcount) FROM temptab

下面是例子查詢的結果：

												
														1
-----------
SQL0347W  The recursive common table expression "TORSTEN.TEMPTAB" may contain
an infinite loop.  SQLSTATE=01605
         50
  1 record(s) selected with 1 warning messages printed.

通過 DB2 解釋工具可以檢查 DB2 是如何執行這種遞歸查詢的。嵌套的循環連接（NLJOIN）以一個臨時結果表（TEMP）為基礎，而這次連接的的結果又再次通過 UNION 被放到這個臨時表中。

Oracle 與 DB2 UDB 之間的差異

Oracle 通過使用 CONNECT BY PRIOR 提供了類似的特性。在 Oracle 中，上面的例子可以這樣來實現：

												
														SELECT sum(empcount) FROM STRUCREL
   CONNECT BY PRIOR superdept = deptid
     START WITH deptname = 'Production';

除了語法上的不同之外，DB2 與 Oracle 在功能性上也有差異。當使用 CONNECT BY PRIOR 時，Oracle 提供了內建的偽列 level。在 Oracle 中，下面的查詢提供了所有的部門以及這些部門所在的層次結構： 

												
														SELECT deptname, level FROM departments 
  CONNECT BY PRIOR superdept = deptid
  START WITH deptname = 'Samples & Co.';
DEPTNAME             LEVEL
-------------------- -----------
Samples & Co.                  1
Production                     2
QA                             3
Manufacturing                  3
Prebuilding                    4
Finalbuilding                  4
Sales                          2
North                          3
East                           3
South                          3	
West                           3
IT                             2

這種偽列通常用于限制那些查詢的遞歸深度。例如，為了檢索“Sales”這個部門的直屬子部門，在 Oracle 中可以使用下面的查詢：

												
														SELECT deptname FROM departments CONNECT BY PRIOR superdept = deptid
START WITH deptname = 'Sales' AND level=2;
DEPTNAME
--------------------
North
East
South
West

在 DB2 中可以輕易地仿效這一特性，只需像下面這樣在 CTE 中維護一個自定義的偽列：

												
														WITH temptab(deptid, deptname, superdept, level) AS
   (    SELECT root.deptid, root.deptname, root.superdept, 1
            FROM departments root
            WHERE deptname='Sales'
     UNION ALL
        SELECT sub.deptid, sub.deptname, sub.superdept, super.level+1
            FROM departments sub, temptab super
            WHERE sub.superdept = super.deptid
   )
SELECT deptname FROM temptab WHERE level=2;

除了 level 偽列，在 DB2 和 Oracle 中另一個非常重要的差異就是由遞歸查詢生成的結果集的搜索次序。在 Oracle 中，層次結構是由深度優先算法創建的。這樣一來，當檢索整個例子層次結構時，產生的結果集就是這個樣子：

												
														SELECT deptname, level FROM departments CONNECT BY PRIOR superdept = deptid
START WITH deptname = 'Samples & Co.;
DEPTNAME             LEVEL
-------------------- -----------
Samples & Co.                  1
Production                     2
QA                             3
Manufacturing                  3
Prebuilding                    4
Finalbuilding                  4
Sales                          2
North                          3
East                           3
South                          3	
West                           3
IT                             2

這個結果集說明，在查詢延伸到鄰節點之前，先要瀏覽完每個子節點。然而，在 DB2 中，層次結構是通過廣度優先算法創建的：

												
														WITH temptab(deptid, deptname, superdept, level) AS
   (    SELECT root.deptid, root.deptname, root.superdept, 1
            FROM departments root WHERE deptname='Samples & Co.'
     UNION ALL
        SELECT sub.deptid, sub.deptname, sub.superdept, super.level+1
            FROM departments sub, temptab super
            WHERE sub.superdept = super.deptid
   )
SELECT deptname, level FROM temptab;
DEPTNAME             LEVEL
-------------------- -----------
Samples & Co.                  1
Production                     2
Sales                          2
IT                             2
QA                             3
North                          3
East                           3
South                          3	
West                           3
Manufacturing                  3
Prebuilding                    4
Finalbuilding                  4

這意味著，結果集是一級一級地創建的。在本例中，這種差異或許算不了什么。但是在有些遞歸 SQL 的案例中，默認的排序次序則是至關重要的。例如，有一個包含討論論壇的表：

												
														CREATE TABLE discussion (postid INTEGER,
superid INTEGER,
title VARCHAR2(100),
text VARCHAR2(1000) )

為了獲得對所有討論線索的了解，在 Oracle 中可以這樣來查詢這個表：

												
														SELECT RPAD('', level-1, '--') || title FROM discussion
CONNECT BY PRIOR superid = postid START WITH postid = 0;
1
-------------------------------------
Install Problem
--Re: Install Problem
----Re: Install Problem
------Re: Install Problem
--------Got it
--General comment
----Re: General Comment
Cannot find file
--Re: Cannot find file
----Re: Cannot find file
--Re: Cannot find file
Help! Documentation missing!

在 DB2 中使用無格式（plain）遞歸 SQL 時，不能以這樣的次序重新得到結果集。如果非要嘗試這么做的話，將得到下面的結果：

												
														WITH temptab(superid, postid, title, text, level)
     AS 
     (    SELECT root.superid, root.postid, root.title, root.text, 1
              FROM discussion root
              WHERE postid=0
       UNION ALL
          SELECT sub.superid, sub.postid, sub.title, sub.text, super.level+1
             FROM discussion sub, temptab super
             WHERE sub.superid = super.postid
     )
SELECT VARCHAR(REPEAT('--', level-1) || title , 60) FROM temptab;
1
------------------------------------
Problem Discussions
--Install Problem
--Cannot find file
--Help! Documentation missing!
----Re: Install Problem
----General comment
----Re: Cannot find file
----Re: Cannot find file
------Re: Install Problem
------Re: General Comment
------Re: Cannot find file
--------Re: Install Problem
----------Got it

顯然，對于用戶來說該結果集完全沒有用，因為這里失去了論壇上各個帖子之間的相關性。

DB2 UDB 中仿效 Oracle 的行為

在 DB2 中，要生成 Oracle 中那樣的深度優先次序，解決方案的基礎就是引入一個附加的偽列，這個偽列可以在 ORDER BY 屬性中使用。這個列的類型是 VARCHAR，包含了到每個節點的路徑，其格式為“1.3.1”。另外還引入了一個用戶定義的表函數，這個函數可以返回一個給定節點的所有子節點。通過將子節點的序號連接到上級節點的路徑上，能夠可靠地維護偽列代碼。可以使用 DB2 的 RANK() 函數來檢索一個子節點的序號。之后，遞歸查詢從這個函數中進行選擇，并提供當前節點的 id 以及它的路徑作為輸入。

下面的例子將創建與上一例子中 Oracle 中的查詢完全一致的結果集：

												
														CREATE FUNCTION GetResponses(code VARCHAR(100), superid INTEGER)
   RETURNS TABLE(code VARCHAR(100), superid INTEGER, postid INTEGER,
                 title VARCHAR(100), text VARCHAR(1000))
   READS SQL DATA DETERMINISTIC NO EXTERNAL ACTION
   RETURN SELECT GetResponses.code || '.'
                  || RTRIM(CHAR(RANK() OVER (ORDER BY postid))),
                 T.superid , T.postid, T.title, T.text
              FROM discussion T
              WHERE T.superid = GetResponses.superid;
WITH TEMPTAB(code, superid, postid, title, text, level)
     AS 
     (    VALUES(CAST('1' AS VARCHAR(100)), CAST(NULL AS INTEGER), 0,  
                 CAST(NULL AS VARCHAR(100)), CAST(NULL AS VARCHAR(1000)), 0)
       UNION ALL
          SELECT t.code, t.superid, t.postid, t.title, t.text, level+1
             FROM TEMPTAB,
               TABLE(GetResponses(TEMPTAB.code, TEMPTAB.postid)) AS T
     )
SELECT VARCHAR(REPEAT('--', level-1) || title , 60)
     FROM TEMPTAB T
     WHERE t.superid is not null
     ORDER BY code;
1
-------------------------------------
Install Problem
--Re: Install Problem
----Re: Install Problem
------Re: Install Problem
--------Got it
--General comment
----Re: General Comment
Cannot find file
--Re: Cannot find file
----Re: Cannot find file
--Re: Cannot find file
Help! Documentation missing!

為了使應用程序中的語句簡單一些，這里同樣可以將這些遞歸語句包裝到一個 UDF 中。

一種更好地使用 DB2 Node 類型的方法

您必須清楚，基于一個字符串使用偽列以強制性地使結果集具有某一特定的次序，這只能保證總體上的層次結構次序。如果某個節點的直屬子節點的數量超過 9 的話，這樣做未必能夠正確地對這些子節點排序。這是因為像“1.2.13”這樣的字符串比“1.2.13”有著更低的次序。但是從語義上講，事情剛好相反。如果您要依賴于這種方法，而又不能保證最多只有 9 個直屬子節點，那么您就決不能為偽列使用一個字符串。

相反，您可以使用 DB2 Node 類型，這是一個 DB2 擴展，當前在 IBM DB2 Developer Domain 上（由 Jacques Roy 撰寫的 Using the Node Data Type to Solve Problems with Hierarchies in DB2 Universal Database ）可以獲得。您必須使用最低版本為 1.1 的 Node 類型擴展。可以通過 nodeVersion() 函數來檢查版本。如果該函數不存在，那么就說明您使用的是更老版本的 DB2 Node 類型。

因此，現在我們不使用 VARCHAR 類型來維護偽列代碼，而是使用用戶定義類型的 Node。下面的例子對此作了演示。該例子將創建與上面使用 VARCHAR 的例子一樣的結果集：

												
														CREATE FUNCTION GetResponsesN(code Node, superid INTEGER)
   RETURNS TABLE(code Node, superid INTEGER, postid INTEGER,
                 title VARCHAR(100), text VARCHAR(1000))
   READS SQL DATA DETERMINISTIC NO EXTERNAL ACTION
RETURN SELECT nodeInput(nodeOutput(GetResponsesN.code) || '.' ||  
                                 RTRIM(CHAR(RANK() OVER (ORDER BY postid)))),
              T.superid , T.postid, T.title, T.text
         FROM discussion T
         WHERE T.superid = GetResponsesN.superid;
WITH TEMPTAB(code, superid, postid, title, text, level)
     AS 
     (    VALUES(nodeInput('1.1'), CAST(NULL AS INTEGER), 0,  
                 CAST(NULL AS VARCHAR(100)), CAST(NULL AS VARCHAR(1000)), 0)
       UNION ALL
          SELECT t.code, t.superid, t.postid, t.title, t.text, level+1
             FROM TEMPTAB,
               TABLE(GetResponsesN(TEMPTAB.code, TEMPTAB.postid)) AS T
     )
SELECT VARCHAR(REPEAT('--', level-1) || title , 60)
     FROM TEMPTAB T
     WHERE t.superid is not null
     ORDER BY code;
1
-------------------------------------
Install Problem
--Re: Install Problem
----Re: Install Problem
------Re: Install Problem
--------Got it
--General comment
----Re: General Comment
Cannot find file
--Re: Cannot find file
----Re: Cannot find file
--Re: Cannot find file
Help! Documentation missing!

為了創建一個 Node 值，我們必須使用函數 nodeInput()，并為之提供像“1.2” 這樣的一個字符串作為輸入。對于根節點，輸入是“1.1”（由于 DB2 節點類型的具體實現，我們只能從 1.1 開始，而不是從 1 開始）。對于所有其他的節點，我們同樣使用 DB2 的 RANK() 函數來為直屬子節點分配序號。這是在 GetResponsesN() 函數中進行的。之后，這個序號被連接到上級節點的字符表示（通過 nodeOutput() 獲得）上，再將這樣得到的字符串作為輸入，通過 nodeInput() 函數創建新的 Node 值。


結束語

DB2 UDB 為遞歸 SQL 而設的方法提供了一種非常靈活的方式來處理層次結構。正如本文所演示的，DB2 UDB 能夠輕易地仿效其他數據庫供應商的行為，因為 DB2 通過用戶定義的函數提供了方便自如的可擴展性。而且，DB2 UDB 還提供了處理非常高級的遞歸查詢的方法，例如那些在單個表中有多重層次結構的情況下的遞歸查詢。通過使用我描述過的這些技術，在移植應用程序時您可以充分利用 DB2 的長處。