階層式查詢
階層式查詢是使用 CONNECT BY 子句從關聯式資料擷取階層 (例如資料清單) 的遞迴查詢形式。
啟用
db2set DB2_COMPATIBILITY_VECTOR=08
db2stop
db2start然後，您可以使用 CONNECT BY 語法，包括虛擬直欄、單元運算子及 SYS_CONNECT_BY_PATH 純量函數。
階層式查詢包含 CONNECT BY 子句，其中定義母元素與子元素之間的結合條件。 Connect-by 遞迴對種子 (START WITH 子句) 和遞迴步驟 (CONNECT BY 子句) 使用相同的子查詢。 此組合提供代表遞迴的簡要方法，例如資料帳單、報告至鏈結或電子郵件執行緒。
如果發生循環，則 Connect-by 遞迴會傳回錯誤。 當橫列直接或間接產生本身時，即會發生 循環 。 透過使用選用的 CONNECT BY NOCYCLE 子句，您可以引導遞迴忽略重複的列，從而避免循環及錯誤。 階層式查詢或連接遞迴與Db2 遞迴不同。 如需差異的相關資訊，請參閱 Port CONNECT BY to DB2®。
hierarchical-query-clause
包含 hierarchical-query-clause 的次選取稱為階層式查詢。
- start-with-clause
- START WITH 表示遞迴的種子。 start-with-clause 指定階層式查詢的中間結果表格 H1 。 表 H1 由 search-condition 為 true 的那些 R 列組成。 如果您未指定 start-with-clause，則 H1 是整個中間結果表格 R。 start-with-clause 內 search-condition 的規則與 WHERE 子句內的規則相同。
- connect-by-clause
- CONNECT BY 說明遞迴步驟。 connect-by-clause 會使用搜尋條件結合 Hn 與 R ，從 Hn 產生中間結果表格 Hn + 1 。 如果您指定 NOCYCLE 關鍵字，則中間結果表格 Hn + 1中不會包括重複列。 未傳回錯誤。 connect-by-clause 內 search-condition 的規則與 WHERE 子句內的規則相同，但無法指定 OLAP 規格 (SQLSTATE 42903)。
建立第一個中間結果表格 H1 之後，會產生後續中間結果表格 H2、H3等。 後續建立的中間結果表格是透過使用 connect-by-clause 作為結合條件來結合 Hn 與表格 R ，以產生 Hn + 1。 R 是次選取的 FROM 子句以及 WHERE 子句中任何結合述詞的結果。 當 Hn + 1 產生空的結果表格時，處理程序會停止。 hierarchical-query-clause 的結果表格 H 是結果，就像每個中間結果表格都套用 UNION ALL 一樣。
CONNECT BY MGRID = PRIOR EMPID規則
- 如果中間結果表格 Hn + 1 將從 R 傳回階層式路徑的列，而該階層式路徑與已在該階層式路徑中的 R 中的列相同，則會傳回錯誤 (SQLSTATE 560CO)。
- 如果指定 NOCYCLE 關鍵字，則不會傳回錯誤，但中間結果表格 Hn + 1中不會包含重複列。
- 最多支援 64 個遞迴層次 (SQLSTATE 54066)。
- 除非您使用選取清單中的明確 ORDER BY 子句、GROUP BY 或 HAVING 子句或 DISTINCT 關鍵字來毀損中間結果集，否則作為階層式查詢的次選取會以局部順序傳回中間結果集。 偏序會傳回列，以便在特定階層的 Hn + 1 中產生的列緊跟在產生它們的 Hn 中的列之後。 您可以使用 ORDER SIBLINGS BY 子句，在相同母項所產生的一組列內施行順序。
- 具體化查詢表格不支援階層式查詢 (SQLSTATE 428EC)。
- 您無法搭配使用 CONNECT BY 子句與 XML 函數或 XQuery (SQLSTATE 428H4)。
- 您無法在下列位置指定序列的 NEXT VALUE 表示式 (SQLSTATE 428F9):
- CONNECT_BY_ROOT 運算子或 SYS_CONNECT_BY_PATH 函數的參數清單
- START WITH 及 CONNECT BY 子句
注意事項
- 階層式查詢支援會以下列方式影響次選取:
- 次選取的子句按下列順序處理:
- FROM 子句
- hierarchical-query-clause
- WHERE 子句
- GROUP BY 子句
- HAVING 子句
- SELECT 子句
- ORDER BY 子句
- FETCH FIRST 子句
- 特殊規則適用於 WHERE 子句中述詞的處理順序。 search-condition 會納入述詞及其 AND 條件 (conjunction)。 如果述詞是隱含結合述詞 (亦即，它參照 FROM 子句中的多個表格) ，則在套用 hierarchical-query-clause 之前會先套用述詞。 任何最多參照 FROM 子句中一個表格的述詞，都會套用至 hierarchical-query-clause的中間結果表格。
如果您撰寫包含結合的階層式查詢，請使用具有 ON 子句的明確結合表格，以避免混淆 WHERE 子句述詞的應用程式。
- 您可以指定 ORDER SIBLINGS BY 子句。 此子句指定排序僅套用至階層內的同層級。
- 次選取的子句按下列順序處理:
- 虛擬直欄 是在特定環境定義中具有意義且與直欄及變數共用相同名稱空間的完整或不完整 ID。 如果不完整 ID 無法識別直欄或變數，則會檢查 ID 以查看它是否識別虛擬直欄。
LEVEL 是用於階層式查詢的虛擬直欄。 LEVEL 虛擬直欄會在產生列的階層中傳回遞迴步驟。 START WITH 子句所產生的所有列都會傳回值 1。 套用 CONNECT BY 子句的第一次反覆運算所產生的列會傳回 2 ，依此類推。 直欄的資料類型是 INTEGER NOT NULL。
您必須在階層式查詢的環境定義中指定 LEVEL。 您無法在 START WITH 子句中指定 LEVEL 作為 CONNECT_BY_ROOT 運算子的引數，或作為 SYS_CONNECT_BY_PATH 函數 (SQLSTATE 428H4) 的引數。
- 支援階層式查詢的單元運算子為 CONNECT_BY_ROOT 及 PRIOR。
- 支援階層式查詢的函數是 SYS_CONNECT_BY_PATH 純量函數。
範例
- 下列報告至鏈結範例說明依遞迴來連接。 範例基於名為 MY_EMP 的表格，該表格建立並移入資料如下:
CREATE TABLE MY_EMP( EMPID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(10), SALARY DECIMAL(9, 2), MGRID INTEGER); INSERT INTO MY_EMP VALUES ( 1, 'Jones', 30000, 10); INSERT INTO MY_EMP VALUES ( 2, 'Hall', 35000, 10); INSERT INTO MY_EMP VALUES ( 3, 'Kim', 40000, 10); INSERT INTO MY_EMP VALUES ( 4, 'Lindsay', 38000, 10); INSERT INTO MY_EMP VALUES ( 5, 'McKeough', 42000, 11); INSERT INTO MY_EMP VALUES ( 6, 'Barnes', 41000, 11); INSERT INTO MY_EMP VALUES ( 7, 'O''Neil', 36000, 12); INSERT INTO MY_EMP VALUES ( 8, 'Smith', 34000, 12); INSERT INTO MY_EMP VALUES ( 9, 'Shoeman', 33000, 12); INSERT INTO MY_EMP VALUES (10, 'Monroe', 50000, 15); INSERT INTO MY_EMP VALUES (11, 'Zander', 52000, 16); INSERT INTO MY_EMP VALUES (12, 'Henry', 51000, 16); INSERT INTO MY_EMP VALUES (13, 'Aaron', 54000, 15); INSERT INTO MY_EMP VALUES (14, 'Scott', 53000, 16); INSERT INTO MY_EMP VALUES (15, 'Mills', 70000, 17); INSERT INTO MY_EMP VALUES (16, 'Goyal', 80000, 17); INSERT INTO MY_EMP VALUES (17, 'Urbassek', 95000, NULL);下列查詢會傳回所有為 Goyal 工作的員工，以及一些其他資訊，例如報告至鏈結:1 SELECT NAME, 2 LEVEL, 3 SALARY, 4 CONNECT_BY_ROOT NAME AS ROOT, 5 SUBSTR(SYS_CONNECT_BY_PATH(NAME, ':'), 1, 25) AS CHAIN 6 FROM MY_EMP 7 START WITH NAME = 'Goyal' 8 CONNECT BY PRIOR EMPID = MGRID 9 ORDER SIBLINGS BY SALARY;NAME LEVEL SALARY ROOT CHAIN ---------- ----------- ----------- ----- --------------- Goyal 1 80000.00 Goyal :Goyal Henry 2 51000.00 Goyal :Goyal:Henry Shoeman 3 33000.00 Goyal :Goyal:Henry:Shoeman Smith 3 34000.00 Goyal :Goyal:Henry:Smith O'Neil 3 36000.00 Goyal :Goyal:Henry:O'Neil Zander 2 52000.00 Goyal :Goyal:Zander Barnes 3 41000.00 Goyal :Goyal:Zander:Barnes McKeough 3 42000.00 Goyal :Goyal:Zander:McKeough Scott 2 53000.00 Goyal :Goyal:Scott第 7 行和第 8 行包含遞迴的核心: 選用的 START WITH 子句說明要在來源表格上用來植入遞迴的 WHERE 子句。 在此情況下，只會選取員工 Goyal 的列。 如果省略 START WITH 子句，則會使用整個來源表格來設定遞迴種子。 CONNECT BY 子句說明在給定現存橫列的情況下，如何找到下一組橫列。 單元運算子 PRIOR 用來區分前一個步驟中的值與現行步驟中的值。 PRIOR 將 EMPID 識別為前一個遞迴步驟的員工 ID ，並將 MGRID 識別為源自現行遞迴步驟。
第 2 行中的 LEVEL 虛擬直欄指出遞迴的現行層次。
CONNECT_BY_ROOT 是一個單元運算子，一律會傳回其引數在第一個遞迴步驟期間的值; 亦即，明確或隱含 START WITH 子句所傳回的值。
SYS_CONNECT_BY_PATH () 是二進位函數，它會在第一個引數前面附加第二個引數，然後將結果附加至它在前一個遞迴步驟中產生的值。 引數必須是字元類型。
除非明確置換，否則 connect-by 遞迴會以局部順序傳回結果集; 亦即，遞迴步驟所產生的列一律遵循產生它們的列。 相同遞迴層次的同層級沒有特定順序。 第 9 行中的 ORDER SIBLINGS BY 子句定義這些同層級的訂單，其會進一步精簡局部訂單，可能變成總訂單。
- 傳回 DEPARTMENT 表格的組織結構。 使用部門層次來視覺化階層。
查詢會傳回:SELECT LEVEL, CAST(SPACE((LEVEL - 1) * 4) || '/' || DEPTNAME AS VARCHAR(40)) AS DEPTNAME FROM DEPARTMENT START WITH DEPTNO = 'A00' CONNECT BY NOCYCLE PRIOR DEPTNO = ADMRDEPTLEVEL DEPTNAME ----------- ---------------------------------------- 1 /SPIFFY COMPUTER SERVICE DIV. 2 /PLANNING 2 /INFORMATION CENTER 2 /DEVELOPMENT CENTER 3 /MANUFACTURING SYSTEMS 3 /ADMINISTRATION SYSTEMS 2 /SUPPORT SERVICES 3 /OPERATIONS 3 /SOFTWARE SUPPORT 3 /BRANCH OFFICE F2 3 /BRANCH OFFICE G2 3 /BRANCH OFFICE H2 3 /BRANCH OFFICE I2 3 /BRANCH OFFICE J2