會員積分系統是電商、O2O 平台核心模組。本文以真實專案 sp_GM_JF_AddScore 為例，深度解析積分獲取儲存過程的核心邏輯：防重複領取機制、明細與總帳雙寫入、樂觀鎖並發控制、事務原子性保障，供架構師與後端工程師參考借鑒。
| 表名 | 用途 | 關鍵欄位 |
|---|---|---|
GM_JF | 會員總積分帳戶 | ACCOUNT_ID, JF_AMOUNT(總積分), TTL_JF_AMOUNT(可用積分), Version(樂觀鎖) |
GM_JF_DETAIL | 積分變動明細流水 | ACCOUNT_ID, JF_CategoryNumber, CARD_NUM, HQ_JF_AMOUNT, USE_DATE, USE_DESC |
GM_JF_ACTION_RULES | 積分動作規則定義 | AN_CategoryNumber, RepetitionsCycle(周期天數), RepetitionsFrequency(周期內最大次數) |
GM_JF_GOODS_RULES | 積分商品兌換規則 | （本範例未直接使用） |
sp_GM_JF_AddScoreALTER PROCEDURE [dbo].[sp_GM_JF_AddScore]
@ACCOUNT_ID varchar(30), -- 會員帳號
@JF_CategoryNumber varchar(15), -- 動作類別代碼
@CARD_NUM int, -- 關聯卡號/訂單號
@HQ_JF_AMOUNT int, -- 本次獲得積分數
@State varchar(16), -- 業務狀態標記
@USE_DESC varchar(400), -- 描述備註
@StatusCode int OUTPUT -- 0失敗 1成功 2重複領取
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@repetitionsCycle float = 0, -- 規則周期(天)
@repetitionsCycle_sec int = 0, -- 規則周期(秒)
@repetitionsFrequency int = 0, -- 周期內允許最大次數
@realFrequency int = 0, -- 實際已領取次數
@USE_DATE datetime = GETDATE();
/* ---------- 1. 讀取規則判斷是否允許重複領取 ---------- */
SELECT TOP 1
@repetitionsCycle = RepetitionsCycle,
@repetitionsFrequency = RepetitionsFrequency
FROM GM_JF_ACTION_RULES
WHERE AN_CategoryNumber = @JF_CategoryNumber;
IF (@repetitionsCycle < 1) -- 秒級周期
BEGIN
SET @repetitionsCycle_sec = (@repetitionsCycle - 1) * 24 * 60 * 60;
SELECT @realFrequency = COUNT(1)
FROM GM_JF_DETAIL
WHERE ACCOUNT_ID = @ACCOUNT_ID
AND JF_CategoryNumber = @JF_CategoryNumber
AND USE_DATE <= @USE_DATE
AND USE_DATE >= CONVERT(varchar(19), DATEADD(SECOND, -@repetitionsCycle_sec, @USE_DATE), 120);
END
ELSE -- 天級周期
BEGIN
SELECT @realFrequency = COUNT(1)
FROM GM_JF_DETAIL
WHERE ACCOUNT_ID = @ACCOUNT_ID
AND JF_CategoryNumber = @JF_CategoryNumber
AND USE_DATE <= @USE_DATE
AND USE_DATE >= CONVERT(varchar(10), DATEADD(DAY, -(@repetitionsCycle - 1), @USE_DATE), 120);
END
IF (@realFrequency >= @repetitionsFrequency)
BEGIN
SET @StatusCode = 2; -- 重複領取
RETURN 2;
END
/* ---------- 2. 事務處理：寫明細 + 更新總帳 ---------- */
DECLARE @count int = 0;
DECLARE @temp_table TABLE (
ACCOUNT_ID varchar(30),
JF_AMOUNT decimal(16,2),
TTL_JF_AMOUNT decimal(16,2),
Last_Update_Time datetime,
[Version] int
);
BEGIN TRAN;
-- 2.1 寫入明細流水
INSERT INTO GM_JF_DETAIL (ACCOUNT_ID, JF_CategoryNumber, CARD_NUM, HQ_JF_AMOUNT, [State], USE_DESC)
VALUES (@ACCOUNT_ID, @JF_CategoryNumber, @CARD_NUM, @HQ_JF_AMOUNT, @State, @USE_DESC);
-- 2.2 讀取總帳現狀至表變數（避免長時間鎖表）
INSERT INTO @temp_table
SELECT ACCOUNT_ID, JF_AMOUNT, TTL_JF_AMOUNT, Last_Update_Time, [Version]
FROM GM_JF WHERE ACCOUNT_ID = @ACCOUNT_ID;
SELECT @count = COUNT(1) FROM @temp_table;
IF (@count = 0) -- 首次獲得積分，建立總帳
BEGIN
INSERT INTO GM_JF (ACCOUNT_ID, JF_AMOUNT, TTL_JF_AMOUNT)
VALUES (@ACCOUNT_ID, @HQ_JF_AMOUNT, @HQ_JF_AMOUNT);
END
ELSE -- 更新既有總帳，樂觀鎖檢查 Version
BEGIN
DECLARE @JF_AMOUNT decimal(16,2), @TTL_JF_AMOUNT decimal(16,2), @Version int;
SELECT @JF_AMOUNT = JF_AMOUNT, @TTL_JF_AMOUNT = TTL_JF_AMOUNT, @Version = [Version]
FROM @temp_table WHERE ACCOUNT_ID = @ACCOUNT_ID;
UPDATE GM_JF
SET JF_AMOUNT = @JF_AMOUNT + @HQ_JF_AMOUNT,
TTL_JF_AMOUNT = @TTL_JF_AMOUNT + @HQ_JF_AMOUNT,
Last_Update_Time = GETDATE(),
[Version] = @Version + 1
WHERE ACCOUNT_ID = @ACCOUNT_ID AND [Version] = @Version; -- 關鍵：樂觀鎖條件
IF (@@ROWCOUNT = 0) -- 版本號不符，並發衝突
BEGIN
ROLLBACK TRAN;
SET @StatusCode = 0;
RETURN 0;
END
END
COMMIT TRAN;
SET @StatusCode = 1;
IF (@@ERROR <> 0)
BEGIN
SET @StatusCode = 0;
ROLLBACK TRAN;
END
ENDRepetitionsCycle（週期長度）與 RepetitionsFrequency（最大次數）GM_JF_DETAIL 保留完整審計軌跡，支援對帳、爭議處理GM_JF 提供高效餘額查詢，避免每次 SUM 明細表Version 欄位WHERE Version = @OldVersion，@@ROWCOUNT = 0 表示衝突SELECT ... FOR UPDATE），減少鎖持有時間，提升吞吐@temp_table 僅存在於記憶體/執行緒區域，無鎖競爭DECLARE @StatusCode int = 1;
EXEC sp_GM_JF_AddScore
'admin', 'AN_JF_001_001', 1, 5, 0, '', @StatusCode OUTPUT;
PRINT @StatusCode; -- 1=成功 2=重複 0=失敗sp_GM_JF_DeductScore，邏輯鏡像，需檢查 TTL_JF_AMOUNT >= 扣減量OperatorID、ClientIP、TraceID 欄位ACCOUNT_ID + USE_DATE 分區；總帳表建立 ACCOUNT_ID 唯一索引BizSeqNo，明細表建立唯一索引防重複提交此儲存過程展示了企業級積分系統核心寫入路徑的最佳實踐：規則驅動、雙帳一致、樂觀並發、事務保障。理解並靈活套用這些模式，能顯著提升金融級業務模組的可靠性與擴展性。
