Oracle 階層查詢 CONNECT BY 與 WITH 遞歸 CTE 實戰：組織架構樹狀顯示

部門、選單、帳套等樹狀結構常存於單表，靠 PARENT_ID 自關聯。Oracle 提供兩種查詢方式：傳統 CONNECT BY 與 ANSI 標準 WITH RECURSIVE (11gR2+)。本文以部門表為例，示範兩種寫法與輸出格式化。

測試表結構

CREATE TABLE m_depart (
  fnum     VARCHAR2(20) PRIMARY KEY,
  fname    VARCHAR2(100),
  fparent  VARCHAR2(20) REFERENCES m_depart(fnum)
);

-- 範例資料
INSERT INTO m_depart VALUES ('1', '總公司', NULL);
INSERT INTO m_depart VALUES ('11', '華北分公司', '1');
INSERT INTO m_depart VALUES ('111', '北京部', '11');
INSERT INTO m_depart VALUES ('12', '華東分公司', '1');
INSERT INTO m_depart VALUES ('121', '上海部', '12');

方法一：CONNECT BY（Oracle 專有，簡潔）

SELECT fnum,
       LPAD(' ', 3*(LEVEL-1)) || '|-' || fname AS fname,
       fparent,
       LEVEL AS lev
FROM m_depart
START WITH fparent IS NULL
CONNECT BY PRIOR fnum = fparent
ORDER SIBLINGS BY fnum;

輸出

FNUM  FNAME                    FPARENT  LEV
----- ------------------------ -------- ---
1     |-總公司                          1
11    |---華北分公司                    1      2
111   |------北京部                     11     3
12    |---華東分公司                    1      2
121   |------上海部                     12     3

關鍵字解釋

• START WITH：根節點條件（頂層部門）。
• CONNECT BY PRIOR：父子關聯條件（PRIOR 指父行欄位）。
• LEVEL：虛擬欄位，根=1，遞增。
• ORDER SIBLINGS BY：同層級排序，不影響層次結構。
• SYS_CONNECT_BY_PATH(fname, '>')：取得完整路徑 總公司>華北分公司>北京部。
• CONNECT_BY_ISLEAF：判斷是否葉節點（1=葉）。
• CONNECT_BY_ROOT fname：取得根節點名稱。

方法二：WITH RECURSIVE（ANSI SQL，可攜性佳）

WITH RECURSIVE dept_tree (fnum, fname, fparent, lvl, path) AS (
  -- Anchor member: 根節點
  SELECT fnum, fname, fparent, 1, fname
  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
  WHERE dt.lvl < 10  -- 防無限遞歸
)
SEARCH DEPTH FIRST BY fnum SET order_col
SELECT fnum,
       LPAD(' ', 3*(lvl-1)) || '|-' || fname AS fname,
       fparent,
       lvl AS lev,
       path
FROM dept_tree
ORDER BY order_col;

優勢

• 標準 SQL，遷移 PostgreSQL/SQL Server/MySQL 8.0+ 免改碼。
• 可在 CTE 內完成複雜轉換（如路徑串聯、層級聚合）。
• SEARCH DEPTH/BREADTH FIRST 控制輸出順序。

效能與選擇建議

常用索引建議

CREATE INDEX idx_m_depart_parent ON m_depart(fparent);
-- 加速 CONNECT BY PRIOR fnum = fparent 的父鍵查找