Example of a MySQL stored procedure with parameters:

Example of a MySQL stored procedure with parameters:

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

 

The mysql Stored Procedure also provides the exception handling function: the implementation of exception declaration is completed by defining HANDLER.

 

 

Syntax:

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

Handlers type:

1. EXIT: EXIT the current code block when an error occurs (it may be a Subcode block or a main code block)
2. CONTINUE: CONTINUE executing the subsequent code when an error is sent

Condition_value:

Condition_value supports standard SQLSTATE definitions;

SQLWARNING is a shorthand for all SQLSTATE codes starting with 01.

Not found is a shorthand for all SQLSTATE codes starting with 02.

SQLEXCEPTION is a shorthand for all SQLSTATE code NOT captured by SQLWARNING or not found.

Besides the SQLSTATE value, the MySQL error code is also supported.

 

However, for mysql, the priority is as follows:
MySQL Error code> SQLSTATE code> naming Conditions

 

 

 

 

Use SQLSTATE or MySQL Error Code?

1. SALSTATE is a standard and seems to be more portable, but in fact, the syntax of MySQL, DB2, Oracle and so on is quite different, so the advantage of portable does not exist.
2. the MySQL error code and SQLSTATE do not correspond one by one. For example, many MySQL error codes are mapped to the same SQLSTATE code (HY000)

When the MySQL client encounters an error, it will report the MySQL error code and related SQLSATE code:

Mysql> CALL nosuch_sp ();
ERROR 1305 (42000): PROCEDURE sqltune. nosuch_sp does not exist

You can find the latest error codes in Appendix B of mysql reference manual at http://dev.mysql.com/doc /.

 

Condition_name: naming Condition

MySQL error code or SQLSTATE code is too readable, so the naming conditions are introduced:

Syntax:

Java code

1. DECLARE condition_name condition for condition_value
3. Condition_value:
4. SQLSTATE [VALUE] sqlstate_value
5. | Mysql_error_code

Usage:

 

Java code

1. # Original
2. Declare continue handler for 1216 MySQL_statements;
4. # Changed
5. DECLARE foreign_key_error condition for 1216;
6. Declare continue handler for foreign_key_error MySQL_statements;

UseCondition_name is an alias for the error code.

 

Example 1: Duplicate entry Handler

 

SQL code

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

 

Example 2: Last Row Handler

 

 

SQL code

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
7. SELECT department_id FROM orders;
8. Declare continue handler for not found set l_last_row = 1;
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;

 

Comprehensive example:

SQL code

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
10. /* START Declare Conditions */
12. DECLARE duplicate_key condition for 1062;
13. DECLARE foreign_key_violated condition for 1216;
15. /* END Declare COnditions */
17. /* START Declare variables and cursors */
19. DECLARE l_manager_id INT;
20. DECLARE csr_mgr_id CURSOR
21. SELECT employee_id FROM employees
22. WHERE surname = UPPER (p_manager_surname)
23. AND firstname = UPPER (p_manager_firstname );
25. /* END Declare variables and cursors */
27. /* START Declare Exception Handlers */
29. Declare continue handler for duplicate_key
30. BEGIN
31. SET p_sqlcode = 1052;
32. SET p_status_message = 'duplicate key error ';
33. END;
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;
41. DECLARE CONTINUE HANDLER FOR NOT FOUND
42. BEGIN
43. SET p_sqlcode = 1329;
44. SET p_status_message = 'no record found ';
45. END;
47. /* END Declare Exception Handlers */
49. /* START Execution */
51. SET p_sqlcode = 0;
52. OPEN csr_mgr_id;
53. FETCH csr_mgr_id INTO l_manager_id;
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 orders ments (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;
65. CLOSE csr_mgr_id;
67. /* END Execution */
69. END