It is very important to handle the Exceptions in stored procedures by exiting the code by providing a proper error message or by exiting or continuing the execution. We can define handlers in MySQL to handle the error causing conditions.
How To Declare a handler
The syntax to write a handler is:
In the above syntax, MySQL will perform the action if the condition matches the condition_value. Here, action may contain one of the following values:
condition_value activates the handler on specific condition or class of conditions. It accepts one of the following values:
In the above code, if an SQLEXCEPTION occur, the handler will set the value of hasError to 1.
Rollback on an Error
Here is an example of rollback previous operations and genarate an error message with the code termination on sql exception.
Implementation of Handler in Stored Procedure
MySQL handler example in stored procedure
In this example, error code 1062 signifies duplicate key. Duplicate values in key will activate the handler that willl generate the error message and terminate the code block. Otherwise, if key is unique, it will insert the values in Student table.
If we use multiple handlers, MySQL will call the most specific handler to handle the errors which involves priority.