採用顯式交易、餘額檢查、錯誤處理,確保 ACID。
USE [BankInfor]
GO
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE [dbo].[Transfer]
@inAccount INT, -- 轉入帳號
@outAccount INT, -- 轉出帳號
@amount FLOAT -- 金額
AS
BEGIN
SET NOCOUNT ON;
DECLARE @totalDeposit FLOAT;
BEGIN TRY
BEGIN TRANSACTION;
-- 鎖定轉出帳戶列 (U 鎖升級 X 鎖),防止並發超扣
SELECT @totalDeposit = total FROM Account WITH (UPDLOCK, ROWLOCK)
WHERE AccountNum = @outAccount;
IF @totalDeposit IS NULL
THROW 50001, '轉出帳戶不存在', 1;
IF @totalDeposit < @amount
THROW 50002, '餘額不足,無法轉帳', 1;
UPDATE Account SET total = total - @amount WHERE AccountNum = @outAccount;
UPDATE Account SET total = total + @amount WHERE AccountNum = @inAccount;
COMMIT TRANSACTION;
PRINT '轉帳成功!';
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
THROW; -- 重拋錯誤供上層處理
END CATCH
END
使用**單一資料表**儲存各業務代碼的當前流水值,交易內 `UPDATE` 取得鎖天然串行化,無需額外 `sp_getapplock`。
IF EXISTS (SELECT 1 FROM sysobjects WHERE id = OBJECT_ID('GetSerialNo') AND xtype = 'P')
DROP PROC GetSerialNo
GO
CREATE PROCEDURE [dbo].[GetSerialNo]
@sCode VARCHAR(50) -- 業務代碼 (如 'ORD', 'INV')
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sValue VARCHAR(16), @dToday DATETIME, @sQZ VARCHAR(50);
BEGIN TRANSACTION;
BEGIN TRY
-- 1. 取得前綴
SELECT @sQZ = sQZ FROM SerialNo WHERE sCode = @sCode;
-- 2. 鎖定並讀取當前值 (若無則插入初始值)
UPDATE SerialNo SET sValue = sValue WHERE sCode = @sCode; -- 取得 U/X 鎖
SELECT @sValue = sValue FROM SerialNo WHERE sCode = @sCode;
IF @sValue IS NULL
BEGIN
-- 首次使用:年月日(YYMMDD) + 000001
SET @sValue = CONVERT(VARCHAR(6), GETDATE(), 12) + '000001';
UPDATE SerialNo SET sValue = @sValue WHERE sCode = @sCode;
IF @@ROWCOUNT = 0 -- 並發插入競爭
INSERT SerialNo (sCode, sValue, sQZ) VALUES (@sCode, @sValue, @sQZ);
END
ELSE
BEGIN
-- 檢查日期是否跨日
SET @dToday = CONVERT(VARCHAR(6), GETDATE(), 12);
IF LEFT(@sValue, 6) = @dToday
SET @sValue = CONVERT(VARCHAR(16), CONVERT(BIGINT, @sValue) + 1);
ELSE
SET @sValue = @dToday + '000001'; -- 新一天重置
UPDATE SerialNo SET sValue = @sValue WHERE sCode = @sCode;
END
COMMIT TRANSACTION;
SELECT Result = @sQZ + @sValue; -- 回傳完整流水號
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
SELECT Result = 'Error';
END CATCH
END
CREATE TABLE SerialNo (
sCode VARCHAR(50) PRIMARY KEY, -- 業務代碼
sValue VARCHAR(16) NOT NULL, -- 當前流水值 (YYMMDD + 6位序號)
sQZ VARCHAR(50) NOT NULL -- 前綴 (如 'ORD-')
);