前面範例皆為標量 UDF,現在介紹返回表值的 UDF。處理層次資料時,常見需求是返回指定管理員為根的整棵子樹。Listing 6 顯示建立 ufn_GetSubtree 的腳本。
CREATE FUNCTION ufn_GetSubtree
(
@mgrid 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,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @lvl AS int, @path AS varchar(900)
SELECT @lvl = 0, @path = '.'
INSERT INTO @tree
SELECT empid, mgrid, empname, salary,
@lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @mgrid
WHILE @@ROWCOUNT > 0
BEGIN
SET @lvl = @lvl + 1
INSERT INTO @tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @tree AS T
ON E.mgrid = T.empid AND T.lvl = @lvl - 1
END
RETURN
END
返回表結構同 Employees,額外增加 lvl (層級,從 0 起算) 和 path (格式 .id0.id1...idn.,包含從根到當前節點的所有員工 ID,前後以點號包圍)。path 可讓子樹行正確排序:下屬的 path 以管理員的 path 為前綴,排序時自然出現在管理員之後。
取得 Andrew (empid=2) 及其所有下屬:
SELECT * FROM ufn_GetSubtree(2) ORDER BY path
取得全員層次圖:
SELECT REPLICATE(' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1) ORDER BY path
圖一顯示查詢結果的層次結構視覺化。