Oracle 遞歸查詢實戰：START WITH ... CONNECT BY 構建組織架構/物料BOM/無限分類樹

適用場景

• 組織架構 (員工-經理)
• 物料清單 BOM (父件-子件)
• 無限分類 (目錄-子目錄)
• 帳戶科目樹、權限菜單樹

資料模型：雙欄位自關聯

CREATE TABLE test_tree (
  id  NUMBER PRIMARY KEY,
  pid NUMBER,           -- 父節點 ID，根節點 PID=0 或 NULL
  name VARCHAR2(50)
);

INSERT INTO test_tree VALUES (1, 0, 'Root');
INSERT INTO test_tree VALUES (2, 0, 'Root2');
INSERT INTO test_tree VALUES (3, 1, 'Child1-1');
INSERT INTO test_tree VALUES (4, 1, 'Child1-2');
INSERT INTO test_tree VALUES (5, 2, 'Child2-1');
INSERT INTO test_tree VALUES (6, 2, 'Child2-2');
INSERT INTO test_tree VALUES (7, 3, 'Leaf1-1-1');
INSERT INTO test_tree VALUES (8, 4, 'Leaf1-2-1');
INSERT INTO test_tree VALUES (9, 5, 'Leaf2-1-1');

核心語法：階層查詢

SELECT *
FROM test_tree
START WITH id = 1                 -- 根節點條件
CONNECT BY PRIOR id = pid         -- 遞歸關係：父 ID = 子 PID
ORDER BY id;                      -- 同層排序

關鍵偽欄位與函數

進階：帶條件過濾的遞歸查詢

-- 只查詢名稱包含 'Child' 的分支，但保留其完整祖先路徑
SELECT *
FROM test_tree
WHERE name LIKE '%Child%'          -- 過濾在 START WITH 之前執行 (10g+)
START WITH id = 1
CONNECT BY PRIOR id = pid;

-- 若需在遞歸過程中過濾 (如只遞歸啟用狀態的節點)
SELECT *
FROM test_tree
START WITH id = 1
CONNECT BY PRIOR id = pid
  AND status = 'ACTIVE';           -- 遞歸條件加入狀態判斷

11g+ 標準 SQL 遞歸：WITH RECURSIVE (CTE) 更強大、可移植

WITH RECURSIVE tree_path (id, pid, name, lvl, path) AS (
  -- Anchor member
  SELECT id, pid, name, 1, '/'||name
  FROM test_tree
  WHERE id = 1
  UNION ALL
  -- Recursive member
  SELECT t.id, t.pid, t.name, tp.lvl+1, tp.path||'/'||t.name
  FROM test_tree t
  JOIN tree_path tp ON t.pid = tp.id
  WHERE tp.lvl < 10                -- 防無限遞歸深度限制
)
SELECT * FROM tree_path ORDER BY lvl, id;

• 支援 **多根節點**、**自定義輸出欄位**、**跨表遞歸**、**可移植至 PostgreSQL/SQL Server/MySQL 8.0+**。
• 生產環境**強烈建議遷移至 CTE 寫法**。

效能優化建議

1. PID 欄位建索引：`CREATE INDEX idx_test_tree_pid ON test_tree(pid);`，加速 `CONNECT BY` 子查詢定位子節點。
2. 避免 SELECT *：僅選取必要欄位，減少投影開銷。
3. 深度限制：`WHERE LEVEL <= 10` 或 CTE 中 `WHERE lvl < 10`，防資料錯誤導致無限遞歸。
4. 大樹分頁/懶加載：前端僅載入前 2-3 層，點擊展開再查詢子樹 (`WHERE pid = :parent_id`)。

結語

`START WITH ... CONNECT BY` 是 Oracle 獨有的階層查詢語法，簡潔強大；**`WITH RECURSIVE` CTE 是 ANSI SQL 標準、功能更豐、可移植**，為現代應用首選。掌握兩種寫法、偽欄位應用、索引優化與循環檢測，即可輕鬆構建任意深度的樹狀資料應用。