| 这个作业属于哪个课程 | https://edu.cnblogs.com/campus/uzz/cs3 |
|---|---|
| 这个作业要求在哪里 | https://edu.cnblogs.com/campus/uzz/cs3/homework/13102 |
| 这个作业的目标 | 第8次作业-事物的的例子实现及演示 |
一.题目
【例6-2】假设银行存在两个借记卡账户(account)李三与‘王五，要求这两个借记卡账户不能用于透支，即两个账户的余额 (balance) 不能小于0。创建存储过程tran procO， 实现两个账户的转账业务。
CREATE table account(
account_no INT auto_increment PRIMARY KEY,
account_name VARCHAR(10) not null ,
balance int unsigned
);
insert into account VALUES (null,'李三',1000);
insert into account VALUES (null,'王五',1000);
delimiter @@
create PROCEDURE tran_proc(in from_account int,
in to_account int,
in money int)
BEGIN
DECLARE CONTINUE HANDLER FOR 1690
BEGIN
SELECT '余额小于0'信息;
ROLLBACK;
END;
START TRANSACTION;
UPDATE account SET balance=balance+money
WHERE account_no=to_account;
UPDATE account SET balance=balance-money
WHERE account_no=from_account;
COMMIT;
END @@
delimiter;
call tran_proc(1,2,800);
SELECT * FROM account;
【例6-3】示例。下面创建的两个存储过程，分别对在同一个事务中创建两个账号相同的银行账户进行的不同处理。
delimiter @@
CREATE PROCEDURE save_p1_proc()
begin
declare CONTINUE HANDLER FOR 1062
BEGIN
ROLLBACK TO b;
end;
start TRANSACTION;
INSERT INTO account VALUES(null,'赵四',1000);
SAVEPOINT b;
#last_insert_id()获取赵四账户的账户
INSERT into account VALUES(last_insert_id(),'钱六',1000);
COMMIT;
END@@
delimiter;
CALL save_p1_proc();
SELECT * FROM account;
创建save_p2_proc,先撤销第二条insert语句，然后撤销所有的insert语句
DELETE FROM account where account_no=3;
SELECT * FROM account;
delimiter @@
CREATE PROCEDURE save_p2_proc()
BEGIN
DECLARE CONTINUE HANDLER for 1062
BEGIN
ROLLBACK TO b;
ROLLBACK;
END;
START TRANSACTION;
INSERT INTO account VALUES(null,'赵四',1000);
SAVEPOINT b;
INSERT INTO account VALUES(last_insert_id(),'钱六',1000);
COMMIT;
END @@
delimiter;
call save_p2_proc();
SELECT * FROM account;
