mysql的错误处理以及回滚的问题
代码如下:
DELIMITER $$CREATE PROCEDURE sp_test(OUT errcode1 INT,OUT errcode2 INT)sp_test_l: BEGIN DECLARE CONTINUE HANDLER FOR 1452 SELECT 'Foreign key error'; START TRANSACTION; SET errcode1=@@error_count; INSERT INTO acct_access_log (accountno,al_type,al_time,al_address,al_session) VALUES ('RCT01',0,CURRENT_TIMESTAMP,'192.168.101.171','123456789'); INSERT INTO acct_access_log (accountno,al_type,al_time,al_address,al_session) VALUES (123,0,CURRENT_TIMESTAMP,'192.168.101.171','123456789'); IF @@error_count<>0 THEN SET errcode2=1; rollback; leave sp_test_l; END IF; COMMIT; END sp_test_l$$DELIMITER ;
mysql> create table t_cqlxm ( -> id int primary key, -> c int -> );Query OK, 0 rows affected (0.09 sec)mysql> DELIMITER $$mysql> CREATE PROCEDURE sp_cqlxm() -> BEGIN -> DECLARE CONTINUE HANDLER FOR 1062 rollback ; -> START TRANSACTION; -> insert into t_cqlxm values (1,1); -> insert into t_cqlxm values (1,1); -> -> COMMIT; -> END -> $$Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> call sp_cqlxm();Query OK, 0 rows affected (0.05 sec)mysql> select * from t_cqlxm;Empty set (0.00 sec)mysql>