MySQL帶參數的存儲過程小例子

http://wwty.iteye.com/blog/698239

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的可讀性太差，所以引入了命名條件:

語法：

1. DECLARE condition_name CONDITION FOR condition_value  
2.   
3. condition_value:  
4.     SQLSTATE [VALUE] sqlstate_value  
5.   | mysql_error_code  

 使用: 

1. # original  
2. DECLARE CONTINUE HANDLER FOR 1216 MySQL_statements;  
3.   
4. # changed  
5. DECLARE foreign_key_error CONDITION FOR 1216;  
6. DECLARE CONTINUE HANDLER FOR foreign_key_error MySQL_statements;  

  用condition_name為錯誤代碼起了個別名。

示例1：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;  

示例2： 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;  

綜合示例：

1. CREATE PROCEDURE sp_add_department  
2.     (p_department_name     VARCHAR(30),  
3.      p_manager_surname     VARCHAR(30),  
4.      p_manager_firstname   VARCHAR(30),  
5.      p_location            VARCHAR(30),  
6.      OUT p_sqlcode         INT,  
7.      OUT p_status_message  VARCHAR(100))  
8. BEGIN  
9.   
10.     /* START Declare Conditions */  
11.   
12.     DECLARE duplicate_key CONDITION FOR 1062;  
13.     DECLARE foreign_key_violated CONDITION FOR 1216;  
14.   
15.     /* END Declare COnditions */  
16.   
17.     /* START Declare variables and cursors */  
18.   
19.     DECLARE l_manager_id INT;  
20.     DECLARE csr_mgr_id CURSOR FOR  
21.         SELECT employee_id FROM employees  
22.         WHERE surname=UPPER(p_manager_surname)  
23.         AND firstname=UPPER(p_manager_firstname);  
24.   
25.     /* END Declare variables and cursors */  
26.   
27.     /* START Declare Exception Handlers */  
28.   
29.     DECLARE CONTINUE HANDLER FOR duplicate_key  
30.     BEGIN  
31.         SET p_sqlcode=1052;  
32.         SET p_status_message='Duplicate key error';  
33.     END;  
34.   
35.     DECLARE CONTINUE HANDLER FOR foreign_key_violated  
36.     BEGIN  
37.         SET p_sqlcode=1216;  
38.         SET p_status_message='Foreign key violated';  
39.     END;  
40.   
41.     DECLARE CONTINUE HANDLER FOR NOT FOUND  
42.     BEGIN  
43.         SET p_sqlcode=1329;  
44.         SET p_status_message='No record found';  
45.     END;  
46.   
47.     /* END Declare Exception Handlers */  
48.   
49.     /* START Execution */  
50.   
51.     SET p_sqlcode=0;  
52.     OPEN csr_mgr_id;  
53.     FETCH csr_mgr_id INTO l_manager_id;  
54.   
55.     IF p_sqlcode<>0 THEN     /* Failed to get manager id */  
56.         SET p_status_message=CONCAT(p_status_message,' when fetching manager id');  
57.     ELSE                     /* Got manager id, we can try and insert */  
58.         INSERT INTO departments (department_name, manager_id, location)  
59.         VALUES(UPPER(p_department_name), l_manager_id, UPPER(p_location));  
60.         IF p_sqlcode<>0 THEN /* Failed to insert new department */  
61.             SET p_status_message=CONCAT(p_status_message, ' when inserting new department');  
62.         END IF;  
63.     END IF;  
64.   
65.     CLOSE csr_mgr_id;  
66.   
67.     /* END Execution */  
68.   
69. END