添加两条记录: 1,张晓峰,10000,自动获取系统时间 2,王杠杠,10000,自动获取系统时间 4:定义一个存储过程,并在此存储过程中定义事务,实现从王杠杠账号转出2000元到张晓峰账号
时间: 2024-05-07 22:23:37 浏览: 12
以下是SQL Server的存储过程代码:
```sql
CREATE PROCEDURE TransferMoney
@fromAccount VARCHAR(50),
@toAccount VARCHAR(50),
@amount DECIMAL(18, 2)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @now DATETIME = GETDATE();
-- 扣除转出账户余额
UPDATE Account SET Balance = Balance - @amount, UpdateTime = @now WHERE AccountNo = @fromAccount;
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('转出账户不存在或余额不足', 16, 1);
ROLLBACK TRANSACTION;
RETURN;
END
-- 增加转入账户余额
UPDATE Account SET Balance = Balance + @amount, UpdateTime = @now WHERE AccountNo = @toAccount;
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('转入账户不存在', 16, 1);
ROLLBACK TRANSACTION;
RETURN;
END
COMMIT TRANSACTION;
PRINT '转账成功';
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
ROLLBACK TRANSACTION;
END CATCH
END
```
接下来,我们可以执行以下脚本,向 `Account` 表中插入两条记录:
```sql
INSERT INTO Account (AccountNo, Balance, UpdateTime)
VALUES ('张晓峰', 10000, GETDATE()), ('王杠杠', 10000, GETDATE());
```
最后,我们可以执行以下脚本,调用存储过程进行转账操作:
```sql
EXEC TransferMoney '王杠杠', '张晓峰', 2000;
```