部門架構、組織架構、物料清單 (BOM)、分類目錄等樹狀結構資料在企業應用普遍存在。Oracle 提供強大的 **階層查詢** 功能(CONNECT BY / START WITH)與 **WITH 子查詢 (CTE, Common Table Expression)**,能以純 SQL 完成遞迴遍歷、路徑構建、層級縮排顯示。本文以部門管理為例,完整示範階層查詢語法、關鍵偽欄位、縮排顯示、循環偵測、WITH 遞迴寫法 (11gR2+),並對比兩者優劣。
CREATE TABLE m_depart (
fnum VARCHAR2(20) PRIMARY KEY, -- 部門代碼
fname VARCHAR2(100), -- 部門名稱
fparent VARCHAR2(20) -- 父部門代碼 (自參考 FK)
);
-- 範例資料
INSERT INTO m_depart VALUES ('D001', '總公司', NULL);
INSERT INTO m_depart VALUES ('D010', '華北區', 'D001');
INSERT INTO m_depart VALUES ('D011', '北京分公司', 'D010');
INSERT INTO m_depart VALUES ('D012', '天津分公司', 'D010');
INSERT INTO m_depart VALUES ('D020', '華東區', 'D001');
INSERT INTO m_depart VALUES ('D021', '上海分公司', 'D020');
COMMIT;

SELECT t.fnum,
LPAD(' ', 3*(LEVEL-1)) || '|-' || t.fname AS fname,
t.fparent,
LEVEL AS lev
FROM m_depart t
START WITH t.fparent IS NULL -- 根節點條件
CONNECT BY PRIOR t.fnum = t.fparent; -- 父子關聯: 父節點 fnum = 子節點 fparent
| 偽欄位/函數 | 意義 | 範例輸出 |
|---|---|---|
LEVEL | 當前節點層級 (根=1) | 1, 2, 3... |
CONNECT_BY_ISLEAF | 是否葉節點 (1=是, 0=否) | 1 (無子節點) |
CONNECT_BY_ISCYCLE | 是否形成循環 (需 NOCYCLE) | 1 (檢測到循環) |
SYS_CONNECT_BY_PATH(col, '/') | 從根到當前節點的路徑字串 | /總公司/華北區/北京分公司 |
CONNECT_BY_ROOT col | 根節點的指定欄位值 | 總公司 |
| 維度 | CONNECT BY | WITH RECURSIVE |
|---|---|---|
| 標準性 | Oracle 專有 | ANSI SQL:1999 標準 (跨資料庫可攜) |
| 可讀性 | 簡潔、宣告式 | 結構化、邏輯清晰 (Anchor + Recursive) |
| 功能豐富度 | 內建 LEVEL、PATH、ISLEAF、ISCYCLE、ORDER SIBLINGS | 需自行構建 (如 path、level 計算) |
| 效能 | 優化器深度整合,通常較快 | 現代優化器亦佳,複雜邏輯更靈活 |
| 多重遞迴 | 單一 CONNECT BY | 可定義多個 CTE 互相引用 |
| Debug/中間結果檢視 | 困難 | 可單獨查詢 CTE 中間結果 |
| 適用版本 | 所有版本 | 11gR2+ |
-- Oracle 12c+ 支援 SEARCH 子句控制遍歷順序
WITH dept_tree(...) AS (...) -- 同上
SEARCH DEPTH FIRST BY fname SET seq_order -- DFS (預設)
-- SEARCH BREADTH FIRST BY fname SET seq_order -- BFS
SELECT * FROM dept_tree ORDER BY seq_order;
-- CONNECT BY
SELECT fnum, SYS_CONNECT_BY_PATH(fname, ' > ') AS full_path
FROM m_depart
START WITH fparent IS NULL
CONNECT BY PRIOR fnum = fparent;
-- WITH
WITH dept_tree(...) AS (...) SELECT fnum, path FROM dept_tree;
SELECT fnum, fname, COUNT(*) - 1 AS descendant_cnt
FROM m_depart
START WITH fparent IS NULL
CONNECT BY PRIOR fnum = fparent
GROUP BY fnum, fname;
SELECT fnum AS id, fparent AS parent_id, fname AS text, LEVEL AS level
FROM m_depart
START WITH fparent IS NULL
CONNECT BY PRIOR fnum = fparent;
fparent 欄位**必須建立索引**,否則遞迴每層皆全表掃描。LEVEL 偽欄位限制),極深樹需改用 WITH + 迴圈或應用層遞迴。Oracle CONNECT BY 是處理樹狀結構的利器,語法簡潔、功能豐富、效能優異;WITH RECURSIVE 提供標準化、模組化、可除錯的替代方案。掌握兩者語法、偽欄位、循環處理、路徑構建、排序控制,能輕鬆應對組織架構、BOM、分類目錄等各類遞迴查詢需求。**關鍵成功因子:fparent 索引、統計資訊鮮度、合理深度預期、前端配合分頁/懶加載**。