SQL Server 使用者定義函數 (UDF) 替代游標的兩大經典模式

前言：為什麼要用函數替代游標？

游標 (CURSOR) 屬於逐列處理 (RBAR)，在集合導向的 SQL Server 中效能極差。使用者定義函數 (UDF) 結合集合操作，可將複雜邏輯封裝、在單一陳述式中完成運算，大幅提升吞吐量。本文介紹兩大實戰模式。

模式一：UPDATE 中引用標量函數進行跨表計算

場景：更新 TABLEFMECA 表的 Cvalue_M 欄位，需依 TASKPHASEID 關聯 TABLETASKPHASE、TABLERELATION、TABLETASK 多表查詢計算工時比例。

1. 建立標量函數

CREATE FUNCTION dbo.FUN_GETTIME (@TASKPHASEID INT)
RETURNS FLOAT
AS
BEGIN
    DECLARE @TASKID INT, @HOUR FLOAT, @PERCENT FLOAT, @RETURN FLOAT;
    
    IF @TASKPHASEID IS NULL RETURN 0.0;
    
    SELECT @TASKID = TASKID, @PERCENT = ISNULL(WORKPERCENT, 0) / 100.0
    FROM TABLETASKPHASE WHERE ID = @TASKPHASEID;
    
    SELECT @HOUR = ISNULL(TASKTIME, 0) FROM TABLETASK WHERE ID = @TASKID;
    
    SET @RETURN = @HOUR * @PERCENT;
    RETURN @RETURN;
END;

2. 預存程序中單一 UPDATE 完成批次計算

CREATE PROCEDURE dbo.PROC_CALCCA @ROID INT
AS
BEGIN
    UPDATE TABLEFMECA
    SET Cvalue_M = ISNULL(MODERATE, 0) * ISNULL(FMERATE, 0) * ISNULL(B.BASFAILURERATE, 0) * dbo.FUN_GETTIME(C.ID)
    FROM TABLEFMECA
    INNER JOIN TABLERELATION B ON B.ID = @ROID
    INNER JOIN TABLETASKPHASE C ON TABLEFMECA.TASKPHASEID = C.ID
    WHERE TABLEFMECA.ROID = @ROID;

    DECLARE @CA FLOAT;
    SELECT @CA = SUM(ISNULL(Cvalue_M, 0)) FROM TABLEFMECA WHERE ROID = @ROID;
    
    UPDATE TABLERELATION SET CRITICALITY = @CA WHERE ID = @ROID;
END;

優勢：單一掃描完成多表關聯計算，避免游標循環的上下文切換開銷。

模式二：聚合函數中嵌入標量函數實現條件式加總

場景：依 PARTID、ENID、SOURCEID、QUALITYID、COUNT 多條件查詢失效率，再乘以 WORKPERCENT 加總。

1. 建立複雜計算函數

CREATE FUNCTION dbo.FUN_RATE (
    @PARTID INT, @ENID INT, @SOURCEID INT, @QUALITYID INT, @COUNT INT
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @QXS FLOAT, @G FLOAT, @RATE FLOAT;
    
    IF @ENID IS NULL OR @PARTID IS NULL OR @SOURCEID IS NULL OR @QUALITYID IS NULL
        RETURN 0.0;
    
    SELECT @QXS = ISNULL(XS, 0) FROM TABLEQUALITY WHERE ID = @QUALITYID;
    
    SELECT @G = ISNULL(FRATE_G, 0)
    FROM TABLEFAILURERATE
    WHERE SUBKINDID = @PARTID
      AND ENID = @ENID
      AND DATASOURCEID = @SOURCEID
      AND ( (ISNULL(MINCOUNT, 0) <= ISNULL(@COUNT, 0) AND ISNULL(MAXCOUNT, 0) >= ISNULL(@COUNT, 0))
         OR ISNULL(@COUNT, 0) > ISNULL(MAXCOUNT, 0) );
    
    SET @RATE = ISNULL(@QXS * @G, 0);
    RETURN @RATE;
END;

2. 預存程序中 SUM() 直接呼叫函數

CREATE PROCEDURE dbo.PROC_FAULTRATE
    @PARTID INT, @QUALITYID INT, @SOURCEID INT, @COUNT INT,
    @ROID INT, @GRADE INT, @RATE FLOAT = 0 OUTPUT
AS
BEGIN
    DECLARE @TASKID INT;
    SET @RATE = 0.0;
    
    SELECT @TASKID = ISNULL(TASKPROID, -1)
    FROM TABLERELATION
    WHERE ID = (SELECT PID FROM TABLERELATION WHERE ID = @ROID);
    
    IF @TASKID = -1 OR @GRADE = 1 BEGIN SET @RATE = 0; RETURN; END;
    
    SELECT @RATE = SUM( dbo.FUN_RATE(@PARTID, ENID, @SOURCEID, @QUALITYID, @COUNT) * ISNULL(WORKPERCENT, 0) / 100.0 )
    FROM TABLETASKPHASE
    WHERE TASKID = @TASKID;
END;

關鍵點：SUM( dbo.FUN_RATE(...) * ... ) 讓聚合與逐列計算在單一查詢中完成，SQL Server 8.0+ 甚至可內聯標量 UDF (Inline Scalar UDF) 進一步消除函數呼叫開銷。

進階：表值函數 (TVF) — 替代游標的終極武器

當需要回傳多列多行、或參與 JOIN 時，內嵌表值函數 (Inline TVF) 是最佳選擇：

CREATE FUNCTION dbo.GetTaskPhasesByTaskID (@TASKID INT)
RETURNS TABLE
AS RETURN
    SELECT ID, TASKID, WORKPERCENT
    FROM TABLETASKPHASE
    WHERE TASKID = @TASKID; -- 單一 SELECT，優化器可內聯展開

使用方式如同檢視表：

SELECT tp.*, f.ID AS FaultID
FROM TABLETASK t
CROSS APPLY dbo.GetTaskPhasesByTaskID(t.ID) tp
LEFT JOIN FAULTS f ON f.TASKPHASEID = tp.ID;

效能對比與選擇指南

總結

「能用單一陳述式解決，就不用游標。」將商業邏輯封裝於 UDF/TVF，搭配集合操作，是 SQL Server 高效能開發的核心心法。SQL Server 2019+ 的標量 UDF 內聯 功能更讓此模式如虎添翼。