萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 階層查詢與 WITH 子查詢實戰:CONNECT BY 遞迴樹狀結構完整指南

前言

部門架構、組織架構、物料清單 (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;

部門表資料範例

二、CONNECT BY 階層查詢 (經典語法,全版本支援)

2.1 基礎語法

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

2.2 關鍵偽欄位與函數

2.3 完整輸出範例

FNUM    FNAME                      FPARENT   LEV
-----   -------------------------  --------  ---
D001    |-總公司                   NULL      1
D010    |---|-華北區               D001      2
D011    |------|-北京分公司        D010      3
D012    |------|-天津分公司        D010      3
D020    |---|-華東區               D001      2
D021    |------|-上海分公司        D020      3

查詢結果樹狀顯示

2.4 進階應用

  • 僅查詢某子樹:START WITH fnum = 'D010'
  • 自下而上查詢:CONNECT BY PRIOR fparent = fnum START WITH fnum = 'D011'
  • 防循環:CONNECT BY NOCYCLE PRIOR fnum = fparent,配合 CONNECT_BY_ISCYCLE 識別循環節點。
  • 排序同層節點:ORDER SIBLINGS BY fname (僅影響同層順序,不破壞層級結構)。

三、WITH 遞迴子查詢 (CTE, ANSI SQL 標準,11gR2+)

3.1 語法結構

WITH RECURSIVE dept_tree (fnum, fname, fparent, lvl, path) AS (
    -- Anchor member (錨點): 根節點
    SELECT fnum, fname, fparent, 1 AS lvl, fname AS path
    FROM m_depart
    WHERE fparent IS NULL

    UNION ALL

    -- Recursive member (遞迴): 子節點關聯父節點
    SELECT d.fnum, d.fname, d.fparent, dt.lvl + 1, dt.path || ' > ' || d.fname
    FROM m_depart d
    JOIN dept_tree dt ON d.fparent = dt.fnum
)
SELECT * FROM dept_tree ORDER BY path;

3.2 CONNECT BY vs WITH RECURSIVE 對比

偽欄位/函數意義範例輸出
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 BYWITH RECURSIVE
標準性Oracle 專有ANSI SQL:1999 標準 (跨資料庫可攜)
可讀性簡潔、宣告式結構化、邏輯清晰 (Anchor + Recursive)
功能豐富度內建 LEVEL、PATH、ISLEAF、ISCYCLE、ORDER SIBLINGS需自行構建 (如 path、level 計算)
效能優化器深度整合,通常較快現代優化器亦佳,複雜邏輯更靈活
多重遞迴單一 CONNECT BY可定義多個 CTE 互相引用
Debug/中間結果檢視困難可單獨查詢 CTE 中間結果
適用版本所有版本11gR2+

3.3 WITH 進階:廣度優先搜尋 (BFS) vs 深度優先 (DFS)

-- 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;

四、實務應用場景與技巧

4.1 取得完整路徑 (麵包屑導航)

-- 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;

4.2 統計各節點子孫數量

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;

4.3 扁平化輸出供前端樹形元件 (id, parent_id, text, level)

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 欄位**必須建立索引**,否則遞迴每層皆全表掃描。
  • 深度限制:預設最大深度 255 (受 LEVEL 偽欄位限制),極深樹需改用 WITH + 迴圈或應用層遞迴。
  • 大量節點:超過 10 萬節點時,考慮:
    • Materialized View 預先扁平化。
    • 應用層快取樹結構 (Redis/記憶體)。
    • 分批載入 (Lazy Loading) 前端元件。
  • 統計資訊:定期收集表統計資訊,優化器才能選擇最佳連接順序。

六、結語

Oracle CONNECT BY 是處理樹狀結構的利器,語法簡潔、功能豐富、效能優異;WITH RECURSIVE 提供標準化、模組化、可除錯的替代方案。掌握兩者語法、偽欄位、循環處理、路徑構建、排序控制,能輕鬆應對組織架構、BOM、分類目錄等各類遞迴查詢需求。**關鍵成功因子:fparent 索引、統計資訊鮮度、合理深度預期、前端配合分頁/懶加載**。

copyright © 萬盛學電腦網 all rights reserved