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 

DECLARE condition_name CONDITION FOR condition_value

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | 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; 

# original
DECLARE CONTINUE HANDLER FOR 1216 MySQL_statements;

# changed
DECLARE foreign_key_error CONDITION FOR 1216;
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; 

CREATE PROCEDURE sp_add_location
    (in_location    VARCHAR(30),
     in_address1    VARCHAR(30),
     in_address2    VARCHAR(30),
     zipcode        VARCHAR(10),
     OUT out_status VARCHAR(30))
BEGIN
    DECLARE CONTINUE HANDLER
        FOR 1062
        SET out_status='Duplicate Entry';

    SET out_status='OK';
    INSERT INTO locations
        (location,address1,address2,zipcode)
    VALUES
        (in_location,in_address1,in_address2,zipcode);
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; 

CREATE PROCEDURE sp_not_found()
    READS SQL DATA
BEGIN
    DECLARE l_last_row INT DEFAULT 0;
    DECLARE l_dept_id INT:
    DECLARE c_dept CURSOR FOR
        SELECT department_id FROM departments;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row=1;

    OPEN c_dept;
    dept_cursor: LOOP
        FETCH c_dept INTO l_dept_id;
        IF (l_last_row=1) THEN
            LEAVE dept_cursor;
        END IF;
    END LOOP dept_cursor;
    CLOSE c_dept;
END;

综合示例：

Sql代码

1. CREATE PROCEDURE sp_add_department 
2.     (p_department_name     VARCHAR(30), 
3.      p_manager_surname     VARCHAR(30), 
4.      p_manager_firstname   VARCHAR(30), 
5.      p_location            VARCHAR(30), 
6.      OUT p_sqlcode         INT, 
7.      OUT p_status_message  VARCHAR(100)) 
8. BEGIN 
9.  
10.     /* START Declare Conditions */ 
11.  
12.     DECLARE duplicate_key CONDITION FOR 1062; 
13.     DECLARE foreign_key_violated CONDITION FOR 1216; 
14.  
15.     /* END Declare COnditions */ 
16.  
17.     /* START Declare variables and cursors */ 
18.  
19.     DECLARE l_manager_id INT; 
20.     DECLARE csr_mgr_id CURSOR FOR 
21.         SELECT employee_id FROM employees 
22.         WHERE surname=UPPER(p_manager_surname) 
23.         AND firstname=UPPER(p_manager_firstname); 
24.  
25.     /* END Declare variables and cursors */ 
26.  
27.     /* START Declare Exception Handlers */ 
28.  
29.     DECLARE CONTINUE HANDLER FOR duplicate_key 
30.     BEGIN 
31.         SET p_sqlcode=1052; 
32.         SET p_status_message='Duplicate key error'; 
33.     END; 
34.  
35.     DECLARE CONTINUE HANDLER FOR foreign_key_violated 
36.     BEGIN 
37.         SET p_sqlcode=1216; 
38.         SET p_status_message='Foreign key violated'; 
39.     END; 
40.  
41.     DECLARE CONTINUE HANDLER FOR NOT FOUND 
42.     BEGIN 
43.         SET p_sqlcode=1329; 
44.         SET p_status_message='No record found'; 
45.     END; 
46.  
47.     /* END Declare Exception Handlers */ 
48.  
49.     /* START Execution */ 
50.  
51.     SET p_sqlcode=0; 
52.     OPEN csr_mgr_id; 
53.     FETCH csr_mgr_id INTO l_manager_id; 
54.  
55.     IF p_sqlcode<>0 THEN     /* Failed to get manager id */ 
56.         SET p_status_message=CONCAT(p_status_message,' when fetching manager id'); 
57.     ELSE                     /* Got manager id, we can try and insert */ 
58.         INSERT INTO departments (department_name, manager_id, location) 
59.         VALUES(UPPER(p_department_name), l_manager_id, UPPER(p_location)); 
60.         IF p_sqlcode<>0 THEN /* Failed to insert new department */ 
61.             SET p_status_message=CONCAT(p_status_message, ' when inserting new department'); 
62.         END IF; 
63.     END IF; 
64.  
65.     CLOSE csr_mgr_id; 
66.  
67.     /* END Execution */ 
68.  
69. END  

 

 

http://wwty.iteye.com/blog/698239