MySQL Small example of stored procedures with parameters

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

MySQL stored procedures also provide functionality for exception handling: the implementation of exception declarations is accomplished by defining handler

The syntax is as follows:

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 Type:

1, exit: Exits the current code block (possibly a child code block or a main code block) when an error occurs
2, CONTINUE: Continue execution of subsequent code when sending an error

Condition_value:

Condition_value supports the SQLSTATE definition of the standard;

SQLWarning is shorthand for all SQLSTATE codes starting with 01

Not found is shorthand for all SQLSTATE codes that begin with 02

SqlException is shorthand for all SQLSTATE codes that are not captured by sqlwarning or not found

In addition to the SQLSTATE value, the MySQL error code is also supported

But for MySQL, the priority is as follows:
MySQL Error code > SQLSTATE code > naming criteria

Use SQLState or MySQL Error Code?

1,salstate is the standard, seemingly will be more portable, but actually MySQL, DB2, Oracle and so on the storage program syntax is very different, so portable advantage does not exist
2,mysql error code and SQLSTATE are not one by one corresponding, such as many MySQL error code are mapped to the same SQLState code ( HY000)

When the MySQL client encounters an error, it reports the MySQL error code and the associated Sqlsate code:

MySQL > Call nosuch_sp ();
ERROR 1305 (42000): PROCEDURE sqltune.nosuch_sp does not exist

The specific sqlsdate and MySQL error code correspondence can be found in the http://dev.mysql.com/doc/MySQL reference manual Appendix B to find the complete latest error codes

Condition_name: Naming criteria

MySQL error code or SQLSTATE code is poorly readable, so a naming condition is introduced:

Grammar:

Java code

1. DECLARE condition_name Condition for Condition_value
3. Condition_value:
4. SQLSTATE [VALUE] Sqlstate_value
5. | Mysql_error_code

Use:

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;

An individual name was used for the error code with Condition_name.

Example 1:duplicate entry Handler

SQL code

2. CREATE PROCEDURE sp_add_location
4. (In_location VARCHAR (),
6. In_address1 VARCHAR (+),
8. In_address2 VARCHAR (+),
10. ZipCode VARCHAR (Ten),
12. out Out_status VARCHAR (+))
14. BEGIN
16. DECLARE CONTINUE HANDLER
18. For 1062
20. SET out_status=' Duplicate Entry ';
24. SET out_status=' OK ';
26. INSERT into locations
28. (Location,address1,address2,zipcode)
30. VALUES
32. (In_location,in_address1,in_address2,zipcode);
34. END;

Example 2:last Row Handler

SQL code

2. CREATE PROCEDURE sp_not_found ()
4. READS SQL DATA
6. BEGIN
8. DECLARE l_last_row INT DEFAULT 0;
10. DECLARE l_dept_id INT:
12. DECLARE c_dept CURSOR for
14. SELECT department_id from departments;
16. DECLARE CONTINUE HANDLER for not FOUND SET l_last_row=1;
20. OPEN c_dept;
22. Dept_cursor:loop
24. FETCH c_dept into l_dept_id;
26. IF (l_last_row=1) Then
28. LEAVE Dept_cursor;
30. END IF;
32. END LOOP dept_cursor;
34. CLOSE c_dept;
36. END;

Comprehensive Example:

SQL code

2. CREATE PROCEDURE sp_add_department
4. (P_department_name VARCHAR (),
6. P_manager_surname VARCHAR (+),
8. P_manager_firstname VARCHAR (+),
10. P_location VARCHAR (+),
12. Out p_sqlcode INT,
14. out P_status_message VARCHAR (+))
16. BEGIN
20. /* START Declare Conditions * /
24. DECLARE Duplicate_key CONDITION for 1062;
26. DECLARE foreign_key_violated CONDITION for 1216;
30. /* END Declare COnditions * /
34. /* START Declare variables and cursors * /
38. DECLARE l_manager_id INT;
40. DECLARE csr_mgr_id CURSOR for
42. SELECT employee_id from employees
44. WHERE surname=UPPER (p_manager_surname)
46. and firstname=UPPER (p_manager_firstname);
50. /* END Declare variables and cursors * /
54. /* START Declare Exception handlers * /
58. DECLARE CONTINUE HANDLER for duplicate_key
60. BEGIN
62. SET p_sqlcode=1052;
64. SET p_status_message=' Duplicate key error ';
66. END;
70. DECLARE CONTINUE HANDLER for foreign_key_violated
72. BEGIN
74. SET p_sqlcode=1216;
76. SET p_status_message=' Foreign key violated ';
78. END;
82. DECLARE CONTINUE HANDLER for not FOUND
84. BEGIN
86. SET p_sqlcode=1329;
88. SET p_status_message=' No record found ';
90. END;
94. /* END Declare Exception handlers * /
98. /* START Execution */
102. SET p_sqlcode=0;
104. OPEN csr_mgr_id;
106. FETCH csr_mgr_id into l_manager_id;
110. IF p_sqlcode<>0 then/ * Failed to get Manager ID * /
112. SET P_status_message=concat (p_status_message,' when fetching manager ID ');
114. ELSE/* Got Manager ID, we can try and Insert * /
116. INSERT into departments (Department_name, manager_id, location)
118. VALUES (UPPER (p_department_name), l_manager_id, UPPER (p_location));
120. IF p_sqlcode<>0 then/ * Failed to Insert new Department * /
122. SET P_status_message=concat (p_status_message, ' when inserting new Department ');
124. END IF;
126. END IF;
130. CLOSE csr_mgr_id;
134. /* END execution * /
138. END

MySQL Small example of stored procedures with parameters