樹形結構數據（組織架構、菜單、物料 BOM）在 Oracle 中常用 START WITH ... CONNECT BY 進行遞歸查詢。本文通過建表、插數、正向/反向遍歷示例，詳細講解核心子句語法、PRIOR 運算符位置決定遍歷方向、LEVEL 偽列、SYS_CONNECT_BY_PATH 路徑拼接等實戰技巧。
CREATE TABLE tbl_test (
id NUMBER PRIMARY KEY,
name VARCHAR2(100),
pid NUMBER DEFAULT 0
);
INSERT INTO tbl_test VALUES (1, '10', 0);
INSERT INTO tbl_test VALUES (2, '11', 1);
INSERT INTO tbl_test VALUES (3, '20', 0);
INSERT INTO tbl_test VALUES (4, '12', 1);
INSERT INTO tbl_test VALUES (5, '121', 2);
COMMIT;
樹形結構：
10 (root) ├─ 11 │ └─ 121 └─ 12 20 (root)
SELECT column_list
FROM table_name
[WHERE condition]
[START WITH root_condition]
CONNECT BY [NOCYCLE] PRIOR parent_col = child_col
[ORDER SIBLINGS BY col];
SELECT id, name, pid, LEVEL, SYS_CONNECT_BY_PATH(name, '>') AS path
FROM tbl_test
START WITH id = 1
CONNECT BY PRIOR id = pid;
關鍵：PRIOR id = pid 表示「上一條記錄的 ID = 當前記錄的 PID」，即父 → 子。
結果：
| ID | NAME | PID | LEVEL | PATH |
|---|---|---|---|---|
| 1 | 10 | 0 | 1 | >10 |
| 2 | 11 | 1 | 2 | >10>11 |
| 5 | 121 | 2 | 3 | >10>11>121 |
| 4 | 12 | 1 | 2 | >10>12 |
SELECT id, name, pid, LEVEL
FROM tbl_test
START WITH id = 5
CONNECT BY PRIOR pid = id;
關鍵：PRIOR pid = id 表示「上一條記錄的 PID = 當前記錄的 ID」，即子 → 父。
結果：
| ID | NAME | PID | LEVEL |
|---|---|---|---|
| 5 | 121 | 2 | 1 |
| 2 | 11 | 1 | 2 |
| 1 | 10 | 0 | 3 |
| 需求 | 寫法 |
|---|---|
| 多根節點 | START WITH pid = 0 或 START WITH id IN (1,3) |
| 過濾特定層級 | WHERE LEVEL <= 2 (在 CONNECT BY 後) |
| 排序同級節點 | ORDER SIBLINGS BY name |
| 檢測循環 | CONNECT BY NOCYCLE PRIOR ... + CONNECT_BY_ISCYCLE 偽列 |
| 路徑拼接 | SYS_CONNECT_BY_PATH(col, '/') |
| 判斷葉子節點 | CONNECT_BY_ISLEAF 偽列 (1=葉子) |
SELECT * FROM v_wf_wfkind_tree
WHERE id IN (
SELECT DISTINCT id
FROM v_wf_wfkind_tree
START WITH id IN ('0','01','0101','0203','0304')
CONNECT BY PRIOR pid = id
);
從給定節點集合向上遞歸收集所有祖先，配合去重獲取完整鏈路。
掌握 START WITH 定根、CONNECT BY PRIOR 定向 (父→子 vs 子→父)、LEVEL/PATH/ISLEAF/ISCYCLE 偽列、ORDER SIBLINGS BY 排序，即可應對 99% 樹形查詢場景。11g+ 亦可用標準 WITH RECURSIVE (CTE) 實現跨庫兼容。
