Oracle 10g 增強 CONNECT BY：循環偵測與葉節點識別

背景

Oracle 早期即支援 CONNECT BY 遞迴查詢樹狀結構。但資料若含循環 (如 A 父節點為 B、B 父節點為 A) 會拋出 ORA-01436: CONNECT BY loop in user data。10g 引入 NOCYCLE 與兩個偽欄位徹底解決此痛點。

範例資料

INSERT INTO t_tonedirlib (dirindex, fatherindex, dirname, status) VALUES (666, 667, '123', 5);
INSERT INTO t_tonedirlib (dirindex, fatherindex, dirname, status) VALUES (667, 666, '456', 5);
-- 666 與 667 互為父子，形成循環

1. CONNECT_BY_ISCYCLE — 識別循環節點

SELECT CONNECT_BY_ISCYCLE, dirindex, fatherindex,
       RPAD(' ', 2*(LEVEL-1)) || dirname AS tree_view
FROM   t_tonedirlib
START WITH fatherindex = 666
CONNECT BY NOCYCLE fatherindex = PRIOR dirindex;

結果：

CONNECT_BY_ISCYCLE  DIRINDEX  FATHERINDEX  TREE_VIEW
------------------  --------  -----------  ---------
0                   667       666          456
1                   666       667            123   <-- 此節點導致循環

• CONNECT_BY_ISCYCLE = 1 表示該行會導致循環 (即其子節點包含祖先)。
• NOCYCLE 關鍵字讓查詢不報錯、繼續遍歷，最多走訪每節點一次。

2. CONNECT_BY_ISLEAF — 識別葉節點

SELECT CONNECT_BY_ISLEAF, dirindex, fatherindex,
       RPAD(' ', 2*(LEVEL-1)) || dirname AS tree_view
FROM   t_tonedirlib
START WITH fatherindex = 0
CONNECT BY NOCYCLE fatherindex = PRIOR dirindex;

• CONNECT_BY_ISLEAF = 1 表示該節點無子節點 (葉節點)。
• 適用於只需展開到最底層、或統計各分支深度的場景。

3. 結合應用：安全的樹狀報表

SELECT CASE WHEN CONNECT_BY_ISCYCLE = 1 THEN '[CYCLE] ' ELSE '' END ||
       RPAD(' ', 2*(LEVEL-1)) || dirname AS safe_tree,
       dirindex, fatherindex,
       CONNECT_BY_ISLEAF AS is_leaf,
       LEVEL AS depth
FROM   t_tonedirlib
START WITH fatherindex = 0
CONNECT BY NOCYCLE fatherindex = PRIOR dirindex
ORDER SIBLINGS BY dirname;

輸出同時標記循環風險與葉節點，配合 ORDER SIBLINGS BY 保證同層排序，極適合前端樹狀元件直接渲染。

版本相容性提醒

• 10gR1 (10.1) 起支援。
• 9i 及舊版無此功能，需自寫 PL/SQL 遞迴或用 SYS_CONNECT_BY_PATH 輔助偵測循環。

結語

NOCYCLE + CONNECT_BY_ISCYCLE + CONNECT_BY_ISLEAF 讓 Oracle 10g 成為處理髒亂樹狀資料的利器。資料品質不完美時，不再需要先清洗再查詢，直接在 SQL 層面安全、完整地輸出結果。