视频链接:还没录制好,暂时就先不上传了
【例6-2】假设银行存在两个借记卡账户(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,'李三',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 '余额小于零'信息;
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;
【例6-3】示例。下面创建的两个存储过程,分别对在同一个事务中创建两个账号相同的银行账户进行的不同处理。
创建 save pl_proc,仅撤销第二条 INSERT语句,提交了第一条INSERT语句。
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,'赵四',1000);
SAVEPOINT b;
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;