MySQL存储程序之Error Handling
1，简介MySQL支持HANDLER来处理错误：
Duplicate entry Handler
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;
Last Row Handler
CREATE PROCEDURE sp_not_found() READS SQL DATABEGIN 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;
2，Handlers
语法:
DECLARE {CONTINUE | EXIT} HANDLER FOR {SQLSTATE sqlstate_code | MySQL error code | condition_name} handler_actions
Handlers类型:
1, EXIT: 发生错误时退出当前代码块（可能是子代码块或者main代码块）2, CONTINUE: 发送错误时继续执行后续代码
Handlers条件:
1, MySQL error code，如10622, ANSI标准SQLSTATE code，如230003, 命名条件，如NOT FOUND
优先级：
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
上面Error code是1305，SQLSTATE code是42000
常见的MySQL error code和SQLSTATE code:
MySQL error code SQLSTATE code Error message1011 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 change1036 HY000 Table '%s' is read only1048 23000 Column '%s' cannot be null1062 23000 Duplicate entry '%s' for key %d1099 HY000 Table '%s' was locked with a READ lock and can't be updated1100 HY000 Table '%s' was not locked with LOCK TABLES1104 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 okay1106 42000 Incorrect parameters to procedure '%s'1114 HY000 The table '%s' is full1150 HY000 Delayed insert thread couldn't get requested lock for table %s1165 HY000 INSERT DELAYED can't be used with table '%s' because it is locked with LOCK TABLES1242 21000 Subquery returns more than 1 row1263 22004 Column set to default value; NULL supplied to NOT NULL column '%s' at row %ld1264 22003 Out of range value adjusted for column '%s' at row %ld1265 1000 Data truncated for column '%s' at row %ld1312 0A000 SELECT in a stored program must have INTO1317 70100 Query execution was interrupted1319 42000 Undefined CONDITION: %s1325 24000 Cursor is already open1326 24000 Cursor is not open1328 HY000 Incorrect number of FETCH variables1329 2000 No data to FETCH1336 42000 USE is not allowed in a stored program1337 42000 Variable or condition declaration after cursor or handler declaration1338 42000 Cursor declaration after handler declaration1339 20000 Case not found for CASE statement1348 HY000 Column '%s' is not updatable1357 HY000 Can't drop a %s from within another stored routine1358 HY000 GOTO is not allowed in a stored program handler1362 HY000 Updating of %s row is not allowed in %s trigger1363 HY000 There is no %s row in %s trigger
可以在http://dev.mysql.com/doc/的MySQL reference manual的附录B找到完整的最新的error codes
3，命名条件
MySQL error code或者SQLSTATE code的可读性太差，所以引入了命名条件:
DECLARE condition_name CONDITION FOR {SQLSTATE sqlstate_code | MySQL_error_code};
使用:
# originalDECLARE CONTINUE HANDLER FOR 1216 MySQL_statements;# changedDECLARE foreign_key_error CONDITION FOR 1216;DECLARE CONTINUE HANDLER FOR foreign_key_error MySQL_statements;
4，SQL:2003的特性
可以使用SIGNAL语句来触发错误
SIGNAL SQLSTATE sqlstate_code|condition_name ;
MySQL5.2才支持 SQL:2003
5，Error Handling的例子
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_messageVARCHAR(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
页:
[1]