若不用遞歸,某些問題極難解決。例如計算指定管理員為根的子樹所有員工薪資總額。Listing 4 以 dbo.ufn_GetSubtreeSalary 完成。
CREATE FUNCTION dbo.ufn_GetSubtreeSalary
(
@mgrid AS int
)
RETURNS int
AS
BEGIN
RETURN (SELECT Salary FROM Employees WHERE empid = @mgrid) +
CASE
WHEN EXISTS (SELECT * FROM Employees WHERE mgrid = @mgrid) THEN
(SELECT SUM(dbo.ufn_GetSubtreeSalary(empid))
FROM Employees WHERE mgrid = @mgrid)
ELSE 0
END
END
函數極簡短,僅一個 RETURN 語句:取得指定管理員薪資,加上每位直屬下屬子樹薪資總和 (遞歸)。測試 Janet (empid=3) 的子樹薪資總額為 20000:
SELECT dbo.ufn_GetSubtreeSalary(3)
同理可計算子樹深度,Listing 5 所示。
CREATE FUNCTION dbo.ufn_GetSubtreeDepth
(
@mgrid AS int
)
RETURNS int
AS
BEGIN
RETURN CASE
WHEN EXISTS (SELECT * FROM Employees WHERE mgrid = @mgrid)
THEN 1 + (SELECT MAX(dbo.ufn_GetSubtreeDepth(empid))
FROM Employees WHERE mgrid = @mgrid)
WHEN EXISTS (SELECT * FROM Employees WHERE empid = @mgrid)
THEN 1
ELSE NULL
END
END
邏輯:若有下屬,返回 1 + 直屬下屬子樹最大深度 (遞歸);若無下屬但存在,深度為 1;不存在返回 NULL。測試 Nancy (empid=1,最高層) 整棵樹深度為 5:
SELECT dbo.ufn_GetSubtreeDepth(1)