第8次作业-事物的的例子实现及演示

这个作业属于哪个课程	https://edu.cnblogs.com/campus/uzz/cs3
这个作业要求在哪里	https://edu.cnblogs.com/campus/uzz/cs3/homework/13102
这个作业的目标	1.可以运行2.视频讲解、演示实现过程（发布于b站）3.提交作业、并附带视频地址。

视频链接：【事务】https://www.bilibili.com/video/BV1nN411g7NN?vd_source=2201cd13367cea3cdb6024186ebc488e

1.假设银行存在两个借记卡账户(account)李三与‘王五，要求这两个借记卡账户不能用于透支，即两个账户的余额 (balance) 不能小于0。创建存储过程tran procO， 实现两个账户的转账业务。

#建立 account 表
create table account (
account_no int auto_increment primary key,
account_name varchar(10) not null,
balance int unsigned 
);
# 向account 表中插入记录
insert into account values(null,'james',1000);
insert into account values(null,'harden',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 '余额小于零'信息;
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;

call tran_proc(1,2,800);

select * from account;

2.下面创建的两个存储过程，分别对在同一个事务中创建两个账号相同的银行账户进行的不同处理。

delimiter @@
create procedure save_p1_proc()
begin 
declare continue HANDLER for 1062
BEGIN
rollback  to b; # 事务回滚到保存点b
end;
start TRANSACTION;
insert into account values(null,'Paul',1000);
SAVEPOINT b;

insert into account values(last_insert_id(),'george',1000);
commit;
END@@

delimiter;
call save_p1_proc();
select * from account;

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,'Paul',1000);
SAVEPOINT b;

insert into account values(last_insert_id(),'george',1000);
commit;
END@@

delimiter;
call save_p2_proc();
select * from account;