T-SQL 存储过程: 用于通过将相同的数据存储在数据库中来节省写入代码的时间,并通过传递参数获得所需的输出。
语法:
范例:修改的话只需要将create改为alter,内部需要改的部分改掉就行了。
CREATE PROCEDURE [dbo].[up_bank]
@MONEY int,@Mshou nvarchar(10),@Mout nvarchar(10)\\自定义变量
AS\\存储过程的开始
BEGIN TRANSACTION
DECLARE @errsum INT 自定义变量
SET @errsum=0
print 'check money current'
select name,money as before_trans from bank
update bank set money=money-@MONEY where name=@Mout
set @errsum=@errsum+@@ERROR
update bank set money=money+@MONEY where name=@Mshou
set @errsum=@errsum+@@ERROR
print 'working on process'
if @errsum<>0
BEGIN
print 'oh shit,no money'
rollback TRANSACTION
END
ELSE
BEGIN
print 'job done'
COMMIT TRANSACTION
END
print 'check money '
select name,money as after_trans from bank
execute up_bank 使用存储
ALTER procedure [dbo].[up_bank2]
@money int ,@jia nvarchar(50),@yi nvarchar(50)
as
if exists(select * from bank where name = @jia)
begin
if exists ( select * from bank where name =@yi)
begin
execute up_bank @MONEY=@money,@shou = @jia ,@chu=@yi 调用存储过程
end
else
begin
insert into bank values (@yi,0)
print'ads'
execute up_bank @MONEY=@money,@shou = @jia ,@chu=@yi
end
end
else
begin
print'不存在帐户'
end