MySQL存储程序之Error Handling

[b]1，简介[/b]
MySQL支持HANDLER来处理错误：
[b]Duplicate entry Handler[/b]
[code]
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;
[/code]

[b]Last Row Handler[/b]
[code]
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;
[/code]

[b]2，Handlers[/b]
语法:
[code]
DECLARE {CONTINUE | EXIT} HANDLER FOR
{SQLSTATE sqlstate_code | MySQL error code | condition_name}
handler_actions
[/code]
Handlers类型:
[code]
1, EXIT: 发生错误时退出当前代码块（可能是子代码块或者main代码块）
2, CONTINUE: 发送错误时继续执行后续代码
[/code]
Handlers条件:
[code]
1, MySQL error code，如1062
2, ANSI标准SQLSTATE code，如23000
3, 命名条件，如NOT FOUND
[/code]

优先级：
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:
[code]
mysql > CALL nosuch_sp();
ERROR 1305 (42000): PROCEDURE sqltune.nosuch_sp does not exist
[/code]
上面Error code是1305，SQLSTATE code是42000

常见的MySQL error code和SQLSTATE code:
[code]
MySQL error code SQLSTATE code Error message
1011 HY000 Error on delete of '%s' (errno: %d)
1021 HY000 Disk full (%s); waiting for someone to free some space...
1022 23000 Can't write; duplicate key in table '%s'
1027 HY000 '%s' is locked against change
1036 HY000 Table '%s' is read only
1048 23000 Column '%s' cannot be null
1062 23000 Duplicate entry '%s' for key %d
1099 HY000 Table '%s' was locked with a READ lock and can't be updated
1100 HY000 Table '%s' was not locked with LOCK TABLES
1104 42000 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
1106 42000 Incorrect parameters to procedure '%s'
1114 HY000 The table '%s' is full
1150 HY000 Delayed insert thread couldn't get requested lock for table %s
1165 HY000 INSERT DELAYED can't be used with table '%s' because it is locked with LOCK TABLES
1242 21000 Subquery returns more than 1 row
1263 22004 Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld
1264 22003 Out of range value adjusted for column '%s' at row %ld
1265 1000 Data truncated for column '%s' at row %ld
1312 0A000 SELECT in a stored program must have INTO
1317 70100 Query execution was interrupted
1319 42000 Undefined CONDITION: %s
1325 24000 Cursor is already open
1326 24000 Cursor is not open
1328 HY000 Incorrect number of FETCH variables
1329 2000 No data to FETCH
1336 42000 USE is not allowed in a stored program
1337 42000 Variable or condition declaration after cursor or handler declaration
1338 42000 Cursor declaration after handler declaration
1339 20000 Case not found for CASE statement
1348 HY000 Column '%s' is not updatable
1357 HY000 Can't drop a %s from within another stored routine
1358 HY000 GOTO is not allowed in a stored program handler
1362 HY000 Updating of %s row is not allowed in %s trigger
1363 HY000 There is no %s row in %s trigger
[/code]
可以在[url]http://dev.mysql.com/doc/[/url]的MySQL reference manual的附录B找到完整的最新的error codes

3，命名条件
MySQL error code或者SQLSTATE code的可读性太差，所以引入了命名条件:
[code]
DECLARE condition_name CONDITION FOR {SQLSTATE sqlstate_code | MySQL_error_code};
[/code]
使用:
[code]
# 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;
[/code]

4，SQL:2003的特性
可以使用SIGNAL语句来触发错误
[code]
SIGNAL SQLSTATE sqlstate_code|condition_name [SET MESSAGE_TEXT=string_or_variable];
[/code]
MySQL5.2才支持 SQL:2003

5，Error Handling的例子
[code]
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

/* START Declare Conditions */

DECLARE duplicate_key CONDITION FOR 1062;
DECLARE foreign_key_violated CONDITION FOR 1216;

/* END Declare COnditions */

/* START Declare variables and cursors */

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);

/* END Declare variables and cursors */

/* START Declare Exception Handlers */

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;

/* END Declare Exception Handlers */

/* START Execution */

SET p_sqlcode=0;
OPEN csr_mgr_id;
FETCH csr_mgr_id INTO l_manager_id;

IF p_sqlcode<>0 THEN /* Failed to get manager id */
SET p_status_message=CONCAT(p_status_message,' when fetching manager id');
ELSE /* Got manager id, we can try and insert */
INSERT INTO departments (department_name, manager_id, location)
VALUES(UPPER(p_department_name), l_manager_id, UPPER(p_location));
IF p_sqlcode<>0 THEN /* Failed to insert new department */
SET p_status_message=CONCAT(p_status_message, ' when inserting new department');
END IF;
END IF;

CLOSE csr_mgr_id;

/* END Execution */

END
[/code]