526互联

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

发布时间 2023-11-11 20:38:53作者: 计科李华超
这个作业属于哪个课程 https://edu.cnblogs.com/campus/uzz/cs3
这个作业要求在哪里 https://edu.cnblogs.com/campus/uzz/cs3/homework/13102

视频链接：https://www.bilibili.com/video/BV19b4y1u7oA/?spm_id_from=333.999.0.0&vd_source=2a1c9a61ba505dd11dcc23112f987bb1

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


CREATE TABLE accunt(
account_no INT auto_increment frimary key,
account_name VARCHAR(10) NOT NULL,
balance INT UNSIGNED);
select * from accunt;
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 accunt SET balance=balance+money WHERE account_no=to_account;
 UPDATE accunt SET balance=balance-money WHERE account_no=from_account;
 COMMIT;
 END @@
 delimirer;
 CALL tran_proc(1,2,800);
 SELECT * FROM accunt;
 CALL tran_proc(1,2,800);
 SELECT * FROM accunt;



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

CREATE DEFINER=`root`@`localhost` PROCEDURE `save_p1_proc`()
begin 
declare CONTINUE HANDLER FOR 1062
BEGIN
ROLLBACK TO b;
end;
start TRANSACTION;
INSERT INTO accunt VALUES(null,'赵四',1000);
SAVEPOINT b;
#last_insert_id()获取赵四账户的账户
INSERT into accunt VALUES(last_insert_id(),'钱六',1000);
COMMIT;
END
CALL save_p1_proc();
SELECT * FROM accunt;



DELETE FROM accunt WHERE account_no=3;
SELECT * from accunt;
CREATE DEFINER=`root`@`localhost` PROCEDURE `save_p2_proc`()
BEGIN
  DECLARE CONTINUE HANDLER FOR 1062
	BEGIN
	ROLLBACK TO b;
	ROLLBACK;
	END;
	START TRANSACTION;
	INSERT INTO accunt VALUES(null,'赵四',1000);
	SAVEPOINT b;
	INSERT INTO accunt VALUES(LAST_INSERT_ID(),'钱六',1000);
	COMMIT;
	END
	CALL save_p2_proc();
	SELECT * FROM accunt;