Oracle 樹狀遞歸查詢完全指南：CONNECT BY 實戰與層級控制

樹狀結構 (組織架構、選單目錄、BOM 物料清單) 在資料庫中通常以「遞歸外鍵」 (父子欄位自關聯) 方式儲存。Oracle 原生的 START WITH ... CONNECT BY PRIOR 提供宣告式遞歸查詢能力，無需撰寫複雜的 PL/SQL 迴圈或 CTE。本文系統講解語法結構、根節點定義、連接方向控制、層級偽欄位應用，並以部門表為例示範自頂向下與自底向上兩種遍歷模式。

語法架構解析

SELECT *
FROM   tablename
START  WITH cond1          -- 根節點條件
CONNECT BY cond2           -- 父子連接條件 (含 PRIOR)
WHERE  cond3;              -- 結果過濾條件 (選用)

三大條件角色

• COND1 (START WITH)：定義遞歸起點。可指定單一根節點 (deptid=76) 或多根節點 (paredeptid=0 代表所有頂層部門)。
• COND2 (CONNECT BY PRIOR)：核心邏輯。PRIOR 修飾「父代」欄位。
  • CONNECT BY PRIOR parent_id = child_id：自頂向下 (父→子)
  • CONNECT BY PRIOR child_id = parent_id：自底向上 (子→父)
• COND3 (WHERE)：作用於最終結果集，不影響遞歸展開路徑。

實戰範例：部門表 (DEPT)

DEPTID       NUMBER   -- 部門 ID
PAREDEPTID   NUMBER   -- 父部門 ID (0 表頂層)
NAME         CHAR(40) -- 部門名稱

場景 A：自底向上追溯 (子節點找所有祖先)

從部門 76 向上找到根：

SELECT *
FROM   persons.dept
START  WITH deptid = 76
CONNECT BY PRIOR paredeptid = deptid;

PRIOR paredeptid 代表「上一筆記錄的父部門 ID」等於「本筆記錄的部門 ID」，即向上一層層尋找父節點。

場景 B：自頂向下展開 (根節點找所有子孫)

從所有頂層部門 (父部門=0) 向下遞歸：

SELECT *
FROM   persons.dept
START  WITH paredeptid = 0
CONNECT BY PRIOR deptid = paredeptid;

此處 PRIOR deptid (上一層的 ID) = paredeptid (本層的父 ID)，形成父→子遞歸。

場景 C：顯示層級深度

使用 LEVEL 偽欄位 (根節點 LEVEL=1)：

SELECT a.*, LEVEL
FROM   persons.dept a
START  WITH paredeptid = 0
CONNECT BY PRIOR deptid = paredeptid;

進階技巧與注意事項

• 排序輸出：搭配 ORDER SIBLINGS BY 維持同層節點排序，不破壞階層結構。
• 路徑構建：SYS_CONNECT_BY_PATH(name, ' > ') 可產生完整路徑字串 (如「總公司 > 研發部 > 後端組」)。
• 循環偵測：Oracle 10g+ 支援 CONNECT BY NOCYCLE PRIOR ... 避免資料臟污導致無限遞歸，並可用 CONNECT_BY_ISCYCLE 標記循環節點。
• 效能考量：大量資料時，確保父子欄位有索引；必要時可改寫為 WITH RECURSIVE (11gR2+) 並配合 SEARCH DEPTH FIRST 優化。
• WHERE vs CONNECT BY 過濾：WHERE 過濾不影響遞歸路徑；若需在遞歸過程中剪枝，需放入 CONNECT BY 條件中 (如 CONNECT BY PRIOR deptid = paredeptid AND status='ACTIVE')。

總結對照表

善用 CONNECT BY 的宣告式特性，能以極簡潔的 SQL 解決複雜的階層查詢問題，是 Oracle 開發者的利器。