您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 純量函數兩大實戰模式：UPDATE 內嵌呼叫與彙總運算重構

編輯：關於SqlServer

前言

在 T-SQL 開發中，純量函數 常被誤解為效能殺手。但若善用其「封裝邏輯、支援內嵌於查詢」的特性，能優雅解決兩類棘手問題：UPDATE 中需引用複雜計算邏輯，以及 彙總查詢中需逐列計算後再加總。本文透過兩個實戰案例深度解析。

模式一：UPDATE 陳述式中透過函數實現跨表計算

場景

需根據 TABLETASKPHASE 的工作進度百分比，計算 TABLEFMECA 中的關鍵度數值 Cvalue_M，並彙總更新至 TABLERELATION.CRITICALITY。因 UPDATE 不支援直接呼叫預存程序，傳統做法使用游標，效能差且程式碼冗長。

解決方案：純量函數 + UPDATE ... FROM

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
    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
         JOIN TABLERELATION B ON B.ID = @ROID
         JOIN TABLETASKPHASE C ON C.ID = TABLEFMECA.TASKPHASEID
    WHERE TABLEFMECA.ROID = @ROID;
    
    SELECT @CA = SUM(ISNULL(Cvalue_M, 0)) FROM TABLEFMECA WHERE ROID = @ROID;
    
    UPDATE TABLERELATION SET CRITICALITY = @CA WHERE ID = @ROID;
END

優勢分析

邏輯封裝於函數，預存程序碼簡潔易維護
集合式操作，避免游標逐列處理的效能損耗
函數可被其他查詢重用，符合 DRY 原則

模式二：彙總查詢中使用函數表達式實現逐列計算後加總

場景

需根據零件、環境、資料來源、品質等級、數量等多維參數查詢失效率，再乘以工作百分比加總。傳統做法需游標逐列計算並累加中間值，但 SQL Server 2000+ 支援 SUM(函數運算式) 語法，可直接在彙總中完成。

核心語法

SUM ( [ ALL | DISTINCT ] expression )
-- expression 可為：常數、欄位、函數、或算術/位元/字串運算式的任意組合

實作範例

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. 彙總呼叫預存程序

CREATE PROC PROC_FAULTRATE
    @PARTID INTEGER, @QUALITYID INTEGER, @SOURCEID INTEGER,
    @COUNT INTEGER, @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