萬盛學電腦網

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

UDF 處理層次資料 (二):尋找指定層級的祖先 (遞歸與迴圈)

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

第一個範例:尋找比指定員工高 N 層的管理員。可用遞歸或迴圈。先看遞歸實現 (Listing 2)。

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

函數先檢查參數合法性,再以 @lvl=0 為終止條件,遞歸調用自身,每次向上一層。遞歸有兩大限制:

  1. 消耗大量資源 (每層調用佔用記憶體)。
  2. SQL Server 限制嵌套層級不超過 32 層,超過則失效。

為突破 32 層限制,提供迴圈版本 (Listing 3)。

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 (empid=3)。
  • 取得該管理員完整資訊:SELECT * FROM Employees WHERE empid = dbo.ufn_GetAncestor(11, 2)。
  • 列出所有員工及其高兩層管理員:使用 LEFT OUTER JOIN 確保無管理員的員工也被列出。
copyright © 萬盛學電腦網 all rights reserved