Oracle 遞歸查詢 CONNECT BY 實戰指南：從基礎語法到組織架構遍歷

Oracle 的 CONNECT BY 子句是處理階層式資料（組織架構、物料清單、目錄樹）的利器。本文透過具體範例，系統講解遞歸查詢的語法結構、PRIOR 運算子運作邏輯、START WITH 根節點定義，以及實務常見的向下/向上遍歷模式。

核心語法結構

SELECT column_list
FROM   table_name
START WITH condition          -- 根節點條件
CONNECT BY PRIOR child_col = parent_col  -- 遞歸關聯條件

關鍵概念解析

• START WITH：指定遞歸起始點（根節點），可返回多行作為多棵樹的根
• CONNECT BY PRIOR：定義父子關聯方向
  • PRIOR child = parent：向下遍歷（父 → 子）
  • PRIOR parent = child：向上遍歷（子 → 父）
• LEVEL 偽欄：根節點 LEVEL=1，每遞歸一層遞增
• SYS_CONNECT_BY_PATH：串接路徑字串，便於顯示完整層級路徑
• CONNECT_BY_ISLEAF：判斷是否為葉節點 (1=葉, 0=非葉)
• CONNECT_BY_ROOT：取得根節點欄位值

範例一：基本向下遍歷（員工-主管架構）

測試資料

PID  ID
A    B
A    C
A    E
B    B1
B    B2
C    C1
E    E1
E    E3
D    D1

查詢：以 A 為根向下展開

SELECT PID, ID
FROM   test
START WITH PID = 'A'
CONNECT BY PRIOR ID = PID;

結果

PID  ID
A    B
A    C
A    E
B    B1
B    B2
C    C1
E    E1
E    E3

範例二：實務組織架構表

表結構 COMM_ORG_SUBJECTION

ORG_SUBJECTION_ID  -- 子鍵 (PK)
ORG_ID             -- 組織代碼
FATHER_ORG_ID      -- 父組織代碼
...其他欄位...

向下查找所有子節點

SELECT *
FROM   comm_org_subjection a
START WITH a.org_id = 'C6000000000001'
CONNECT BY PRIOR a.org_subjection_id = a.father_org_id;

向上查找所有父節點

SELECT org_id
FROM   comm_org_subjection a
START WITH a.org_id = 'C6000000000001'
CONNECT BY PRIOR a.father_org_id = a.org_subjection_id;

進階實務技巧

1. 顯示完整路徑與層級

SELECT LEVEL,
       LPAD(' ', 2*(LEVEL-1)) || org_id AS org_tree,
       SYS_CONNECT_BY_PATH(org_id, ' > ') AS full_path,
       CONNECT_BY_ISLEAF AS is_leaf
FROM   comm_org_subjection
START WITH org_id = 'ROOT_ORG'
CONNECT BY PRIOR org_subjection_id = father_org_id;

2. 排除循環參考 (NOCYCLE)

SELECT ...
FROM   ...
START WITH ...
CONNECT BY NOCYCLE PRIOR child = parent;

Oracle 10g+ 支援 NOCYCLE 防止無限迴圈，配合 CONNECT_BY_ISCYCLE 偵測循環。

3. 兄弟節點排序 (ORDER SIBLINGS BY)

SELECT ...
FROM   ...
START WITH ...
CONNECT BY PRIOR child = parent
ORDER SIBLINGS BY org_name;

僅對同層級兄弟節點排序，不改變父子層級關係。

效能考量

• 遞歸查詢本質為逐行處理，大量資料時效能較集合操作差
• 確保連結欄位有索引 (FATHER_ORG_ID, ORG_SUBJECTION_ID)
• 深層次樹狀結構考慮物化路徑或巢狀集合模型替代
• 11g+ 可使用標準 SQL WITH RECURSIVE (ANSI 標準) 替代

總結

掌握 START WITH ... CONNECT BY PRIOR 的父子方向邏輯，是處理 Oracle 階層資料的核心技能。結合 LEVEL、SYS_CONNECT_BY_PATH、ORDER SIBLINGS BY 等進階功能，即可優雅解決組織架構、BOM 展開、目錄樹等經典業務場景。