In MySQL, how can we declare a handler while handling errors?

MySQLMySQLi Database

It is very important to handle the errors by throwing a proper error message. MySQL provides a handler to handle the error. We can declare a handler with the help of the following syntax −

Syntax of handler

DECLARE handler_action FOR condition_value statement;

The above syntax shows that we need to use DECLARE HANDLER statement to declare a handler. If a condition whose value matches the condition_value then MySQL will execute the statement and continue or exit the current code block based on the action. Followings are the three major things in the above syntax −

  • Handler_action is of two types and can accept one of the following values −

    • CONTINUE − If the handler_action is ‘CONTINUE’ then the execution of the enclosing code block continues.
    • EXIT − If the handler_action is ‘EXIT’ then the execution of the enclosing code block terminates.
  • Condition_value specifies a particular condition or class of conditions that activate the handler. It can accept one of the following values −

    • ERROR CODE − The condition_value can be a MySQL error code.
    • SQLSTATE − The condition_value can be an SQLSTATE also.
    • SQLWARNING − The condition_value can be an SQLWARNING also.
    • NOTFOUND − The condition_value can be a NOTFOUND also.
    • SQLEXCEPTION − The condition_value can be an SQLEXCEPTION also.
  • The Statement can be a simple statement or compound statement enclosing by the BEGIN and END keywords.

Example

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET got_error = 1;

In the above example, a handler means that if an error occurs then set the value of the got_error variable to 10 and continues the execution.

raja
Published on 21-Feb-2018 10:00:26
Advertisements