Oracle 的 CONNECT BY 子句是處理樹狀結構資料的利器，10g 版本引入三大增強特性——CONNECT_BY_ROOT、CONNECT_BY_ISLEAF、CONNECT_BY_ISCYCLE——徹底解決了舊版本難以追蹤根節點、識別葉節點、處理循環資料的痛點。
目錄表 T_TONEDIRLIB：
DIRINDEX：節點 IDFATHERINDEX：父節點 ID（0 為根節點）DIRNAME：節點名稱資料包含三棵子樹（根節點 1、2、3），多層級巢狀結構。
需求：統計每個根分類（1、2、3）下包含多少子分類（含所有層級）。舊版本需複雜自連接或 PL/SQL 迴圈。
SELECT CONNECT_BY_ROOT dirindex AS root_index,
fatherindex,
RPAD(' ', 2*(LEVEL-1)) || dirname AS dir_tree
FROM t_tonedirlib
START WITH fatherindex = 0
CONNECT BY fatherindex = PRIOR dirindex;ROOT_INDEX FATHERINDEX DIR_TREE
---------- ----------- ------------------------------
1 0 中文經典
1 1 kkkkkkk
1 52 222
1 52 sixx
... ... ...
2 0 熱門流行
2 2 有獎活動
3 0 老歌回放
3 3 老電影所有節點的 ROOT_INDEX 皆為其所屬根節點 ID，後續統計極簡單：
SELECT root_index, COUNT(*) AS sub_count
FROM (
SELECT CONNECT_BY_ROOT dirindex AS root_index
FROM t_tonedirlib
START WITH fatherindex = 0
CONNECT BY fatherindex = PRIOR dirindex
)
GROUP BY root_index;
ROOT_INDEX SUB_COUNT
---------- ---------
1 19
2 4
3 3傳統做法需在表中新增 IS_LEAF 欄位並由應用程式維護，極易因邏輯疏漏導致資料不一致。
SELECT CONNECT_BY_ISLEAF AS is_leaf,
dirindex, fatherindex,
RPAD(' ', 2*(LEVEL-1)) || dirname AS dir_tree
FROM t_tonedirlib
START WITH fatherindex = 0
CONNECT BY fatherindex = PRIOR dirindex;IS_LEAF DIRINDEX FATHERINDEX DIR_TREE
------- -------- ----------- ------------------------------
0 1 0 中文經典
0 52 1 kkkkkkk
1 70 52 222
1 58 52 sixx
1 59 52 seven
1 69 52 uiouoooo
1 55 52 four
1 7 1 流行風雲
0 8 1 影視金曲
1 1111 8 aaa
1 1112 8 bbb
1 1113 8 ccc
... ... ... ...0 = 非葉節點（有子節點）1 = 葉節點（無子節點）應用場景：樹狀 UI 僅展開非葉節點、葉節點掛載業務資料、遞迴終止條件判斷。
髒資料導致循環引用（A 父是 B，B 父是 A），舊版本拋出 ORA-01436: CONNECT BY loop in user data 直接報錯中斷。
INSERT INTO t_tonedirlib (dirindex, fatherindex, dirname) VALUES (666, 667, '123');
INSERT INTO t_tonedirlib (dirindex, fatherindex, dirname) VALUES (667, 666, '456');SELECT dirindex, fatherindex, RPAD(' ', 2*(LEVEL-1)) || dirname
FROM t_tonedirlib
START WITH fatherindex = 666
CONNECT BY fatherindex = PRIOR dirindex;
-- ORA-01436: CONNECT BY loop in user dataSELECT CONNECT_BY_ISCYCLE AS is_cycle,
dirindex, fatherindex,
RPAD(' ', 2*(LEVEL-1)) || dirname
FROM t_tonedirlib
START WITH fatherindex = 666
CONNECT BY NOCYCLE fatherindex = PRIOR dirindex;IS_CYCLE DIRINDEX FATHERINDEX DIR_TREE
-------- -------- ----------- -------------
0 667 666 456
1 666 667 123 <-- 此節點導致循環NOCYCLE：忽略循環繼續遍歷，不報錯CONNECT_BY_ISCYCLE=1：標記導致循環的節點CONNECT_BY_ISCYCLE=0：正常節點| 特性 | 回傳值 | 用途 | 配合語法 |
|---|---|---|---|
| CONNECT_BY_ROOT | 根節點的運算式值 | 跨層級歸屬統計、根屬性繼承 | 標準 CONNECT BY |
| CONNECT_BY_ISLEAF | 0/1 | 葉節點識別、UI 渲染控制、遞迴終止 | 標準 CONNECT BY |
| CONNECT_BY_ISCYCLE | 0/1 | 循環資料偵測、髒資料清理、容錯查詢 | 必須搭配 NOCYCLE |
CONNECT_BY_ROOT dirname AS root_name 同時取得根名稱SYS_CONNECT_BY_PATH(dirname, ' > ') 產生完整路徑字串WHERE LEVEL <= 3 限制展開深度ORDER SIBLINGS BY dirname 同層級排序INDEX ON (fatherindex, dirindex)START WITH 使用非索引欄位NOCYCLE 會增加循環檢測開銷，僅在必要時啟用RECURSIVE WITH (ANSI SQL 標準遞迴 CTE) 替代結語：10g 的三大 CONNECT BY 增強，將階層式查詢從「能用」提升到「好用、強健」，是處理組織架構、產品分類、BOM 結構、帳號體系等樹狀資料的必備技能。
