事物的例子实现及演示

【例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;

CALL tran_proc(1,2,800);

SELECT * FROM account;

视频讲解：https://www.bilibili.com/video/BV14M411D7Ft/?share_source=copy_web&vd_source=177f6d85b03d42c9b4e590116780ba12

【例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;
	 INSERT INTO account VALUES(LAST_INSERT_ID(),'钱六',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,'赵四',1000);
		 SAVEPOINT b;
		 INSERT INTO account VALUES(LAST_INSERT_ID(),'钱六',1000);
	 COMMIT;
 END @@
 
 DELIMITER ;
 CALL save_p2_proc();
 SELECT * FROM account;

视频讲解：https://www.bilibili.com/video/BV1ta4y1S7Zo/?share_source=copy_web&vd_source=177f6d85b03d42c9b4e590116780ba12