MYSQL 8 基本操作之06 (日誌表)

1.創建日誌表(ENGINE=myisam)

create table sys_proc_log
(
sys_proc_log_seq int auto_increment not null,
sys_proc_name varchar(100),
log_start_time datetime not null,
log_end_time datetime,
log_status varchar(10) not null,  /* start  end  ok   error  */
error_no varchar(20),
error_desc varchar(1000),
primary key(sys_proc_log_seq)
) ENGINE=myisam auto_increment=100001;

2. 存储过程中测试日志功能

DROP PROCEDURE IF EXISTS  proc_my_acc_rollback4;

DELIMITER $$

CREATE PROCEDURE proc_my_acc_rollback4
(
  in v_acc_no VARCHAR(20),
  in v_acc_name varchar(50),
  in v_tran_no varchar(30),
  in v_tran_money DECIMAL(10,2),
  out v_return varchar(500)
)
  BEGIN  
    DECLARE v_now datetime;
    declare v_id int;
    DECLARE i TINYINT DEFAULT 1;
    DECLARE v_errcount INT DEFAULT 0;  /**获取一次错误数据条数 */
    DECLARE v_errno INT DEFAULT 0;/**获取错误代码*/
    DECLARE v_msg TEXT;/**获取错误详细信息*/
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION /**定义一个异常处理块*/
    BEGIN     
     get stacked diagnostics v_errcount = number;       
     set i=1;              
     WHILE i <= v_errcount
     DO
        GET stacked DIAGNOSTICS CONDITION i v_errno = MYSQL_ERRNO, v_msg = MESSAGE_TEXT; 
        SET v_return = CONCAT(v_return,v_errno);
        SET v_return = CONCAT(v_return,':');
        SET v_return = CONCAT(v_return,v_msg);
        SET v_return = CONCAT(v_return,'; '); 
        SET i = i + 1;
     END WHILE;
    END;

    /** 显式的开启事务，它开启后，事务会暂时停止自动提交*/  
    -- start transaction;  
    /** 关闭事务的自动提交 */  
    SET autocommit = 0; 
    set v_now =now();

    insert into sys_proc_log(sys_proc_name,log_start_time,log_status) values('proc_my_acc_rollback4',v_now,'start');
    set v_id=LAST_INSERT_ID();

    SET v_return='';         
    insert into my_account_tran(acc_no,tran_no,tran_money,tran_time) values(v_acc_no,v_tran_no,v_tran_money,v_now);
    IF v_errcount=0 THEN
    /* 注释掉上面if 语句表示 报错后继续运行，最后捕获所以异常，否则 出现第一个异常后就退出，仅仅能捕获第一个异常 */ 
      insert into my_account(acc_no,acc_name,acc_banance,acc_createtime) values(v_acc_no,v_acc_name,v_tran_money,v_now);
    END IF;
    /** 标记被改变,表示事务应该回滚 */  
    IF v_errcount>0 THEN
        update sys_proc_log set error_no=v_errno,error_desc=v_return,log_status='error',log_end_time=now() 
        where sys_proc_log_seq=v_id;
        ROLLBACK; -- 事务回滚    
        SET autocommit =1;  
    ELSE 
        SET v_return='[OK]';
        update sys_proc_log set log_status='ok',log_end_time=now() 
        where sys_proc_log_seq=v_id;     
        COMMIT; -- 事务提交  
        SET autocommit =1;         
    END IF;         
END$$


DELIMITER ;

3. 調用存儲過程

root@127.0.0.1 : testdb【11:43:11】164 SQL->call proc_my_acc_rollback4('c1003','xag3','20160702100003',100,@msg);

root@127.0.0.1 : testdb【11:43:14】165 SQL->select @msg;
+------+
| @msg |
+------+
| [OK] |
+------+

root@127.0.0.1 : testdb【11:43:26】166 SQL->select * from sys_proc_log;
+------------------+-----------------------+---------------------+---------------------+------------+----------+------------+
| sys_proc_log_seq | sys_proc_name         | log_start_time      | log_end_time        | log_status | error_no | error_desc |
+------------------+-----------------------+---------------------+---------------------+------------+----------+------------+
|           100001 | proc_my_acc_rollback4 | 2019-07-05 23:43:14 | 2019-07-05 23:43:14 | ok         | NULL     | NULL       |
+------------------+-----------------------+---------------------+---------------------+------------+----------+------------+

root@127.0.0.1 : testdb【11:43:58】169 SQL->call proc_my_acc_rollback4('c1003','xag3','20160702100003',100,@msg);

root@127.0.0.1 : testdb【11:44:05】170 SQL->select @msg;
+-----------------------------------------------------------+
| @msg                                                      |
+-----------------------------------------------------------+
| 1062:Duplicate entry '20160702100003' for key 'tran_no';  |
+-----------------------------------------------------------+

root@127.0.0.1 : testdb【11:44:09】171 SQL->select * from sys_proc_log;
+------------------+-----------------------+---------------------+---------------------+------------+----------+-----------------------------------------------------------+
| sys_proc_log_seq | sys_proc_name         | log_start_time      | log_end_time        | log_status | error_no | error_desc                                                |
+------------------+-----------------------+---------------------+---------------------+------------+----------+-----------------------------------------------------------+
|           100001 | proc_my_acc_rollback4 | 2019-07-05 23:43:14 | 2019-07-05 23:43:14 | ok         | NULL     | NULL                                                      |
|           100002 | proc_my_acc_rollback4 | 2019-07-05 23:44:05 | 2019-07-05 23:44:05 | error      | 1062     | 1062:Duplicate entry '20160702100003' for key 'tran_no';  |
+------------------+-----------------------+---------------------+---------------------+------------+----------+-----------------------------------------------------------+

root@127.0.0.1 : testdb【11:44:18】172 SQL->select * from my_account_tran;
+----------+--------+----------------+------------+---------------------+
| tran_seq | acc_no | tran_no        | tran_money | tran_time           |
+----------+--------+----------------+------------+---------------------+
|     1001 | c1003  | 20160702100003 |     100.00 | 2019-07-05 23:43:14 |
+----------+--------+----------------+------------+---------------------+

root@127.0.0.1 : testdb【11:44:47】173 SQL->select * from my_account;
+---------+--------+----------+-------------+---------------------+------------+
| acc_seq | acc_no | acc_name | acc_banance | acc_createtime      | acc_update |
+---------+--------+----------+-------------+---------------------+------------+
|    1001 | c1003  | xag3     |      100.00 | 2019-07-05 23:43:14 | NULL       |
+---------+--------+----------+-------------+---------------------+------------+