以使用者定義函數取代游標：兩種實務模式詳解

前言

游標處理逐列邏輯效能差、程式碼複雜。SQL Server 2000 支援純量函數與 `SUM(expression)` 彈性，可將計算邏輯封裝於函數，在集合式查詢中呼叫，大幅簡化程式碼並提升效能。

模式一：UPDATE 中引用函數計算衍生欄位

情境：更新表 A 的欄位，需依據表 B、C 的關聯資料計算。

步驟 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
    FROM TABLETASKPHASE WHERE TASKPHASEID = @TASKPHASEID
    SELECT @HOUR = ISNULL(TASKTIME,0) FROM TABLETASK WHERE TASKID = @TASKID
    SET @RETURN = @HOUR * @PERCENT
    RETURN @RETURN
END

步驟 2：在 UPDATE 中直接呼叫

UPDATE TABLEFMECA
SET Cvalue_M = ISNULL(MODERATE,0) * ISNULL(FMERATE,0) * ISNULL(B.BASFAILURERATE,0) * dbo.FUN_GETTIME(C.ID)
FROM TABLEFMECA
JOIN TABLERELATION B ON ...
JOIN TABLETASKPHASE C ON ...
WHERE ...

優點：避免游標迴圈、SQL 最佳化器可產生集合式執行計畫、程式碼可讀性高。

模式二：彙總查詢中使用函數進行複雜計算

情境：依多重條件查找費率，再乘以數量彙總，無法預存中間值。

函數定義

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 PARTID = @PARTID AND ENID = @ENID AND SOURCEID = @SOURCEID
      AND QUALITYID = @QUALITYID
      AND ( (MINCOUNT <= @COUNT AND MAXCOUNT >= @COUNT) OR (@COUNT > MAXCOUNT) )
    -- 後續計算邏輯...
    RETURN @RATE
END

彙總查詢呼叫

SELECT SUM(dbo.FUN_RATE(PARTID, ENID, SOURCEID, QUALITYID, QTY))
FROM TRANSACTION_TABLE
WHERE ...

關鍵點：`SUM()` 的 `expression` 參數接受函數呼叫，將逐列計算邏輯封裝於函數，主查詢保持集合式風格。

效能與限制提醒

• 純量函數逐列執行，大量資料時仍有函數呼叫開銷
• 避免在函數內進行複雜查詢或資料存取
• SQL Server 2005+ 可改用 `CROSS APPLY` / 內嵌資料表值函數獲得更好效能
• 函數不可執行動態 SQL、暫存表操作、非確定性函數（如 `GETDATE()`）