測試數據準備
CREATE TABLE Dept (
DepartNO VARCHAR2(10),
DepartName VARCHAR2(20),
TopNo VARCHAR2(10)
);
INSERT INTO Dept VALUES ('001','董事會','0');
INSERT INTO Dept VALUES ('002','總裁辦','001');
INSERT INTO Dept VALUES ('003','財務部','001');
INSERT INTO Dept VALUES ('004','市場部','002');
INSERT INTO Dept VALUES ('005','公關部','002');
INSERT INTO Dept VALUES ('006','銷售部','002');
INSERT INTO Dept VALUES ('007','分銷處','006');
INSERT INTO Dept VALUES ('008','業務拓展處','004');
INSERT INTO Dept VALUES ('009','銷售科','007');
核心查詢語法
SELECT DepartName
FROM Dept
START WITH TopNo = '0'
CONNECT BY PRIOR DepartNO = TopNo;
START WITH：指定根節點條件。CONNECT BY PRIOR：定義父子關係（父 DepartNO = 子 TopNo）。
常用偽欄位與函數
| 偽欄位/函數 | 用途 | 示例 |
|---|
| LEVEL | 當前層級 (根=1) | SELECT LEVEL, DepartName ... |
| SYS_CONNECT_BY_PATH(col,'/') | 從根到當前節點路徑 | SELECT SYS_CONNECT_BY_PATH(DepartName,'/') ... |
| CONNECT_BY_ISLEAF | 是否葉子節點 (1/0) | WHERE CONNECT_BY_ISLEAF = 1 |
| CONNECT_BY_ROOT col | 根節點的列值 | SELECT CONNECT_BY_ROOT DepartName ... |
縮進顯示層級
SELECT LPAD(DepartNO, LEVEL*2 + LENGTH(DepartNO), ' ') AS TreeView
FROM Dept
START WITH TopNo = '0'
CONNECT BY PRIOR DepartNO = TopNo;
/* 輸出：
001
002
004
008
005
006
007
009
003 */
父子對照查詢
SELECT a.DepartName AS Parent, b.DepartName AS Child
FROM Dept a, Dept b
WHERE a.DepartNO = b.TopNo;
自定義函數獲取節點深度
CREATE OR REPLACE FUNCTION GetLevel(TempNo VARCHAR2) RETURN VARCHAR2 IS
Result VARCHAR2(20);
CURSOR CurLevel(curTempNo VARCHAR2) IS
SELECT DepartNO
FROM Dept
START WITH DepartNO = curTempNo
CONNECT BY PRIOR TopNo = DepartNO;
BEGIN
-- 實現邏輯：遞歸查找父節點直到根，計數層級
RETURN Result;
END;
