MySQL Small example of stored procedures with parameters

Source: Internet
Author: User
Tags mysql client

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
    2. Condition_value:
    3. SQLSTATE [VALUE] Sqlstate_value
    4. | Mysql_error_code

Use:

Java code
    1. # Original
    2. DECLARE CONTINUE HANDLER for 1216 mysql_statements;
    3. # changed
    4. DECLARE foreign_key_error CONDITION for 1216;
    5. 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
  1. CREATE PROCEDURE sp_add_location
  2. (In_location VARCHAR (),
  3. In_address1 VARCHAR (+),
  4. In_address2 VARCHAR (+),
  5. ZipCode VARCHAR (Ten),
  6. out Out_status VARCHAR (+))
  7. BEGIN
  8. DECLARE CONTINUE HANDLER
  9. For 1062
  10. SET out_status=' Duplicate Entry ';
  11. SET out_status=' OK ';
  12. INSERT into locations
  13. (Location,address1,address2,zipcode)
  14. VALUES
  15. (In_location,in_address1,in_address2,zipcode);
  16. 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 for
  7. SELECT department_id from departments;
  8. DECLARE CONTINUE HANDLER for not FOUND SET l_last_row=1;
  9. OPEN c_dept;
  10. Dept_cursor:loop
  11. FETCH c_dept into l_dept_id;
  12. IF (l_last_row=1) Then
  13. LEAVE Dept_cursor;
  14. END IF;
  15. END LOOP dept_cursor;
  16. CLOSE c_dept;
  17. END;

Comprehensive Example:

SQL code
    1. CREATE PROCEDURE sp_add_department
    2. (P_department_name VARCHAR (),
    3. P_manager_surname VARCHAR (+),
    4. P_manager_firstname VARCHAR (+),
    5. P_location VARCHAR (+),
    6. Out p_sqlcode INT,
    7. out P_status_message VARCHAR (+))
    8. BEGIN
    9. /* START Declare Conditions * /
    10. DECLARE Duplicate_key CONDITION for 1062;
    11. DECLARE foreign_key_violated CONDITION for 1216;
    12. /* END Declare COnditions * /
    13. /* START Declare variables and cursors * /
    14. DECLARE l_manager_id INT;
    15. DECLARE csr_mgr_id CURSOR for
    16. SELECT employee_id from employees
    17. WHERE surname=UPPER (p_manager_surname)
    18. and firstname=UPPER (p_manager_firstname);
    19. /* END Declare variables and cursors * /
    20. /* START Declare Exception handlers * /
    21. DECLARE CONTINUE HANDLER for duplicate_key
    22. BEGIN
    23. SET p_sqlcode=1052;
    24. SET p_status_message=' Duplicate key error ';
    25. END;
    26. DECLARE CONTINUE HANDLER for foreign_key_violated
    27. BEGIN
    28. SET p_sqlcode=1216;
    29. SET p_status_message=' Foreign key violated ';
    30. END;
    31. DECLARE CONTINUE HANDLER for not FOUND
    32. BEGIN
    33. SET p_sqlcode=1329;
    34. SET p_status_message=' No record found ';
    35. END;
    36. /* END Declare Exception handlers * /
    37. /* START Execution */
    38. SET p_sqlcode=0;
    39. OPEN csr_mgr_id;
    40. FETCH csr_mgr_id into l_manager_id;
    41. IF p_sqlcode<>0 then/ * Failed to get Manager ID * /
    42. SET P_status_message=concat (p_status_message,' when fetching manager ID ');
    43. ELSE/* Got Manager ID, we can try and Insert * /
    44. INSERT into departments (Department_name, manager_id, location)
    45. VALUES (UPPER (p_department_name), l_manager_id, UPPER (p_location));
    46. IF p_sqlcode<>0 then/ * Failed to Insert new Department * /
    47. SET P_status_message=concat (p_status_message, ' when inserting new Department ');
    48. END IF;
    49. END IF;
    50. CLOSE csr_mgr_id;
    51. /* END execution * /
    52. END

MySQL Small example of stored procedures with parameters

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.