萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 階層查詢完整指南:CONNECT BY、PRIOR、CONNECT_BY_ROOT 與 NOCYCLE 實戰用法

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;        -- 同層級按姓名排序

處理迴路資料:NOCYCLE

若資料品質不佳存在循環參考(A→B→A),加上 NOCYCLE 避免 ORA-01436 錯誤,並可用 CONNECT_BY_ISCYCLE 標記迴路節點。

SELECT ... FROM emp
START WITH ...
CONNECT BY NOCYCLE PRIOR empno = mgr;

效能與限制

  • 階層查詢本質為遞迴掃描,大量資料(>10萬層)易導致 TEMP 表空間暴增。
  • 11gR2+ 推薦使用 遞迴 WITH 子句 (WITH RECURSIVE),符合 ANSI SQL 標準、支援更豐富的終止條件、平行查詢友善。
  • 避免在 CONNECT BY 條件中使用非決定性函數或複雜運算。

遞迴 WITH 寫法對照 (11gR2+)

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,能寫出更清晰、可維護、高效能的查詢。

copyright © 萬盛學電腦網 all rights reserved