22,206
社区成员
发帖
与我相关
我的任务
分享
ALTER PROC pro_ShoppingByEMoney
(
@OrderID char(18),
@UserName varchar(20),
@FullDate datetime,
@FullIP varchar(20),
@OrderMoney money,
@ret int out
)
AS
declare @GoodsID char(12)
declare @CardNo char(12)
declare @SourceType int
declare @AEMoneyBuy decimal(19,4)
declare @AEMoneyPar decimal(19,4)
declare @AEMoneyBuyM decimal(19,4)
declare @AEMoneyParM decimal(19,4)
declare @BEMoneyBuy decimal(19,4)
declare @BEMoneyPar decimal(19,4)
declare @AddFlag int
select @GoodsID=''
select @CardNo=''
select @SourceType=3 -- 购物消费E元
select @BEMoneyBuy=0
select @BEMoneyPar=0
select @AddFlag=1 -- E元消减
select @ret=0 -- 不能使用E元支付
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
--select * from memberEMoney where username='estore'
--commit transaction
-- check user EMoney and set AEMoneyBuy AEMoneyPar
-- 确定订单有效 start add by Lisa on 2005-02-03
declare @OrderPayCount int
declare @OPayMoney money
set @OPayMoney = 0
set @OrderPayCount = 1
select @OrderPayCount=count(OrderBankid), @OPayMoney=isnull(sum(ordermoney),0) from OrderMst where OrderbankID=@OrderID and (OrderStatus=0 or OrderStatus=1)
-- 防止重复支付 start
declare @OrderCount int
set @OrderCount = 0
select @OrderCount=count(orderid) from fullrecord where OrderID=@OrderID
-- 防止重复支付
if (@OrderCount=0 and @OrderPayCount>0 and @OPayMoney=@OrderMoney)
begin
select @AEMoneyBuyM=AEMoneyBuy, @AEMoneyParM=AEMoneyPar from memberEMoney where username=@UserName
-- 如果购买的A类E元大于等于订单金额,则消减购买的A类E元
if @AEMoneyBuyM>=@OrderMoney
begin
set @AEMoneyBuy = @OrderMoney
set @AEMoneyPar = 0
set @AEMoneyBuyM = @AEMoneyBuyM - @OrderMoney
select @ret=1 -- 使用购买的A类E元支付
end
else
begin
-- 如果购买的A类E元大小于订单金额,但是A类E元总数大于等于订单金额,则优先消减购买的A类E元,其余部分消减赠送的A类E元
if (@AEMoneyBuyM+@AEMoneyParM)>=@OrderMoney
begin
set @AEMoneyBuy = @AEMoneyBuyM
set @AEMoneyPar = @OrderMoney-@AEMoneyBuyM
set @AEMoneyParM = @AEMoneyBuyM+@AEMoneyParM-@OrderMoney
set @AEMoneyBuyM = 0
select @ret=2 -- 使用A类E元支付
end
else
begin
select @ret=0 -- 不能使用E元支付
end
end
-- if enoght
if @ret>0
begin
-- fullRecord
-- 如果没有下面exec则执行正常
exec usp_FullRecordInsert @GoodsID, @OrderID, @CardNo, @UserName, @FullDate, @FullIP, @SourceType, @AEMoneyBuy, @AEMoneyPar, @BEMoneyBuy, @BEMoneyPar, @AddFlag
---
-- update user EMoney
update memberEMoney set AEMoneyBuy=@AEMoneyBuyM, AEMoneyPar=@AEMoneyParM, lastdate=getdate() where username=@UserName
if @@error>0
begin
rollback transaction
set @ret=-1
end
else
begin
commit transaction
end
end
--commit transaction
end
--commit transaction
--SET TRANSACTION ISOLATION LEVEL READ COMMITTED
--print @ret
return @ret
-- else return
exec usp_FullRecordInsert @GoodsID, @OrderID, @CardNo, @UserName, @FullDate, @FullIP, @SourceType, @AEMoneyBuy, @AEMoneyPar, @BEMoneyBuy, @BEMoneyPar, @AddFlag
--这里也需要判断@@error-------------------
if @@error>0
begin
rollback transaction
set @ret=-1
end
if @ret>0
begin
-- fullRecord
-- 如果没有下面exec则执行正常
exec usp_FullRecordInsert @GoodsID, @OrderID, @CardNo, @UserName, @FullDate, @FullIP, @SourceType, @AEMoneyBuy, @AEMoneyPar, @BEMoneyBuy, @BEMoneyPar, @AddFlag
---
-- update user EMoney
update memberEMoney set AEMoneyBuy=@AEMoneyBuyM, AEMoneyPar=@AEMoneyParM, lastdate=getdate() where username=@UserName
if @@error>0
begin
rollback transaction
set @ret=-1
end
else
begin
commit transaction
end
end
--应该加这一句
else
begin
rollback transaction
end
看了一下你的结构,有一点缺陷。
if @ret>0
begin
-- fullRecord
-- 如果没有下面exec则执行正常
exec usp_FullRecordInsert @GoodsID, @OrderID, @CardNo, @UserName, @FullDate, @FullIP, @SourceType, @AEMoneyBuy, @AEMoneyPar, @BEMoneyBuy, @BEMoneyPar, @AddFlag [/color]
---
-- update user EMoney
update memberEMoney set AEMoneyBuy=@AEMoneyBuyM, AEMoneyPar=@AEMoneyParM, lastdate=getdate() where ame=@UserName
if @@error>0
begin
rollback transaction
set @ret=-1
end
else
begin
commit transaction
end
end
--commit transaction
end
也就是@ret>0的时候,会执行下面的对事务的提交或者回滚。但是当@ret不大于0的时候呢?下面的是不是就不执行了?按照你前面的语句@ret是有可能=0的,那么自然就缺少了commit或者rollback
CREATE PROC usp_FullRecordInsert
(
@GoodsID varchar(12),
@OrderID varchar(18),
@CardNo char(12),
@UserName varchar(20),
@FullDate datetime,
@FullIP varchar(20),
@SourceType int,
@AEMoneyBuy decimal(19,4),
@AEMoneyPar decimal(19,4),
@BEMoneyBuy decimal(19,4),
@BEMoneyPar decimal(19,4),
@AddFlag int
)
AS
INSERT INTO [FullRecord]
(
[GoodsID],
[OrderID]
-- ......
)
VALUES
(
@GoodsID,
@OrderID
--........
)