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的对应可以在/doc/的MySQL reference manual的附录B找到完整的最新的error codes
condition_name：命名条件
MySQL error code或者SQLSTATE code的可读性太差，所以引入了命名条件:
语法：
Java代码
DECLAREcondition_nameCONDITIONFORcondition_value
condition_value:
SQLSTATE[VALUE]sqlstate_value
|mysql_error_code
DECLARE condition_name CONDITION FOR condition_value
condition_value:
SQLSTATE [VALUE] sqlstate_value
| mysql_error_code
使用:
Java代码
#original
DECLARECONTINUEHANDLERFOR1216MySQL_statements;
#changed
DECLAREforeign_key_errorCONDITIONFOR1216;
DECLARECONTINUEHANDLERFORforeign_key_errorMySQL_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代码
CREATEPROCEDUREsp_add_location
(in_locationVARCHAR(30),
in_address1VARCHAR(30),
in_address2VARCHAR(30),
zipcodeVARCHAR(10),
OUTout_statusVARCHAR(30))
BEGIN
DECLARECONTINUEHANDLER
FOR1062
SETout_status='DuplicateEntry';
SETout_status='OK';
INSERTINTOlocations
(location,address1,address2,zipcode)
VALUES
(in_location,in_address1,in_address2,zipcode);
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代码
CREATEPROCEDUREsp_not_found()
READSSQLDATA
BEGIN
DECLAREl_last_rowINTDEFAULT0;
DECLAREl_dept_idINT:
DECLAREc_deptCURSORFOR
SELECTdepartment_idFROMdepartments;
DECLARECONTINUEHANDLERFORNOTFOUNDSETl_last_row=1;
OPENc_dept;
dept_cursor:LOOP
FETCHc_deptINTOl_dept_id;
IF(l_last_row=1)THEN
LEAVEdept_cursor;
ENDIF;
ENDLOOPdept_cursor;
CLOSEc_dept;
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代码
CREATEPROCEDUREsp_add_department
(p_department_nameVARCHAR(30),
p_manager_surnameVARCHAR(30),
p_manager_firstnameVARCHAR(30),
p_locationVARCHAR(30),
OUTp_sqlcodeINT,
OUTp_status_messageVARCHAR(100))
BEGIN
/*STARTDeclareConditions*/
DECLAREduplicate_keyCONDITIONFOR1062;
DECLAREforeign_key_violatedCONDITIONFOR1216;
/*ENDDeclareCOnditions*/
/*STARTDeclarevariablesandcursors*/
DECLAREl_manager_idINT;
DECLAREcsr_mgr_idCURSORFOR
SELECTemployee_idFROMemployees
WHEREsurname=UPPER(p_manager_surname)
ANDfirstname=UPPER(p_manager_firstname);
/*ENDDeclarevariablesandcursors*/
/*STARTDeclareExceptionHandlers*/
DECLARECONTINUEHANDLERFORduplicate_key
BEGIN
SETp_sqlcode=1052;
SETp_status_message='Duplicatekeyerror';
END;
DECLARECONTINUEHANDLERFORforeign_key_violated
BEGIN
SETp_sqlcode=1216;
SETp_status_message='Foreignkeyviolated';
END;
DECLARECONTINUEHANDLERFORNOTFOUND
BEGIN
SETp_sqlcode=1329;
SETp_status_message='Norecordfound';
END;
/*ENDDeclareExceptionHandlers*/
/*STARTExecution*/
SETp_sqlcode=0;
OPENcsr_mgr_id;
FETCHcsr_mgr_idINTOl_manager_id;
IFp_sqlcode<>0THEN/*Failedtogetmanagerid*/
SETp_status_message=CONCAT(p_status_message,'whenfetchingmanagerid');
ELSE/*Gotmanagerid,wecantryandinsert*/
INSERTINTOdepartments(department_name,manager_id,location)
VALUES(UPPER(p_department_name),l_manager_id,UPPER(p_location));
IFp_sqlcode<>0THEN/*Failedtoinsertnewdepartment*/
SETp_status_message=CONCAT(p_status_message,'wheninsertingnewdepartment');
ENDIF;
ENDIF;
CLOSEcsr_mgr_id;
/*ENDExecution*/
END
