您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 遞歸查詢基礎語法：START WITH CONNECT BY 樹狀結構走訪完整指南

編輯：Oracle 資料庫基礎

建立測試表與資料


CREATE TABLE tbl_test (
  id   NUMBER,
  name VARCHAR2(100 BYTE),
  pid  NUMBER DEFAULT 0
);

INSERT INTO tbl_test (id, name, pid) VALUES (1, '10', 0);
INSERT INTO tbl_test (id, name, pid) VALUES (2, '11', 1);
INSERT INTO tbl_test (id, name, pid) VALUES (3, '20', 0);
INSERT INTO tbl_test (id, name, pid) VALUES (4, '12', 1);
INSERT INTO tbl_test (id, name, pid) VALUES (5, '121', 2);
COMMIT;

樹狀結構：

1 (Root)
├─ 2
│  └─ 5
└─ 4
3 (另一 Root)

核心語法


SELECT [columns]
FROM table
START WITH condition  -- 起始點 (Root 或 Leaf)
CONNECT BY PRIOR parent_col = child_col;  -- 父子關聯

自頂向下走訪 (Root → Leaf)


SELECT * 
FROM tbl_test
START WITH id = 1
CONNECT BY PRIOR id = pid;

結果順序：1 → 2 → 5 → 4 (深度優先)

PRIOR id = 父層 ID
pid = 子層父 ID
意義：「父層 ID = 子層 PID」→ 向下找子節點

自底向上走訪 (Leaf → Root)


SELECT * 
FROM tbl_test
START WITH id = 5
CONNECT BY PRIOR pid = id;

結果順序：5 → 2 → 1

PRIOR pid = 父層 PID
id = 子層 ID
意義：「父層 PID = 子層 ID」→ 向上找父節點

實用輔助欄位與函數

欄位/函數	用途	範例
`LEVEL`	目前層級 (Root=1)	`SELECT LPAD(' ',2*(LEVEL-1))\|\|name FROM ...`
`CONNECT_BY_ISLEAF`	是否為葉節點 (1=是)	`WHERE CONNECT_BY_ISLEAF = 1`
`CONNECT_BY_ISCYCLE`	是否形成循環 (需 NOCYCLE)	`SELECT ... CONNECT BY NOCYCLE PRIOR ...`
`SYS_CONNECT_BY_PATH(col,'>')`	從 Root 到目前節點的路徑	`SYS_CONNECT_BY_PATH(name,' > ')`
`CONNECT_BY_ROOT col`	取得 Root 節點的欄位值	`CONNECT_BY_ROOT name AS root_name`

進階範例：完整樹狀顯示


SELECT 
  LEVEL,
  LPAD(' ', 2*(LEVEL-1)) || name AS tree_view,
  CONNECT_BY_ROOT name AS root_name,
  SYS_CONNECT_BY_PATH(name, ' > ') AS full_path,
  CONNECT_BY_ISLEAF AS is_leaf
FROM tbl_test
START WITH pid = 0  -- 所有 Root
CONNECT BY PRIOR id = pid
ORDER SIBLINGS BY name;  -- 同層級按名稱排序

常見陷阱與解決

問題	原因	解決
無限迴圈 / ORA-01436	資料存在循環參考 (A→B→A)	加入 `NOCYCLE` 關鍵字，並檢查 `CONNECT_BY_ISCYCLE`
結果順序不符預期	預設深度優先，同層無序	使用 `ORDER SIBLINGS BY col` (10g+)
效能差	大量資料遞歸、無索引	在 PID/ID 建立索引；考慮 Materialized View 預先展開
無法使用 WHERE 過濾	WHERE 在 CONNECT BY 後執行，會破壞遞歸	用 `START WITH` 限制起始集、或子查詢先過濾再遞歸

11g+ 替代方案：遞歸 CTE (WITH RECURSIVE)


WITH RECURSIVE tree (id, name, pid, lvl, path) AS (
  SELECT id, name, pid, 1, name
  FROM tbl_test
  WHERE pid = 0
  UNION ALL
  SELECT t.id, t.name, t.pid, tr.lvl+1, tr.path || ' > ' || t.name
  FROM tbl_test t
  JOIN tree tr ON t.pid = tr.id
)
SELECT * FROM tree;

優點：ANSI 標準、可移植、邏輯更直觀、支援廣度優先、易於加入複雜過濾。Oracle 11gR2 起完整支援。