4.创建带参数的存储过程,使用事务实现从张三的帐户转帐到李四的帐户。转账数额使用输入参数保存。事务过程如下: (1)张三的帐户减少一定额度; (2)向transInfo表添加一条转账记录;10010002,取出,数额 (3)李四的帐户增加相应额度; (4)向transInfo表添加一条数据: 10010002,存入,数额; (5)用select语句查看转帐过程中的bank表和transInfo表; (6)若张三余额小于0则撤销事务,否则提交事务; 用select语句查看转帐结束后的bank表和transInfo表
时间: 2024-02-25 15:57:36 浏览: 25
以下是创建带参数的存储过程,使用事务实现从张三的帐户转帐到李四的帐户:
```
CREATE PROCEDURE transfer_money(IN transfer_amount INT)
BEGIN
DECLARE zhangsan_balance INT;
DECLARE lisi_balance INT;
SET zhangsan_balance = (SELECT balance FROM bank WHERE name = '张三');
SET lisi_balance = (SELECT balance FROM bank WHERE name = '李四');
IF zhangsan_balance >= transfer_amount THEN
START TRANSACTION;
UPDATE bank SET balance = balance - transfer_amount WHERE name = '张三';
INSERT INTO transInfo (account, type, amount) VALUES (10010002, '取出', transfer_amount);
UPDATE bank SET balance = balance + transfer_amount WHERE name = '李四';
INSERT INTO transInfo (account, type, amount) VALUES (10010002, '存入', transfer_amount);
SELECT * FROM bank;
SELECT * FROM transInfo;
IF (SELECT balance FROM bank WHERE name = '张三') < 0 THEN
ROLLBACK;
SELECT '转账失败,张三余额不足!' AS result;
ELSE
COMMIT;
SELECT '转账成功!' AS result;
END IF;
ELSE
SELECT '转账失败,张三余额不足!' AS result;
END IF;
END;
```
使用以下语句调用该存储过程:
```
CALL transfer_money(100);
```
该存储过程会将100元从张三的帐户转账到李四的帐户,同时向`transInfo`表中添加转账记录。如果转账成功,则会返回`转账成功!`,并显示转账后的`bank`表和`transInfo`表;如果转账失败,则会返回`转账失败,张三余额不足!`,并不会对数据库进行修改。
注意:在测试过程中,请确保`bank`表和`transInfo`表中已经存在名称为“张三”和“李四”的记录,且他们的初始余额足够进行转账。