Oracle 10g 強化階層查詢功能,新增 CONNECT_BY_ROOT 操作符與 NOCYCLE 關鍵字,配合經典的 START WITH ... CONNECT BY PRIOR,能優雅解決組織架構、BOM 展開、遞迴關係等樹狀結構問題。
SELECT ... FROM table_name START WITH condition -- 根節點條件 CONNECT BY [NOCYCLE] condition; -- 父子關係,NOCYCLE 允許迴路存在時仍回傳結果
| 操作符/偽欄 | 用途 | 範例 |
|---|---|---|
PRIOR expr = expr | 指定父→子方向 | PRIOR emp_id = mgr_id (emp_id 為父) |
expr = PRIOR expr | 指定子→父方向 | emp_id = PRIOR mgr_id |
CONNECT_BY_ROOT col | 取得根節點的欄位值 | CONNECT_BY_ROOT dept_name AS root_dept |
LEVEL | 目前層級 (根=1) | 縮排顯示、限制深度 |
SYS_CONNECT_BY_PATH(col, '/') | 從根到目前節點的路徑 | SYS_CONNECT_BY_PATH(ename, '/') |
CONNECT_BY_ISLEAF | 是否為葉節點 (1/0) | 過濾明細層級 |
SELECT
LPAD(' ', 2*(LEVEL-1)) || ename AS org_chart,
empno, mgr,
CONNECT_BY_ROOT ename AS top_manager,
SYS_CONNECT_BY_PATH(ename, ' > ') AS path,
CONNECT_BY_ISLEAF AS is_leaf
FROM emp
START WITH mgr IS NULL -- 老闆沒有主管
CONNECT BY PRIOR empno = mgr -- empno 是父, mgr 是子
ORDER SIBLINGS BY ename; -- 同層級按姓名排序
若資料品質不佳存在循環參考(A→B→A),加上 NOCYCLE 避免 ORA-01436 錯誤,並可用 CONNECT_BY_ISCYCLE 標記迴路節點。
SELECT ... FROM emp START WITH ... CONNECT BY NOCYCLE PRIOR empno = mgr;
TEMP 表空間暴增。WITH RECURSIVE),符合 ANSI SQL 標準、支援更豐富的終止條件、平行查詢友善。CONNECT BY 條件中使用非決定性函數或複雜運算。WITH RECURSIVE org_tree (empno, ename, mgr, lvl, path) AS ( SELECT empno, ename, mgr, 1, ename FROM emp WHERE mgr IS NULL UNION ALL SELECT e.empno, e.ename, e.mgr, ot.lvl+1, ot.path || ' > ' || e.ename FROM emp e JOIN org_tree ot ON e.mgr = ot.empno WHERE ot.lvl < 20 -- 防無限遞迴 ) SELECT * FROM org_tree ORDER BY path;
階層查詢是關聯式資料庫處理樹狀資料的利器,善用 CONNECT_BY_ROOT、SYS_CONNECT_BY_PATH、NOCYCLE 等進階功能,並適時遷移至標準遞迴 CTE,能寫出更清晰、可維護、高效能的查詢。