Oracle 10g 階層式查詢新語法：PRIOR、CONNECT_BY_ROOT、NOCYCLE 完整指南

前言

Oracle 10g 強化階層查詢，新增 CONNECT_BY_ROOT、NOCYCLE、CONNECT_BY_ISCYCLE 等操作符與偽欄，支援遞迴關係查詢與循環偵測。

核心語法

SELECT ...
FROM table
START WITH condition          -- 根節點條件
CONNECT BY [NOCYCLE] condition;  -- 父子關係，NOCYCLE 允許循環不報錯

• PRIOR 運算子指定父節點欄位，優先級同 +/-。
  範例：PRIOR employee_id = manager_id 或 manager_id = PRIOR employee_id。
• LEVEL 偽欄：根節點為 1，每層遞增。
• SYS_CONNECT_BY_PATH(col, '/')：回傳從根到目前節點的路徑字串。

新增功能示例

1. CONNECT_BY_ROOT：取得根節點值

SELECT last_name "Employee",
       CONNECT_BY_ROOT last_name "Root_Manager",
       LEVEL-1 "Pathlen",
       SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE LEVEL > 1 AND department_id = 110
CONNECT BY PRIOR employee_id = manager_id;

輸出每位員工的根主管、層級差與完整路徑。

2. NOCYCLE + CONNECT_BY_ISCYCLE：循環偵測與處理

人為製造循環：UPDATE employees SET manager_id=145 WHERE employee_id=100;（King 的主管設為 Russell）。

SELECT last_name "Employee",
       CONNECT_BY_ISCYCLE "Cycle",
       LEVEL,
       SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE LEVEL <= 3 AND department_id = 80
START WITH last_name = 'King'
CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4;

• Cycle=1 表示該節點參與循環（Russell）。
• Cycle=0 正常節點。
• 查詢不會因循環報錯 ORA-01436，而是正常返回。

3. ORDER SIBLINGS BY：保持同層排序

SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;

4. 搭配 GROUP BY 彙總階層資料

SELECT name, SUM(salary) "Total_Salary"
FROM (
  SELECT CONNECT_BY_ROOT last_name AS name, salary
  FROM employees
  WHERE department_id = 110
  CONNECT BY PRIOR employee_id = manager_id
)
GROUP BY name;

計算每位主管名下所有下屬薪資總和（含自身）。

小結

10g 階層查詢增強解決了循環處理、根節點存取、路徑顯示等實務痛點，配合 LEVEL、SYS_CONNECT_BY_PATH 可構建強大的組織架構、BOM 展開等報表。