萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

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

效能考量

為提升查詢效能,可將 lvl 和 path 欄位直接加入 Employees 表,並透過觸發器維護。這樣可在 path 上建立索引,避免 UDF 掃描整表計算。但使用 UDF 免去修改表結構與觸發器維護負擔,展現了 UDF 在層次資料處理上的強大彈性。

copyright © 萬盛學電腦網 all rights reserved