若要取得 Andrew 子樹中的葉節點 (非管理者的員工),查詢如下:
SELECT * FROM ufn_GetSubtree(2) AS S
WHERE NOT EXISTS (SELECT * FROM Employees AS E WHERE E.mgrid = S.empid)
最後一項任務是利用 Listing 7 的 ufn_GetMgmtChain 函數,報告通向特定員工的管理鏈。
CREATE FUNCTION ufn_GetMgmtChain
(
@empid AS int
)
RETURNS @tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL
)
AS
BEGIN
DECLARE @lvl AS int
SET @lvl = 0
INSERT INTO @tree
SELECT empid, mgrid, empname, salary, @lvl
FROM Employees
WHERE empid = @empid
WHILE @@ROWCOUNT > 0
BEGIN
SET @lvl = @lvl + 1
INSERT INTO @tree
SELECT E.empid, E.mgrid, E.empname, E.salary, @lvl
FROM Employees AS E JOIN @tree AS T
ON E.empid = T.mgrid AND T.lvl = @lvl - 1
END
RETURN
END
ufn_GetMgmtChain 的實現與 ufn_GetSubtree 相似,差別在於順著管理鏈向上直到頂層。取得通向 James (empid=14) 的管理鏈:
SELECT * FROM ufn_GetMgmtChain(14) ORDER BY lvl DESC
為提升查詢效能,可將 lvl 和 path 欄位直接加入 Employees 表,並透過觸發器維護。這樣可在 path 上建立索引,避免 UDF 掃描整表計算。但使用 UDF 免去修改表結構與觸發器維護負擔,展現了 UDF 在層次資料處理上的強大彈性。