SQL Server 自定義函數遞歸實戰：根據 ClassID 遞歸拼接所有父節點生成 Place 路徑標識

業務場景

分類樹：C2 IT產品 → C12 數碼產品 → C18 數碼攝像機
需求：給定 ClassID = 'C18'，遞歸向上查找所有父節點，用 _ 拼接生成 Place = 'C2_C12_C18_'。

表結構：ClassTree_update(ClassID, ParentClassID, Place)

遞歸函數實現

CREATE FUNCTION dbo.F_ContactAllParentPlaceByClassID(@classID VARCHAR(15))
RETURNS VARCHAR(500)
AS
BEGIN
  DECLARE @ParentClassID VARCHAR(15), @Place VARCHAR(500);
  SET @Place = '';

  -- 獲取直接父節點
  SELECT @ParentClassID = ParentClassID 
  FROM ClassTree_update 
  WHERE ClassID = @classID;

  -- 遞歸終止條件：根節點 '000'
  IF @ParentClassID <> '000'
  BEGIN
    -- 關鍵：先遞歸獲取上層路徑，再拼接當前父節點
    SET @Place = dbo.F_ContactAllParentPlaceByClassID(@ParentClassID) 
               + @ParentClassID + '_';
  END

  RETURN @Place;
END;

創建時的「雞生蛋蛋生雞」問題

函數體內調用自身，創建時函數尚不存在會報錯。
解決：

1. 先創建一個空殼函數（返回 ''）
2. 再 ALTER FUNCTION 替換為完整遞歸邏輯

-- 步驟 1：空殼
CREATE FUNCTION dbo.F_ContactAllParentPlaceByClassID(@classID VARCHAR(15))
RETURNS VARCHAR(500) AS BEGIN RETURN ''; END;
GO

-- 步驟 2：替換為真實遞歸邏輯
ALTER FUNCTION dbo.F_ContactAllParentPlaceByClassID(@classID VARCHAR(15))
RETURNS VARCHAR(500)
AS BEGIN
  DECLARE @ParentClassID VARCHAR(15), @Place VARCHAR(500);
  SET @Place = '';
  SELECT @ParentClassID = ParentClassID FROM ClassTree_update WHERE ClassID = @classID;
  IF @ParentClassID <> '000'
    SET @Place = dbo.F_ContactAllParentPlaceByClassID(@ParentClassID) + @ParentClassID + '_';
  RETURN @Place;
END;
GO

調用驗證

SELECT dbo.F_ContactAllParentPlaceByClassID('C18') AS Place;
-- 結果：C2_C12_C18_

結合新增記錄時的 @@IDENTITY 獲取新 ClassID，即可自動生成完整 Place。

性能與替代方案

CTE 遞歸寫法（SQL 2005+ 推薦）

WITH RecursiveCTE AS (
  SELECT ClassID, ParentClassID, CAST(ClassID + '_' AS VARCHAR(500)) AS Path
  FROM ClassTree_update WHERE ClassID = 'C18'
  UNION ALL
  SELECT t.ClassID, t.ParentClassID, CAST(t.ClassID + '_' + r.Path AS VARCHAR(500))
  FROM ClassTree_update t
  JOIN RecursiveCTE r ON t.ClassID = r.ParentClassID
  WHERE t.ParentClassID <> '000'
)
SELECT Path FROM RecursiveCTE WHERE ParentClassID = '000';

最佳實踐

• 遞歸函數務必設置明確終止條件，防止無限遞歸
• 深度 > 32 層必須用 CTE 或應用層
• 高頻調用場景考慮物化路徑或觸發器維護 Place
• 新增節點時在事務中同步計算 Place，保證一致性