PL/SQL 階層查詢完全指南：CONNECT BY、PRIOR、CONNECT_BY_ROOT 實戰

Oracle 10g 新增操作符

• PRIOR：指定父節點欄位，優先級等同 +/-。
• CONNECT_BY_ROOT：取得根節點資訊。

基本語法

SELECT ...
FROM table
[START WITH condition]          -- 指定根節點
CONNECT BY [NOCYCLE] condition  -- 定義父子關係
[ORDER SIBLINGS BY col];        -- 維持同層級排序

• condition 形式：PRIOR child_col = parent_col 或 child_col = PRIOR parent_col。
• NOCYCLE：資料存在循環參考時仍回傳結果，配合 CONNECT_BY_ISCYCLE 偵測循環。

核心偽欄位

實戰範例（HR.EMPLOYEES）

1. 基本上下層關係

SELECT employee_id, last_name, manager_id
FROM employees
CONNECT BY PRIOR employee_id = manager_id;

2. 顯示層級

SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id;

3. 指定根節點 + 同層排序

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

4. 處理循環參考 (NOCYCLE)

將 Russell 設為 King 的主管，製造循環：

UPDATE employees SET manager_id = 145 WHERE employee_id = 100;

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），並繼續展開其他分支。

5. CONNECT_BY_ROOT 取得根節點資訊

-- 110 部門員工與其各層級主管
SELECT last_name "Employee",
       CONNECT_BY_ROOT last_name "Root_Manager",
       LEVEL-1 "Path_Len",
       SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE LEVEL > 1 AND department_id = 110
CONNECT BY PRIOR employee_id = manager_id;

6. 彙總根節點下所有子節點薪資

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;

效能與使用建議

• 大量資料建立索引於 PRIOR 參考欄位（如 EMPLOYEE_ID、MANAGER_ID）。
• 避免在 CONNECT BY 條件中使用複雜函數或子查詢。
• 10g+ 推薦使用 CONNECT BY NO FILTERING（對應 Hint NO_CONNECT_BY_FILTERING）改善大樹狀結構效能。
• 搭配 START WITH 限制掃描範圍，避免全表遞迴。

1. 上一篇文章： Oracle 自訂函數效能實測：引擎切換成本與實作差異分析
2. 下一篇文章： PL/SQL 呼叫 Web Service 實作：UTL_DBWS 套件完整教學

Oracle 資料庫基礎

Oracle 預存程序分頁查詢封裝：動態 SQL + REF CURSOR 實作

設計目標 封裝通用分頁查詢，支援任意表、任意 WHERE/O

三種將文字資料匯入 Oracle 的實務做法

方法一：SQL*Loader 命令列模式（最常用、效能最佳）

Oracle 專用連線 vs 共享連線：架構差異、效能權衡與 OLTP 選型實戰指南

兩種連線架構本質對比 維度專用連線共享連線 連線模型一對

PL/SQL Developer 窗口列表移至左側：兩步配置優化開發佈局

PL/SQL Developer 默認窗口列表位於右側，習慣