526互联

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

发布时间 2023-11-12 23:00:05作者: Sss~~
这个作业属于哪个课程 https://edu.cnblogs.com/campus/uzz/cs3
这个作业要求在哪里 https://edu.cnblogs.com/campus/uzz/cs3/homework/13102
这个作业的目标 第8次作业-事物的例子实现及演示
b站地址 https://www.bilibili.com/video/BV1fC4y1U7NS/?spm_id_from=333.999.0.0&vd_source=e63a90cd5285f6a3b3a5026e935021cb

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

CREATE DATABASE bank;
USE bank;
CREATE TABLE account(
 account_no INT AUTO_INCREMENT PRIMARY KEY,
 account_name VARCHAR(10) NOT NULL,
balance INT UNSIGNED 
);

SHOW TABLES;

INSERT INTO account VALUES(NULL,'李三',1000);
INSERT INTO account VALUES(NULL,'王五',1000);
SELECT * FROM account;

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;

image

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

(1)第一个存储过程

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;

image

第二个存储过程

	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;

image