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

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

四、查詢管理鏈

　　至此為止，前面的所有例子都是用只返回一個值的數值型用戶定義函數（UDF）。現在，我們來看看如何使用返回表的UDF。這類函數的返回值是一個記錄集或表（即，可用於FROM子句）。例如，在處理層次結構的數據時，一個常見的需求是返回從指定管理員開始的整棵子樹。


LISTING 6：獲得整棵子樹

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


　　Listing 6顯示了創建ufn_GetSubtree函數的腳本。注意返回的表與原來的Employees表有著相同的結構，但增加了兩個列：lvl和path。lvl列保存子樹中從0開始計算的層次值，path列保存“.id0.id1...idn”格式的雇員管理路徑。這個字符串包含從子樹中最頂層的雇員開始、到當前的雇員為止的所有雇員ID。路徑中所有雇員ID的前面和後面都是句點符號。

　　path列允許對本例中dbo.ufn_GetSubtree函數返回的子樹的行進行適當的排序。對於特定的雇員來說，由於其所有下屬的path值都以他的管理員的path值為前綴，排序中這些值能夠正確地出現在它們的管理員之後。函數先把屬於管理員的行插入@tree表變量（屬於管理員的行在函數參數中指定）。接下來，函數開始一個循環，只要前一次插入操作有結果，迭代就一直進行。循環中的代碼把前一次插入操作的直接下屬追加到@tree表變量。上次插入的雇員的級別可以通過在@lvl變量中跟蹤雇員在子樹中的當前級別得到。 綠色c hinaip ower. comCXdiE

　　現在我們可以測試一下ufn_GetSubtree函數。要得到Andrew（empid是2)以及他的所有下屬（包括各個級別）的詳細信息，查詢命令如下：


SELECT * FROM ufn_GetSubtree(2)
ORDER BY path


　　要得到Employees表中所有雇員的層次圖，查詢命令如下：


SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path


綠色c hinaip ower. comCXdiE

綠色c hinaip ower. comCXdiE

　　圖一顯示了上述查詢命令的結果。綠色c hinaip ower. comCXdiE

關鍵詞：