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

白森

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