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：vote是什么意思
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): such_sp does not exist
瓦砾
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
allowSql代码
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;mmds
⽰例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;pong
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;
综合⽰例：
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 inrt */
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 inrt new department */
61. SET p_status_message=CONCAT(p_status_message, ' when inrting new department');
62. END IF;
63. END IF;
qxp64.
kw是什么单位
65. CLOSE csr_mgr_id;
66.
67. /* END Execution */
68.
69. END
70. 原⽂：/blog/698239
