mysql存储过程之异常处理篇

mysql存储过程也提供了对异常处理的功能：通过定义HANDLER来完成异常声明的实现

 

 

语法如下：

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement handler_type: CONTINUE | EXIT condition_value: SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code

Handlers类型:

1, EXIT: 发生错误时退出当前代码块（可能是子代码块或者main代码块）
2, CONTINUE: 发送错误时继续执行后续代码

condition_value：

condition_value支持标准的SQLSTATE定义；

SQLWARNING是对所有以01开头的SQLSTATE代码的速记

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

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

除了SQLSTATE值，MySQL错误代码也被支持

但是对于mysql而言，优先级如下：
MySQL Error code > SQLSTATE code > 命名条件

 

 

使用SQLSTATE还是MySQL Error Code？ 

1，SALSTATE是标准，貌似会更portable，但是实际上MySQL、DB2、Oracle等等的存储程序语法大相径庭，所以portable的优势不存在 
2，MySQL error code与SQLSTATE并不是一一对应的，比如很多MySQL error code都映射到同一SQLSTATE code(HY000) 

当MySQL客户端碰到错误时，它会报告MySQL error code和相关的SQLSATE code:

mysql > CALL nosuch_sp();
ERROR 1305 (42000): PROCEDURE sqltune.nosuch_sp does not exist

具体的sqlsdate和mysql error code的对应可以在http://dev.mysql.com/doc/的MySQL reference manual的附录B找到完整的最新的error codes 

 

condition_name：命名条件

MySQL error code或者SQLSTATE code的可读性太差，所以引入了命名条件:

语法：

Java代码  

1. DECLARE condition_name CONDITION FOR condition_value  
2.   
3. condition_value:  
4.     SQLSTATE [VALUE] sqlstate_value  
5.   | mysql_error_code  

 使用: 

Java代码  

1. # original  
2. DECLARE CONTINUE HANDLER FOR 1216 MySQL_statements;  
3.   
4. # changed  
5. DECLARE foreign_key_error CONDITION FOR 1216;  
6. DECLARE CONTINUE HANDLER FOR foreign_key_error MySQL_statements;  

  用condition_name为错误代码起了个别名。

 

示例1：Duplicate entry Handler

Sql代码  

1. CREATE PROCEDURE sp_add_location  
2.     (in_location    VARCHAR(30),  
3.      in_address1    VARCHAR(30),  
4.      in_address2    VARCHAR(30),  
5.      zipcode        VARCHAR(10),  
6.      OUT out_status VARCHAR(30))  
7. BEGIN  
8.     DECLARE CONTINUE HANDLER  
9.         FOR 1062  
10.         SET out_status='Duplicate Entry';  
11.   
12.     SET out_status='OK';  
13.     INSERT INTO locations  
14.         (location,address1,address2,zipcode)  
15.     VALUES  
16.         (in_location,in_address1,in_address2,zipcode);  
17. END;  

 

示例2： Last Row Handler

 

Sql代码  

1. CREATE PROCEDURE sp_not_found()  
2.     READS SQL DATA  
3. BEGIN  
4.     DECLARE l_last_row INT DEFAULT 0;  
5.     DECLARE l_dept_id INT:  
6.     DECLARE c_dept CURSOR FOR  
7.         SELECT department_id FROM departments;  
8.     DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row=1;  
9.   
10.     OPEN c_dept;  
11.     dept_cursor: LOOP  
12.         FETCH c_dept INTO l_dept_id;  
13.         IF (l_last_row=1) THEN  
14.             LEAVE dept_cursor;  
15.         END IF;  
16.     END LOOP dept_cursor;  
17.     CLOSE c_dept;  
18. END;