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

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

二、查找特定級別的管理員

　　現在我們來看第一個例子——查找特定級別的管理員。例如，我們要提取比指定雇員高兩級的管理員。這可以用兩種方法實現：使用遞歸，或者使用循環。首先我們來看通過遞歸實現的方案。本質上，遞歸意味著一個過程調用它自己。為了避免出現過程無限地調用自己的情況，遞歸過程必須提供一個合適的終止條件。如果運用得當，遞歸可以輕松地解決在采用其他方法時很難解決的問題。然而，在許多情況下，遞歸都可以用循環來替代。通常，使用循環是一種更明智的選擇，因為遞歸調用比循環消耗的資源更多。

　　Listing 2顯示了dbo.ufn_GetAncestor函數，它利用遞歸返回合適的管理員。dbo.ufn_GetAncestor有兩個參數：@empid是雇員ID，@lvl指定了管理員在雇員之上的級別數。函數首先進行一些安全方面的檢查，確保調用參數的合法性。如果參數不合法，則函數返回NULL。接下來，函數進行遞歸終止條件檢查：如果表示管理員層次的參數是0，函數直接返回另一個表示雇員ID的參數。最後，函數執行遞歸調用，非常簡單——函數調用自己，傳遞的參數是指定雇員的管理員以及級別數字減1。實際上，函數是在查找指定雇員的管理員的n-1級的上級管理員，其中n是前面指定的級別數字。當級別數字減到0或者不能找到更高級別的管理員時，遞歸過程立即終止。在後面這種情況下，函數返回NULL，表示請求未能滿足。


LISTING 2：通過遞歸查找管理員

CREATE FUNCTION dbo.ufn_GetAncestor
(
@empid AS int,
@lvl AS int = 1 -- 高於指定雇員的級別數
)
RETURNS int
AS
BEGIN

IF @lvl IS NULL or @empid IS NULL or @lvl < 0
RETURN NULL

IF @lvl = 0
RETURN @empid

RETURN dbo.ufn_GetAncestor(
(SELECT mgrid FROM Employees WHERE empid = @empid),
@lvl -1)

END


　　遞歸有兩個局限。第一個局限的影響面比較廣，對大多數環境來說這個問題都存在；第二個局限是sql server特有的問題。影響較廣的局限是指遞歸需要消耗大量的資源。每一個函數占有一個包含函數代碼和函數變量的內存結構。隨著遞歸調用的進行，多個函數的副本會被激活，從而消耗大量的資源。www.想自殺iTbulo.comw3n05

　　SQL Server特有的局限是指，SQL Server的設計規范限制調用嵌套層次不超過32。這個限制賦予遞歸調用一定的彈性，同時避免了因代碼質量不高可能導致的無限遞歸調用——例如，遞歸過程不帶終止條件檢查。因此，上面的函數只支持對參數中指定雇員之上32級之內的管理員的查詢。在現實的組織結構中，級別數量超過32的情況很罕見。但是，假設組織結構超過了32層，我們必須提供一種不涉及遞歸調用的方案。在本文中，我利用循環技術提供了一個不帶遞歸調用的簡單方案。Listing 3顯示了修改後的函數。在每一次迭代中，我提取出雇員的管理員，同時縮減@lvl參數。一旦當前的級別已經減到了0，則函數完成任務。


LISTING 3：通過循環查找管理員


CREATE FUNCTION dbo.ufn_GetAncestor2
(
@empid AS int,
@lvl AS int = 1 --高於指定雇員的級別數
)
RETURNS int
AS
BEGIN

IF @lvl IS NULL or @empid IS NULL or @lvl < 0
RETURN NULL

DECLARE @mgrid AS int
SET @mgrid = @empid

WHILE @lvl > 0 AND @mgrid IS NOT NULL
SELECT @mgrid = mgrid, @lvl = @lvl - 1
FROM Employees WHERE empid = @mgrid

RETURN @mgrid

END



　　現在你可以對上述任意函數進行測試。如果提供的參數相同，則兩個函數返回的結果也相同。例如，要提取比David（他的empid是11）高兩級的管理員，你可以執行如下命令：


SELECT dbo.ufn_GetAncestor(11, 2)


　　這個命令返回Janet的雇員ID，即3。如果要找出比David高兩級的管理員的所有信息，可以執行如下命令：


SELECT * FROM Employees WHERE empid =
dbo.ufn_GetAncestor(11, 2)




　　要找出所有的雇員以及比他們高兩級的管理員，可以執行如下命令：


SELECT E.empname AS employee, A.empname AS ancestor
FROM Employees AS E LEFT OUTER JOIN Employees AS A
ON A.empid = dbo.ufn_GetAncestor(E.empid, 2)


　　這個查詢在Employees表和它自身之間執行自我連接。連接條件保證了對每一個雇員和比他高兩級的管理員進行匹配。在這裡我用了一個LEFT OUTER JOIN，保證所有雇員——包括那些沒有比他高兩級的管理員的雇員——都包含在查詢結果中。 www.想自殺iTbulo.comw3n05

關鍵詞：