簡介

遞歸 SQL 是用于查詢數(shù)據(jù)層次結(jié)構(gòu)的一種非常強(qiáng)大的方式。組織結(jié)構(gòu)（部門、子部門、子子部門，等等）、討論論壇（發(fā)貼、響應(yīng)、對響應(yīng)的響應(yīng)，等等）、原料帳單、產(chǎn)品分類以及文檔層次結(jié)構(gòu)都是層次型數(shù)據(jù)的例子。

IBM? DB2? Universal Database? (UDB)是實(shí)現(xiàn)了遞歸 SQL 的幾種關(guān)系數(shù)據(jù)庫產(chǎn)品中的一種。通常，可以將 DB2 方法看作一種高度強(qiáng)大和靈活的實(shí)現(xiàn)。DB2 在遞歸優(yōu)勢上的一個體現(xiàn)就是在單個的 DB2 表中查詢多個層次結(jié)構(gòu)的能力。（要了解更多這方面的細(xì)節(jié)，請參考在 DB2 開發(fā)者園地（DB2 Developer Domain）上由 Srini Venigalla 撰寫的文章 使用 DB2 v7.2 中的 SQL UDF 擴(kuò)大遞歸機(jī)會 。

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

最根本的問題就是，在 Oracle 和 DB2 中，查詢的默認(rèn)排序次序各不相同。乍一看來這并不重要，因?yàn)橥ǔ?yīng)用程序并不十分依賴于默認(rèn)的排序次序（沒有使用 ORDER BY 子句）。然而在實(shí)際中，需要用 Oracle 提供的默認(rèn)排序次序來解決許多問題，例如顯示討論的線索。很多應(yīng)用程序都是基于 Oracle 的排序次序的假設(shè)，因而當(dāng)要將那些應(yīng)用程序移植到 DB2 UDB 時，要理解這一點(diǎn)。

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

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


遞歸 SQL 如何工作？

遞歸通常表現(xiàn)為三個基本的步驟：

  1. 初始化。
  2. 遞歸，或者在整個層次結(jié)構(gòu)中重復(fù)對邏輯的迭代。
  3. 終止。

在初始步驟中，要準(zhǔn)備好工作區(qū)域，并用初始值設(shè)置好變量。遞歸由工作區(qū)域中的商業(yè)邏輯操作以及隨后對下一遞歸的調(diào)用組成，這里采用一種嵌套的方式。最后，終止步驟用于限定遞歸。打個比方，可以理解為對嵌套級數(shù)進(jìn)行計數(shù)，當(dāng)達(dá)到某一特定級數(shù)時便停止執(zhí)行。

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

  1. 創(chuàng)建初始結(jié)果集。
  2. 基于現(xiàn)有的結(jié)果集進(jìn)行遞歸。
  3. 查詢完畢，返回最終的結(jié)果集。

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

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

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

這個表的內(nèi)容代表了一個層次結(jié)構(gòu)。下面的 圖 1就是一個例子：


圖 1. 一個表層次結(jié)構(gòu)的例子
Table hierarchy

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

												
														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，隨著查詢的繼續(xù)執(zhí)行，temptab 會逐漸變大。下面給出了所有的遞歸元素：

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

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

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

下面是例子查詢的結(jié)果：

												
														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 是如何執(zhí)行這種遞歸查詢的。嵌套的循環(huán)連接（NLJOIN）以一個臨時結(jié)果表（TEMP）為基礎(chǔ)，而這次連接的的結(jié)果又再次通過 UNION 被放到這個臨時表中。

Oracle 與 DB2 UDB 之間的差異

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

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

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

												
														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 中維護(hù)一個自定義的偽列：

												
														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 中另一個非常重要的差異就是由遞歸查詢生成的結(jié)果集的搜索次序。在 Oracle 中，層次結(jié)構(gòu)是由深度優(yōu)先算法創(chuàng)建的。這樣一來，當(dāng)檢索整個例子層次結(jié)構(gòu)時，產(chǎn)生的結(jié)果集就是這個樣子：

												
														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

這個結(jié)果集說明，在查詢延伸到鄰節(jié)點(diǎn)之前，先要瀏覽完每個子節(jié)點(diǎn)。然而，在 DB2 中，層次結(jié)構(gòu)是通過廣度優(yōu)先算法創(chuàng)建的：

												
														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

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

												
														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 時，不能以這樣的次序重新得到結(jié)果集。如果非要嘗試這么做的話，將得到下面的結(jié)果：

												
														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

顯然，對于用戶來說該結(jié)果集完全沒有用，因?yàn)檫@里失去了論壇上各個帖子之間的相關(guān)性。

DB2 UDB 中仿效 Oracle 的行為

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

下面的例子將創(chuàng)建與上一例子中 Oracle 中的查詢完全一致的結(jié)果集：

												
														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!

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

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

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

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

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

												
														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!

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


結(jié)束語

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