标簽：
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代碼
DECLARE condition_name CONDITION FOR condition_value
condition_value:
SQLSTATE [VALUE] sqlstate_value
| mysql_error_code
使用:
Java代碼
# 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代碼
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代碼
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代碼
CREATE PROCEDURE sp_add_department
(p_department_name VARCHAR(30),
p_manager_surname VARCHAR(30),
p_manager_firstname VARCHAR(30),
p_location VARCHAR(30),
OUT p_sqlcode INT,
OUT p_status_message VARCHAR(100))
BEGIN
DECLARE duplicate_key CONDITION FOR 1062;
DECLARE foreign_key_violated CONDITION FOR 1216;
DECLARE l_manager_id INT;
DECLARE csr_mgr_id CURSOR FOR
SELECT employee_id FROM employees
WHERE surname=UPPER(p_manager_surname)
AND firstname=UPPER(p_manager_firstname);
DECLARE CONTINUE HANDLER FOR duplicate_key
BEGIN
SET p_sqlcode=1052;
SET p_status_message=‘Duplicate key error‘;
END;
DECLARE CONTINUE HANDLER FOR foreign_key_violated
BEGIN
SET p_sqlcode=1216;
SET p_status_message=‘Foreign key violated‘;
END;
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET p_sqlcode=1329;
SET p_status_message=‘No record found‘;
END;
SET p_sqlcode=0;
OPEN csr_mgr_id;
FETCH csr_mgr_id INTO l_manager_id;
IF p_sqlcode<>0 THEN
SET p_status_message=CONCAT(p_status_message,‘ when fetching manager id‘);
ELSE
INSERT INTO departments (department_name, manager_id, location)
VALUES(UPPER(p_department_name), l_manager_id, UPPER(p_location));
IF p_sqlcode<>0 THEN
SET p_status_message=CONCAT(p_status_message, ‘ when inserting new department‘);
END IF;
END IF;
CLOSE csr_mgr_id;
END
原文：http://wwty.iteye.com/blog/698239
标簽：