第八次作业

这个作业属于哪个课程	https://edu.cnblogs.com/campus/uzz/cs3
这个作业要求在哪里	https://edu.cnblogs.com/campus/uzz/cs3/homework/13102
这个作业的目标	第8次作业-事物的的例子实现及演示

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

【例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; -- 事务回滚到保存点b
ROLLBACK; -- 整个事务回滚
END;

START TRANSACTION; -- 开始事务

INSERT INTO account VALUES(null, '赵四', 1000); -- 向账户表中插入记录，null代表自增主键
SAVEPOINT b; -- 设置保存点

INSERT INTO account VALUES(last_insert_id(), '钱六', 1000); -- 向账户表中插入记录，使用了上一个自增主键的值
COMMIT;-- 没有COMMIT，因此整个事务会被回滚

END @@
DELIMITER ;
CALL save_p2_proc();
select * from account;