Oracle 階層式查詢完全指南：CONNECT BY、遞迴 CTE、樹狀結構展開與過濾

核心語法

SELECT columns
FROM table
START WITH condition_root      -- 根節點條件
CONNECT BY condition_parent_child  -- 父子關係
[WHERE condition_filter];      -- 節點過濾（注意執行順序）

正向樹（由根向葉展開）

SELECT LPAD(' ', 4*(LEVEL-1)) || industry_name AS tree_view,
       ind_level, ind_id, parent_id
FROM org_industries
START WITH ind_id = 1          -- 根節點：頂層產業
CONNECT BY parent_id = PRIOR ind_id;  -- 父 = 前一行的子

關鍵：CONNECT BY 父欄位 = PRIOR 子欄位

反向樹（由葉向根回溯）

SELECT LPAD(' ', 4*(LEVEL-1)) || industry_name AS tree_view,
       ind_level, ind_id, parent_id
FROM org_industries
START WITH ind_id = 20         -- 葉節點：某細分產業
CONNECT BY ind_id = PRIOR parent_id;  -- 子 = 前一行的父

關鍵：CONNECT BY 子欄位 = PRIOR 父欄位

實用虛擬欄位

樹狀結構條件過濾：砍掉分支

錯誤寫法（在 WHERE 過濾會先過濾再遞迴，導致子孫遺失）：

-- 錯誤：會遺失被過濾節點的所有子孫
SELECT * FROM org_industries
START WITH ind_id = 1
CONNECT BY parent_id = PRIOR ind_id
WHERE industry_name != '服裝';  -- 服裝及其子孫全消失

正確寫法（在 CONNECT BY 加條件，保留子孫但不顯示該節點）：

SELECT * FROM org_industries
START WITH ind_id = 1
CONNECT BY parent_id = PRIOR ind_id
  AND industry_name != '服裝';  -- 不顯示「服裝」，但其子孫仍會展開

進階：完全排除某分支（含子孫）：

SELECT * FROM org_industries
START WITH ind_id = 1
CONNECT BY parent_id = PRIOR ind_id
  AND PRIOR industry_name != '服裝';  -- 父節點不是「服裝」才繼續遞迴

遞迴 CTE（ANSI SQL 標準，11g R2+）

WITH RECURSIVE org_tree (ind_id, industry_name, parent_id, lvl, path) AS (
  -- Anchor member
  SELECT ind_id, industry_name, parent_id, 1, industry_name
  FROM org_industries
  WHERE ind_id = 1

  UNION ALL

  -- Recursive member
  SELECT c.ind_id, c.industry_name, c.parent_id, p.lvl + 1,
         p.path || ' > ' || c.industry_name
  FROM org_industries c
  JOIN org_tree p ON c.parent_id = p.ind_id
  WHERE p.lvl < 20  -- 防無限遞迴
)
SELECT * FROM org_tree ORDER BY path;

優點：標準 SQL、可攜性高、易於除錯、支援複雜多欄位排序。

效能優化建議

• 為 parent_id、ind_id 建立索引。
• 大型樹狀表（>10萬筆）考慮 NOCYCLE 防循環：CONNECT BY NOCYCLE parent_id = PRIOR ind_id。
• 避免在遞迴查詢中使用非確定性函數（SYSDATE, DBMS_RANDOM）。
• 僅需特定深度時，在 CTE 中加 WHERE lvl <= n 或 CONNECT BY ... AND LEVEL <= n。

常見應用範例

1. 組織架構圖（員工-主管）

SELECT LPAD(' ', 2*(LEVEL-1)) || ename AS org_chart,
       empno, mgr, LEVEL
FROM emp
START WITH mgr IS NULL
CONNECT BY mgr = PRIOR empno;

2. 物料清單 (BOM) 總價計算

WITH RECURSIVE bom_calc (part_id, parent_id, qty, unit_cost, total_cost, lvl) AS (
  SELECT part_id, parent_id, qty, unit_cost, qty * unit_cost, 1
  FROM bom WHERE part_id = :root_part

  UNION ALL

  SELECT b.part_id, b.parent_id, b.qty, b.unit_cost,
         p.total_cost * b.qty * b.unit_cost, p.lvl + 1
  FROM bom b
  JOIN bom_calc p ON b.parent_id = p.part_id
)
SELECT SUM(total_cost) FROM bom_calc;

結語

掌握 CONNECT BY 與遞迴 CTE 兩套語法，並理解 START WITH、CONNECT BY、WHERE 三者的執行順序差異，是處理 Oracle 樹狀資料的關鍵。新專案建議優先使用遞迴 CTE，符合 ANSI 標準且更易維護。

1. 上一篇文章： Windows 環境下徹底移除 Oracle 服務與殘留元件的標準流程
2. 下一篇文章： Oracle PL/SQL 中修改 Collection 參數的關鍵技巧：IN OUT 模式詳解

Oracle 資料庫基礎

Analytic Workspace Manager (AWM) 圖形化管理Oracle OLAP：從關聯立方體建立AW到BI Bean/Discoverer/SQL多通道發布

Oracle 9i R2引入Analytic Workspa

Oracle 基礎教學：SQL 查詢與函數入門（上）

課程目標 本節課程將帶領您掌握 Oracle 資料庫的核心基

Oracle 11g 閃回資料歸檔 (FDA) 深度解析：從原理到實戰

前言：為什麼需要 FDA？ 傳統方案需自建歷史表 + 觸發器

Oracle 位圖索引陷阱實錄：Thomas Kyte 親授的並發鎖死教訓

故事背景 Tom Kyte（Oracle 首席技術專家，《E