MySql存储过程的异常处理理论及实例

古城微笑少年丶 2022-06-13 08:47 429阅读 0赞

MySql存储过程异常处理示例:

在网上查了好多资料,发现关于mysql的异常处理资料都是一些错误号列表,对于平时运行中,我们可能更多的希望能够记录准确的错误消息到日志中.但是mysql由于先天原因，不能在过程中获得准确的异常信息，所以只能靠自己手动将异常捕捉后插表记录了。

先举个栗子：

BEGIN   -- 定义一个异常，并定义在发生这个异常的时候做的操作是什么 DECLARE EXIT HANDLER FOR SQLEXCEPTION #引用前面定义的条件名称并做赋值处理。注意要放到定义游标之后。分别对异常和告警做不同处理。     #另：@@error_count和@@warning_count是会话变量，除非你修改，否则还是记录你的业务信息有意义     DECLARE EXIT HANDLER FOR SQLEXCEPTION     BEGIN         INSERT INTO tb_err_info(exception_content,create_time) VALUES (concat(@@error_count,' errors'),NOW());     END;     DECLARE CONTINUE HANDLER FOR SQLWARNING     BEGIN         INSERT INTO tb_err_info(exception_content,create_time) VALUES (concat(@@warning_count,' warnings'),NOW());     END; END;

再说说语法。

语法的话主要分为两部分

一部分是对异常本身的定义，简称给异常起名字，如下

语法：DECLARE condition\_name CONDITION FOR \[condition\_type\];

说明：**condition\_name**参数表示异常的名称；**condition\_type**参数表示条件的类型

其中：后者由sqlstate\_value|mysql\_error\_code组成，二者都可以表示mysql的异常。

区别：sqlstate\_value为长度为5的字符串类型的错误代码；mysql\_error\_code为数值类型错误代码；

举例：**使用sqlstate\_value**

**               ** DECLARE command\_not\_allowed CONDITION FOR SQLSTATE '42000';

**             使用mysql\_error\_code**

**               ** DECLARE command\_not\_allowed CONDITION FOR 1148;

另一部分是定义如何对待这类异常，也就是处理方式。这个方式可以是一段被begin...end包围起来的代码。如下

语法：DECLARE handler\_type HANDLER FOR condition\_value \[,...\] sp\_statement

说明：**handler\_type:** 值域为这三个：CONTINUE|EXIT|UNDO；

其中：前者的值域中：CONTINUE表示遇到错误不处理，继续执行；

EXIT表示遇到错误时马上退出；

UNDO表示遇到错误后撤回之前的操作，

MySQL暂不支持回滚操作；

后者        ：condition\_value表示错误类型

SQLSTATE \[VALUE\] sqlstate\_value为包含5个字符的字符串错误值；

condition\_name表示DECLARE CONDITION定义的错误条件名称；

SQLWARNING匹配所有以01开头的SQLSTATE错误代码；

NOT FOUND匹配所有以02开头的SQLSTATE错误代码；

SQLEXCEPTION匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；

mysql\_error\_code匹配数值类型错误代码；

举例：其实下面的例子没卵用，谁会只是简单的把结果放到一个变量就完事，肯定是存表。

//方法一：捕获sqlstate_value异常
    
    //这种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为"42S02"，执行CONTINUE操作，并输出"NO_SUCH_TABLE"信息
    
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';
    
    
    //方法二：捕获mysql_error_code异常
    
    //这种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146，执行CONTINUE操作，并输出"NO_SUCH_TABLE"信息；
    
    DECLARE CONTINUE HANDLER FOR 1146 SET @info='NO_SUCH_TABLE';
    
    
    //方法三：先定义条件，然后捕获异常
    
    DECLARE no_such_table CONDITION FOR 1146;
    
    DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='NO_SUCH_TABLE';
    
    
    //方法四：使用SQLWARNING捕获异常
    
    DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';
    
    
    //方法五：使用NOT FOUND捕获异常
    
    DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';
    
    
    //方法六：使用SQLEXCEPTION捕获异常
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

以上的内容中，有几点需要注意

SQLWARNING：是对所有以01开头的SQLSTATE代码的速记，例如：DECLARE CONTINUE HANDLER FOR SQLWARNING。

NOT FOUND:是对所有以02开头的SQLSTATE代码的速记。

SQLEXCEPTION:是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。

其实，在真实使用中，我们一定是把产生异常对应的记录的基本信息需要获取到并保存到表中已备后续跟踪解决使用，如下

#引用前面定义的条件名称并做赋值处理。注意要放到定义游标之后。分别对异常和告警做不同处理。
    	#另：@@error_count和@@warning_count是会话变量，除非你修改，否则还是记录你的业务信息有意义
    	DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    	BEGIN
    		INSERT INTO tb_err_info(exception_content,create_time) 
    		VALUES (concat('id为',@u_group_id,' 的记录产生异常，并且执行到第' ,@STEP_WHERE ,'步操作，请结合步骤分析id是企业抑或用户id'),NOW());
    	END;
    
    SET @STEP_WHERE = 1;		#用来追踪流程走到第几步
    
    	SELECT count(1) 
    		INTO LOOP_COUNT
    		FROM tb_mapping_cm_group ;
    	SET @LOOP_STEP = 0;
    SET @STEP_WHERE = 2;
    	#首先处理用户组到企业和部门表的同步
    	OPEN cursor_user_group;
    	WHILE @LOOP_STEP < LOOP_COUNT DO
    		FETCH cursor_user_group INTO GROUP_ID, GROUP_NAME, GROUP_TOP_ID, CREATE_TIME, UPDATE_TIME, DEAL_TYPE;
    		#如果group_top_id是空，说明为企业信息，插入企业表和企业映射翻译表
    		IF GROUP_TOP_ID IS NULL OR GROUP_TOP_ID = '' THEN
    			INSERT INTO nv_enterprise (enterprise_name, status) 
    			VALUES(GROUP_NAME, 1) ;
    SET @STEP_WHERE = 3;
    			#从information_schema获取尚未commit的自增长字段的最新值
    			SELECT A.AUTO_INCREMENT 
    				INTO TMP_EID
    				FROM information_schema.`TABLES` A 
    #这里制造一个异常，也就是会into多条记录到一个变量中的错误
    #			 WHERE A.TABLE_NAME = 'nv_enterprise' 
    #				 AND A.TABLE_SCHEMA= 'pexipmeetingcm'
    ;
    SET @STEP_WHERE = 4;
    			INSERT INTO tb_mapping_trans_cm_ent(ent_id, user_group_id, status) 
    			VALUES (TMP_EID, GROUP_ID, '1');
    SET @STEP_WHERE = 5;
    		#否则就认为是部门级记录，插入到部门表和部门映射翻译表
    		ELSE
    			#需要先根据GROUP_TOP_ID查询对应企业编码，再插入到部门表
    			SELECT a.ent_id
    				INTO TMP_EID
    				FROM tb_mapping_trans_cm_ent a
    			 WHERE a.user_group_id = GROUP_TOP_ID;
    SET @STEP_WHERE = 6;
    SET @u_group_id = GROUP_ID;
    			INSERT INTO nv_department (name, enterprise_id) 
    			VALUES(GROUP_NAME, TMP_EID) ;
    SET @STEP_WHERE = 7;
    			#从information_schema获取尚未commit的自增长字段的最新值
    			SELECT A.AUTO_INCREMENT 
    				INTO TMP_DID
    				FROM information_schema.`TABLES` A 
    			 WHERE A.TABLE_NAME = 'nv_department' 
    				 AND A.TABLE_SCHEMA= 'pexipmeetingcm';
    SET @STEP_WHERE = 8;
    			INSERT INTO tb_mapping_trans_cm_dept(dept_id, ent_id, user_group_id, user_group_parent_id, status) 
    			VALUES (TMP_DID, TMP_EID, GROUP_ID, GROUP_TOP_ID, '1');
    SET @STEP_WHERE = 9;
    		END IF;
    		SET @LOOP_STEP = @LOOP_STEP + 1;
    	COMMIT;
    	END WHILE;
    	CLOSE cursor_user_group;