create procedure transpro
@aid1 float, @aid2 float,@num int
as
begindeclare @ye int
declare @error int
begin transaction
select @ye=jine from account where [email protected] if(@num > @ye)
begin
print '卡上余额不足'
rollback transaction
end
else
update account set [email protected] where [email protected]
set @[email protected]+@@error
update account set [email protected] where [email protected]
set @[email protected]+@@error
if @error>0
begin
print '转账失败！'
rollback transaction
end
else
begin
print '转账成功！'
commit transaction
end
end 当转账金额为2000 而余额只有800的时候，也能输出 转账成功这是怎么回事，不知道哪里有问题，请各位大侠帮忙啊！
------解决方案--------------------
先帮你格式化一下代码，这样没人看的
- SQL code
CREATE PROCEDURE transpro @aid1 FLOAT, @aid2 FLOAT, @num INTASBEGIN DECLARE @ye INT DECLARE @error INT BEGIN TRANSACTION SELECT @ye = jine FROM account WHERE aid = @aid1 IF(@num > @ye) BEGIN PRINT '卡上余额不足' ROLLBACK TRANSACTION END ELSE UPDATE account SET jine = jine + @num WHERE aid = @aid2 SET @[email protected] + @@error UPDATE account SET jine = jine - @num WHERE aid = @aid1 SET @[email protected] + @@error IF @error > 0 BEGIN PRINT '转账失败！' ROLLBACK TRANSACTION END ELSE BEGIN PRINT '转账成功！' COMMIT TRANSACTION ENDEND
------解决方案--------------------
余额足够才进行转账，[email protected] <= @ye时才执行的，因此要在Else和最后一个End之间加上Begin...End。
- SQL code
CREATE PROCEDURE transpro @aid1 FLOAT, @aid2 FLOAT, @num INTASBEGIN DECLARE @ye INT DECLARE @error INT BEGIN TRANSACTION SELECT @ye = jine FROM account WHERE aid = @aid1 IF(@num > @ye) BEGIN PRINT '卡上余额不足' ROLLBACK TRANSACTION END ELSE BEGIN UPDATE account SET jine = jine + @num WHERE aid = @aid2 SET @[email protected] + @@error UPDATE account SET jine = jine - @num WHERE aid = @aid1 SET @[email protected] + @@error IF @error > 0 BEGIN PRINT '转账失败！' ROLLBACK TRANSACTION END ELSE BEGIN PRINT '转账成功！' COMMIT TRANSACTION END ENDEND
------解决方案--------------------
if /else 如果没有加begin /end 的话，默认只执行最接近这个关键字的那句操作，所以建议楼主要规范化编程。不然会有很多后续问题浪费你时间