Oracle 10g 增強 CONNECT BY：避免循環 (NOCYCLE) 與識別葉節點 (CONNECT_BY_ISLEAF)

Oracle 10g 對階層查詢 CONNECT BY 進行兩大增強：NOCYCLE 避免循環資料報錯，CONNECT_BY_ISCYCLE 識別循環節點，CONNECT_BY_ISLEAF 識別葉節點。

問題背景

樹狀表結構：dirindex (節點 ID)、fatherindex (父節點 ID)。若資料含循環 (A 父 B、B 父 A)，傳統 CONNECT BY 拋出 ORA-01436: CONNECT BY loop in user data。

增強 1：NOCYCLE + CONNECT_BY_ISCYCLE

-- 測試資料
INSERT INTO t_tonedirlib VALUES (666, 667, '123', 5);
INSERT INTO t_tonedirlib VALUES (667, 666, '456', 5);

-- 10g 查詢：加入 NOCYCLE，輸出 ISCYCLE 標記
SELECT CONNECT_BY_ISCYCLE, dirindex, fatherindex,
       RPAD(' ', 2*(LEVEL-1)) || dirname
FROM t_tonedirlib
START WITH fatherindex = 666
CONNECT BY NOCYCLE fatherindex = PRIOR dirindex;

結果

CONNECT_BY_ISCYCLE  DIRINDEX  FATHERINDEX  DIRNAME
0                   667       666          456
1                   666       667          123  <-- 標記為循環節點

• CONNECT_BY_ISCYCLE：1 表示該節點導致循環，0 正常。
• NOCYCLE 讓查詢不中斷，繼續遍歷非循環路徑。

增強 2：CONNECT_BY_ISLEAF 識別葉節點

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

結果節錄

CONNECT_BY_ISLEAF  DIRINDEX  FATHERINDEX  DIRNAME
0                  1         0            中文經典
0                  52        1            kkkkkkk
1                  70        52           222          <-- 葉節點
1                  58        52           sixx         <-- 葉節點
1                  59        52           seven        <-- 葉節點
1                  55        52           uiouoooo     <-- 葉節點
1                  52        1            四大類目
...

• CONNECT_BY_ISLEAF：1 = 葉節點 (在當前查詢結果集中無子節點)，0 = 非葉節點。
• 免去在表中額外維護 is_leaf 欄位與觸發器邏輯。

實務價值

• 資料品質檢核：定期掃描 CONNECT_BY_ISCYCLE = 1 發現髒資料。
• 動態選單/樹狀 UI：直接用 CONNECT_BY_ISLEAF 決定是否顯示展開圖示。
• 遞迴 CTE (11g+ WITH RECURSIVE) 同樣支援 CYCLE ... SET ... 語法，功能更強大。

10g 的 NOCYCLE、CONNECT_BY_ISCYCLE、CONNECT_BY_ISLEAF 讓階層查詢更健壯、更智慧，是處理樹狀資料的利器。