以遞歸方式處理數據關係的一種新方法

Birgitta Hauser, 軟件工程師, Toolmaker Advanced Efficiency GmbH

簡介： 根據 SQL 標準，分級數據（如組織圖和材料單）或雙向數據（如航班中轉）可以通過使用遞歸通用表表達式 (RCTE) 進行評估。DB2 for i 的 V5R4 版本中提供了 RCTE 功能。其他的數據庫（如 Oracle）使用了一個非標準的數據查詢方法，叫做分級查詢子句。為了最大限度地提高可移植性，我們通過PTF SF99701 V9 版本使 DB2 for I 支持了這個功能。本文將介紹分級查詢子句的語法，以及如何組合使用新的運算符、偽列和特殊標量函數實現它。

本文的標籤：  odbc_(開放數據庫連接性), sql_(結構化查詢語言)

標記本文！

發佈日期： 2011 年 11 月 07 日 級別： 初級 原創語言： 英文 訪問情況 ： 413 次瀏覽 評論： 0 (查看 | 添加評論 - 登錄)

 平均分 (1個評分)

為本文評分

遞歸

根據 Wikipedia：遞歸（拉丁語 recurrere -> currere = run 和 re = return）是以自相似性的方式循環某些項的過程。在數學和計算機科學中，遞歸指的是基於自己 定義 來定義函數的一種方法。

存儲在表（或物理文件）中的遞歸數據指向同一表格/物理文件的其他行/記錄的另一個列/字段中的數據。

遞歸數據之間的關係可以是分層的（單向，比如 父母 -> 孩子，經理 -> 下屬）、雙向的或循環的（如航班中轉，法蘭克福 -> 紐約，但也可以是紐約 -> 法蘭克福）。

包含遞歸數據的表

在接下來的所有示例中，都會使用其中的表 STAFF（組織圖）或 FLIGHTS（航班中轉）其中的一個。

下圖顯示了一個小型組織圖和存儲數據的表 STAFF。表 STAFF 包含 3 個列：

• EMPLOYEE：僱員編號
• NAME：僱員的姓名
• BOSS：此僱員的直接主管的僱員編號

圖 1：分層關係 - 組織圖 - STAFF

下一個圖顯示一個含有航班中轉的圖表。在分析雙向數據時，會出現一個循環，最終會產生一個無限循環（用紅色標識）。該圖還顯示了包含航班中轉的表 FLIGHT 的摘要。在這個樣例數據中，您將會發現到柏林和法蘭克福市之間的線路，還會發現到法蘭克福市和柏林之間的連接。

表 FLIGHTS 包含以下的列：

• DEPARTURE - 出發城市
• ARRIVAL - 抵達城市
• PRICE - 連接費用

圖 2：雙向關係 - 航班中轉 – FLIGHTS

回頁首

遞歸通用表表達式 (RCTE)

根據 SQL 標準，評估分層或雙向數據的方法為遞歸通用表表達式 (RCTE)。

RCTE 可分為二個部分：

• 第一個部分，起始 SELECT 語句，預先定義了起始點（如組織圖中的管理人員或航班中轉裏的起航）。
• 第二部分代表迭代或遞歸。第二個SELECT 語句通過將父數據鏈接至子數據與公共表表達式 (CTE) 聯接。使用 UNION ALL 子句，可將當前的迭代 SELECT 語句的結果與初始 SELECT 語句的結果以及之前的迭代返回的所有結果合併在一起。

在 RTCE 與終止 SELECT 語句之間必須指定額外的子句，該子句充許預先定義返回結果的順序。

• SEARCH BREADTH FIRST 是默認順序，在返回下一層數據前會先返回同級的所有數據。
• 如果必須在返回下一個父數據的數據之前返回所有的子數據（所有級別上），則必須指定 SEARCH DEPTH FIRST。

為了避免雙向關係中出現無限循環，必須在 RCTE 和終止 SELECT 語句之間指定一個 CYCLE 子句。一旦檢測到循環，就會設置 CYCLE 子句中已定義好的適當標記，並停止分支的遞歸查詢過程。

注意：要檢測循環，在檢測循環時，系統只會檢查迭代 select 語句返回的結果。而不會檢查起始 select 的返回的結果。

下圖顯示了查詢遞歸數據的 RCTE 結構。

圖 3：遞歸通用表表達式 (RCTE) - 結構

在下一個示例中，將使用 RTCE 來返回 Bauer 經理的所有下屬（不只是直接下級中的下屬）。

RCTE 和終止 SELECT 語句都返回了 4 個列，分別是遞歸的層數、下屬的僱員編號、直屬經理的姓名以及直屬經理的僱員編號。

在起始 SELECT 語句中，級別固定為 1，在 WHERE 子句中，會硬編碼 Bauer 經理的姓名，以便預定義起始點。

在迭代 SELECT 語句中，通過比較表 RCTE 中的僱員編號（列 EMPLOYEE）與表 STAFF 中的管理人員編號（列 BOSS），將 RTCE STAFFLIST 與表 STAFF 聯合在一起。遞歸的層數的值都會增加 1，級別值都會增加 1。此外，應指定 SEARCH DEPTH FIRST 子句。臨時創建的字段 SORT 用於 ORDER BY 子句內，以便按深度優先順序獲得返回的結果。

圖 4：示例 RCTE – 確定所有下屬 

回頁首

分級查詢子句

正如 DB2 for i 技術更新 wiki 中所宣傳的那樣，我們在IBM i 7.1 版本中通過PTF (PTF SF99701 Version 9)支持了分級查詢語句。這個新的功能提供了分級查詢子句。這個新的功能提供了以遞歸方式讀取數據關係的另一種方法。與 RCTE 語法相比，分級查詢子句較更容易理解。分級查詢支持也符合於 Oracle 數據庫所用的非標準方法。

分級查詢子句不是作為公共表表達式來實現的，而是作為子句被指定為子 SELECT 語句的一部分，並且必須在指定 WHERE、GROUP BY 或 HAVING 子句之後指定。

分級查詢子句由兩部分組成：

• START WITH 謂詞定義了起始點，可以與 RCTE 中的起始 SELECT 語句相比較。可以通過將 SQL 謂詞（如 IN 或 LIKE）或邏輯運算符將多個條件語句結合來定義多個起始點。
• CONNECT BY 謂詞定義了父元素和子元素之間的連接關係。對於雙向關係，必須在 CONNECT BY 部分指定關鍵詞NOCYCLE 。如果沒有為雙向關係指定 NOCYCLE，就不會執行查詢，而是返回一個錯誤（與 RCTE 不同，RCTE 在沒有適當的 CYCLE 子句時會以無限循環結束 ）。

下列示例顯示使用一個 SELECT 語句和一個分級查詢子句來返回 Meier 經理（僱員編號為 101）的所有直接下屬。

圖 5：分級查詢子句 – 下一層

您可能會感到失望，因為這類查詢只需使用簡單 WHERE 子句（如 WHERE BOSS = 101）即可實現。

PRIOR 運算符

要獲取所有級別上的所有下屬，必須將 PRIOR 運算符添加到分級查詢子句的 CONNECT BY 部分。PRIOR 運算符必須為要解析的列加上前綴。例如，如果您想要確定某個特定經理（列 BOSS）的所有下屬（列 EMPLOYEE），則必須將 PRIOR 放在 EMPLOYEE 列的前面。PRIOR 運算符可放在比較運算符的左邊或右邊。

在下面的示例中，返回了 Bauer 經理（僱員編號為 202）的所有下屬（不只是下一級的下屬）。在第一個語句中，PRIOR 用於等號的左邊，而在第二個語句中，它被指定在另一邊。兩個語句返回的結果相同。

圖 6：分級查詢 – 運算符 PRIOR

如果聯接鍵是一個複合鍵，那麼必須使用 PRIOR 運算符為每個鍵所在的列加上前綴。

CONNECT BY     PRIOR a.Department = b.Department
           AND PRIOR Employee     = Boss

在將先前查詢的結果與表 STAFF 中的數據進行比較時，結果是按照深度優先順序返回的，這意味着會先返回所有的子行，然後才能返回下一個子行（與 RCTE 相反，RCTE 在默認情況下按寬度優先順序返回數據，比如逐級返回）。

Order Siblings By

即使先前查詢的數據是按照搜索深度優先順序返回的，但在同一個級別中，比如在編號為 405、404 和 403 的僱員中，或者在姓名 Hund、Schmidt 和 Jägerthere 中，沒有特定的順序。

添加一個 ORDER SIBLINGS BY 子句，該子句只能與分級查詢子句組合使用，並且將按預先定義的順序返回同級查詢結果。類似於在常規的 ORDER BY 子句中，可以按升序或降序指定多個列。

在下一個示例中，會修改以前的查詢，添加一個 ORDER BY SIBLINGS 子句，從按照 NAME 排序的結果中獲取同級查詢結果。以前是先返回 Becker（僱員編號為 303），然在修改後的查詢中，先返回 Ackermann（僱員編號為 304），其下屬 Hund、Jäger 和 Schmidt 也是按字母順序排列的。

圖 7：分級查詢 – ORDER SIBLINGS BY

CONNECT_BY_ROOT 運算符

在先前的示例中，第一個列（如總經理）被指定為常數值。硬編碼或更糟的重複硬編碼值（比如 Select 語句和 Start with 子句中的 BOSS）絕不是一個好點子。只要在硬編碼定義多個起始點（比如多個經理），工作區就無法再運行。

要避開硬編碼和重複的硬編碼值，可以使用 CONNECT_BY_ROOT 運算符，它總是返回其參數值，就像在初始步驟中那樣。

在下列的示例中，必須確定僱員編號為 201 (Huber) 和 202 (Bauer) 的經理的所有下屬或者僱員編號大於 303 的所有經理（只有編號為 304 的 Ackermann）的所有下屬。

在此示例中，使用了 CONNECT_BY_ROOT 運算符來返回經理的相應下屬的僱員編號。要將經理的姓名包含在內，則需要添加一個子 SELECT 語句，在該語句中，通過將經理的僱員編號（列 BOSS）與下屬僱員編號（列 EMPLOYEE）聯繫起來確定表 STAFF 中的下屬名稱。

圖 8：分級查詢 – CONNECT_BY_ROOT 運算符

偽列

在分級查詢中，提供了幾個偽列。偽列是具有預先定義的名稱的標識符，其中包含關於分級查詢的信息；它的用途類似於表中的任何列。

• LEVEL – 偽列 LEVEL 返回一個整數值，表示所生成行的分層結構中的遞歸步驟。

• 對於通過 start with 子句生成的所有行，LEVEL 被設置為 1。
• 對於通過 CONNECT BY 子句的迭代生成的所有行，LEVEL 增加 1。

可在 ORDER BY 子句中使用偽列 LEVEL 按照搜索寬度優先順序返回結果集。

• CONNECT_BY_ISCYCLE 返回一個設置為 0（未檢測到循環）或 1（檢測到循環）的小整數值。

偽列不僅可以用來檢測循環，還可以使用偽列從結果中刪除循環。

• CONNECT_BY_ISLEAF 返回一個設置為 0 或 1 的小整數值。

如果某個行是分層結構中的葉子節點，則返回的值為 1，否則返回值為 0 。在分層關係中，對於駐留在最低級別的所有元素，CONNECT_BY_ISLEAF 值都設置為 1。在雙向關係中，CONNECT_BY_ISLEAF 返回與 CONNECT_BY_ISCYCLE 相同的值。

在下列示例中，在選擇列表中指定偽列 LEVEL。還可以使用偽列值按照寬度優先順序對查詢結果進行排序。

圖 9：分級查詢 – 偽列 LEVEL

下面的示例中將顯示使用 LEVEL 偽列的另一種方法。可在字符串中使用偽列 LEVEL，使分層結構變得可視。基於當前的級別值，會根據與當前級別有關的空格數對名稱進行縮進。

圖 10：分級查詢 – 使分層結構變得可視

標量函數 SYS_CONNECT_BY_PATH

標量函數 SYS_CONNECT_BY_PATH 只能與分級查詢組合使用。它構建了一行字符串，該字符串包含從 root 到當前行的所有元素。函數的結果會以 CLOB（字符型大型對象）數據類型的形式返回，可容納 1 MB 的數據。

函數 SYS_CONNECT_BY_PATH 需要兩個字符串表達式。

• 第一個表達式必須為串聯的行值。
• 第二個表達式是為分隔元素而添加的運算符。

在下列的示例中，顯示了法蘭克福市和柏林之間的所有航班中轉。

由於是雙向數據，所以必須在 START WITH … CONNECT BY 子句中指定關鍵字 NOCYCLE。可通過在 WHERE 子句中指定偽列 CONNECT_BY_ISCYCLE 來排除導致循環的中轉。

基於偽列 LEVEL 最大返回兩個連接 (LEVEL <= 3)。

因為沒有檢查循環數據的起始點，並且不能再次連接到 “法蘭克福市” ，所以將從 CONNECT BY 子句中刪除該中轉 (Arrival <> 'Frankfurt')。

可以使用 CONNECT_BY_ROOT 確定出發點（總是法蘭克福市），並將它與 ARRIVAL SYS_CONNECT_BY_PATH 函數所生成字符串串聯在一起，該函數將所有迭代中用破折號 (' – ') 分隔的所有 ARRIVAL 串聯起來。

圖11：函數 SYS_CONNECT_BY_PATH

回頁首

串聯數字值

不幸的是，SYS_CONNECT_BY_PATH 只能用於串聯字符值。有時，您想要串聯數字值，比如，計算航班中轉的總成本。

不好的消息是，沒有一個特殊的函數或運算符可以直接與分級查詢子句結合使用來實現此操作。好消息是，有兩種方法可以實現此目的，例如，要計算總成本，請執行以下操作：

• 第一個選項是使用一個 RCTE
• 第二個選項是使用函數 SYS_CONNECT_BY_PATH 構建一個字符串，將所有成本以加號 (+) 分隔的字符值形式連接在一起。並編寫一個簡單使用動態 SQL 的 SQL 用户定義函數 (UDF)，轉換字符串中傳入的公式並計算值。

使用 UDF 計算以字符串形式傳遞的公式

SYS_CONNECT_BY_PATH 函數只可用來連接字符串（例如，不能將用它來計算總數）。

在 SQL 編程中，可以使用 SQL 命令 PREPARE 將含有任何（數學）公式的字符串轉換成可執行的 SQL 語句。可以使用 SQL 命令 EXECUTE 來執行這個動態預備語句。

下列的 SQL 腳本包含用來創建 Calculate user-defined function (UDF) 的源代碼。UDF 需要一個字符型大型對象 (CLOB)，它包含一個作為輸入參數的數學公式（如 3+5*17）。這個傳入的參數值會嵌套在另一個字符串中，以獲得已計算出的和返回的公式的結果。

VALUES 語句提供了一個無需訪問表或視圖即可執行 SQL 語句的方法。VALUES … INTO 語句允許執行 SQL 表達式，並將結果返回至變量。語句字符串中的 ?（問號）是一個參數標識，代表用來返回結果的變量。

可以使用使用 SQL PREPARE 語句將完整的字符串（如嵌套在 VALUES … INTO、VALUES 組合中的導入公式）轉換成可執行的 SQL 語句。用來檢索結果的變量 RtnVal 與 USING 子句中的參數標記有關聯。

如果發生錯誤，比如公式中包含無效的數據，就會激活後續處理程序，並返回一個負的默認值 (-999999999999.99)。

圖 12：計算字符串 UDF - 示例

一旦生成 UDF，就可以將它與函數 SYS_CONNECT_BY_PATH 結合使用來計算總數。

在下列的示例中，列 LISTPRICES 顯示了包含用加號 (+) 分隔的連接在一起的成本的字符串。要在 Cost 列中顯示結果，則必須通過 Calculate UDF 來傳遞並處理此串聯字符。

圖13：UDF CALCULATE 和函數 SYS_CONNECT_BY_PATH

回頁首

結束語

現在您應該能夠使用下列語句創建具有分層或雙向關係的所有類型的遞歸查詢：

• 分級查詢子句 (START WITH … CONNECT BY)

• 運算符 PRIOR、NOCYCLE 和 CONNECT_BY_ROOT
• 特殊的 ORDER SIBLINGS BY 子句
• 函數 SYS_CONNECT_BY_PATH
• 偽列 LEVEL、CONNECT_BY_ISCYCLE 和 CONNECT_BY_ISLEAF

• 遞歸通用表表達式 (RCTE)

此外，您應該能夠將 UDF 與函數 SYS_CONNECT_BY_PATH 結合使用來計算遞歸關係中的總數。

目前為止，我們已經對分級查詢子句進行了一些有趣的試驗。

回頁首

參考資料

IBM i - DB2 for i SQL Reference - 7.1：第五章 – 查詢

IBM developerWorks DB2 for i - 論壇

IBM developerWorks - IBM i 技術更新

關於作者

Birgitta Hauser