第八次作业

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;


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;

b站链接 https://www.bilibili.com/video/BV1UN4y1D7io/?spm_id_from=444.41.list.card_archive.click