22,209
社区成员
发帖
与我相关
我的任务
分享
------ 测试表开始 -----
USE tempdb
GO
IF OBJECT_ID('user_data') IS NOT NULL DROP TABLE user_data
GO
CREATE TABLE user_data(
userId INT IDENTITY(1,1) PRIMARY KEY
,account_money DECIMAL(10,2) NOT NULL DEFAULT(0)
,account_integral1 INT NOT NULL DEFAULT(0)
,account_integral2 INT NOT NULL DEFAULT(0)
)
GO
INSERT INTO user_data(account_money,account_integral1,account_integral2)
VALUES(100.0,300,800)
------ 测试表结束 -----
--
--1. 增加一个支付顺序列
ALTER TABLE user_data ADD payOrder CHAR(3) NOT NULL DEFAULT('12m')
--2. 创建支付的存储过程
IF OBJECT_ID('Proc_Pay') IS NOT NULL
DROP PROC Proc_Pay
GO
CREATE PROC Proc_Pay(
@userId INT,
@amount DECIMAL(10,2) --支付金额
)
AS
BEGIN
SET NOCOUNT ON
IF EXISTS(SELECT 1 FROM user_data AS ud
WHERE
ud.userId=@userId
AND (ud.account_money+(ud.account_integral1+ud.account_integral2)/100)<@amount
)
BEGIN
RAISERROR ('用户余额不足,支付失败', 16 ,1);
RETURN;
END
DECLARE @account_money DECIMAL(10,1),@account_integral1 INT,@account_integral2 INT,@payOrder CHAR(3)
BEGIN TRAN
BEGIN TRY
SELECT @account_money=account_money
,@account_integral1=account_integral1
,@account_integral2=account_integral2
,@payOrder=payOrder
FROM user_data AS ud
WHERE ud.userId=@userId
IF @payOrder='12m'
BEGIN
IF @account_integral1/100>=@amount
BEGIN
UPDATE user_data
SET
account_integral1 = account_integral1 - @amount*100
WHERE userId=@userId
END
ELSE IF (@account_integral1+@account_integral2)/100>=@amount
BEGIN
UPDATE user_data
SET
account_integral1 = 0
,account_integral2 = account_integral1+ account_integral2 - @amount*100
WHERE userId=@userId
END
ELSE
BEGIN
UPDATE user_data
SET
account_integral1 = 0
,account_integral2 =0
,account_money = account_money + (account_integral1+ account_integral2)/100 - @amount
WHERE userId=@userId
END
END
ELSE IF @payOrder='1m2'
BEGIN
print '--自己补充'
END
ELSE IF @payOrder='21m'
BEGIN
print '--自己补充'
END
ELSE IF @payOrder='2m1'
BEGIN
print '--自己补充'
END
ELSE IF @payOrder='m12'
BEGIN
print '--自己补充'
END
ELSE IF @payOrder='m21'
BEGIN
print '--自己补充'
END
COMMIT TRAN
END TRY
BEGIN CATCH
DECLARE @errMsg NVARCHAR(500)
SET @errMsg='支付出错:'+ ERROR_MESSAGE()
RAISERROR (@errMsg, 16 ,1);
ROLLBACK TRAN
END CATCH
END
GO
EXEC Proc_Pay @userId=1,@amount=23.5
SELECT * FROM user_data AS ud
/*
userId account_money account_integral1 account_integral2 payOrder
----------- --------------------------------------- ----------------- ----------------- --------
1 87.50 0 0 12m
*/