Mysql 存储过程之 Error Handing (转)

1，简介
MySQL支持HANDLER来处理错误：
Duplicate entry Handler

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;  

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

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

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;

2，Handlers
语法:

1. DECLARE {CONTINUE | EXIT} HANDLER FOR   
2.     {SQLSTATE sqlstate_code | MySQL error code | condition_name}   
3.     handler_actions  

DECLARE {CONTINUE | EXIT} HANDLER FOR
{SQLSTATE sqlstate_code | MySQL error code | condition_name}
handler_actions

Handlers类型:

1. 1 , EXIT: 发生错误时退出当前代码块（可能是子代码块或者main代码块）   
2. 2 , CONTINUE: 发送错误时继续执行后续代码  

1, EXIT: 发生错误时退出当前代码块（可能是子代码块或者main代码块）
2, CONTINUE: 发送错误时继续执行后续代码

Handlers条件:

1. 1 , MySQL error code，如1062   
2. 2 , ANSI标准SQLSTATE code，如23000   
3. <