事务的举例(提交与回滚)

视频链接

https://www.bilibili.com/video/BV1aM411S7i9/

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

create database bank;

use bank;

show tables;

drop table account;

create table account(
id varchar(50),
name varchar(10),
balance int 
) ;

desc account;

insert into account values 
("202312310111","李三",1000),("202312310222","王五",1000);

select * from account;

create table print(
number int,
information varchar(50));

insert into print VALUES
(1,"您的余额不足，交易失败！"),
(2,"交易成功！");


drop PROCEDURE tran_proc;

DELIMITER @@ 
CREATE PROCEDURE tran_proc(id_1 varchar(50),id_2 varchar(50),balance_p int) 
 BEGIN 
START TRANSACTION;

UPDATE account
SET balance = balance - balance_p
WHERE id = id_1;
UPDATE account
SET balance = balance + balance_p
WHERE id = id_2;
 IF((select balance from account where id=id_1)<0) then 
     SELECT information from print where number=1;
     ROLLBACK;
		 
	ELSE
	 SELECT information from print where number=2;
	 COMMIT;
	  
	END IF;

END@@

CALL tran_proc("202312310111","202312310222",1500);
CALL tran_proc("202312310222","202312310111",500);

【例6-3】示例。下面创建的两个存储过程，分别对在同一个事务中创建两个账号相同的银行账户进行的不同处理。

drop PROCEDURE tran_info;
DELIMITER @@ 
CREATE PROCEDURE tran_info(point VARCHAR(50)) 
 BEGIN 
START TRANSACTION;
INSERT INTO account VALUES ("202312310333","赵四",1000);
SAVEPOINT b;
INSERT INTO account VALUES ("202312310444","刘娜",1000);
SAVEPOINT c;
IF(point="全部同意")
 THEN
 COMMIT;
 ELSEIF(point="同意赵四加入不同意刘娜")
 THEN
 ROLLBACK TO c;
 ELSEIF(point="同意刘娜加入不同意赵四")
 THEN
 ROLLBACK TO b;
 ELSE
 ROLLBACK;
 END IF;

END@@

CALL tran_info("全部同意");
CALL tran_info("同意赵四加入不同意刘娜");
CALL tran_info("不同意");