SQL Server 函數替代游標的兩種實用技巧

在 SQL Server 中，UPDATE 語句無法直接呼叫預存程序，但常需根據更新的欄位進行計算。傳統做法使用游標，效能較差。本文示範如何利用使用者定義函數（UDF）搭配預存程序，優雅地解決此問題，提升效能與可維護性。

情境說明

需根據任務階段的工時與完成百分比，計算實際投入工時，並更新至 FMECA 表中，最後彙總寫入關聯表的關鍵度欄位。

方案一：標量函數計算單一任務工時

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

方案二：在預存程序中集合呼叫函數進行批次更新

CREATE PROCEDURE [dbo].[PROC_CALCCA] @ROID INT
AS
BEGIN
    DECLARE @CA FLOAT;
    UPDATE TABLEFMECA
    SET Cvalue_M = ISNULL(MODERATE, 0) * ISNULL(FMERATE, 0) * ISNULL(B.BASFAILURERATE, 0) * [dbo].[FUN_GETTIME](C.ID)
    FROM TABLEFMECA, TABLERELATION B, TABLETASKPHASE C
    WHERE ROID = @ROID AND TASKPHASEID = C.ID AND B.ID = @ROID;

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

優勢分析

• 集合式操作： 避免逐行游標循環，大幅降低資料庫引擎負擔。
• 程式碼模組化： 計算邏輯封裝於函數，易於測試、除錯與重用。
• 效能提升： 函數執行計畫可被快取，減少編譯開銷。

注意：若計算邏輯極其複雜或涉及大量資料存取，請評估改用內嵌表值函數或直接在預存程序中以 JOIN 方式完成，以避免標量函數逐行執行的效能瓶頸。