UDF在層次型數據處理中的妙用之五

UDF在層次型數據處理中的妙用　　

　　要得到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函數，報告通向特定雇員的管理鏈。


LISTING 7：獲取通向特定雇員的管理鏈

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


　　從SELECT查詢的性能考慮，我們可以把lvl和path列的值直接加入到Employees表，然後通過觸發器更新這兩個列的值。采用這種方法要比在UDF中計算lvl和path值更有利於提高查詢的性能。如果要使用一個用到了lvl列和path列的過濾器，ufn_GetSubtree必須先掃描整個表，計算這兩個列的值。如果讓lvl和path作為Employees表的列，我們可以在path列上建立一個索引，從而避免對整個表的掃描操作。然而，使用ufn_GetSubtree函數時，我們無需對Employees的結構做任何修改，而且和使用觸發器時不同，我們不會增加任何維護lvl和path列的負擔。UDF的出現拓展了無限的編程空間。在這裡，UDF是不要求對表的結構做任何修改的強大層次型數據處理工具。

關鍵詞：