Exception Handling in MySQL

Exception Handling

Exception Handling is a technique which helps smooth execution of code in case of any known/unknown issues

Declare for Conditions

Certain conditions may require specific handling. These conditions can relate to errors or warnings, as well as to general flow control inside a stored program.

General: Here we declare a specific name to a specific error number. These error numbers are pre-defined mysql errors. When a particular error number exception occurs, the condition associated is fired and relating actions are done.

Syntax

DECLARE condition_name CONDITION FOR condition_value;

condition_value:

    SQLSTATE [VALUE] sqlstate_value

  | mysql_error_code

The DECLARE ... CONDITION statement defines a named error condition. It specifies a condition that needs specific handling and associates a name with that condition. The name can be referred to in a subsequent DECLARE ... HANDLER statement.

A condition_value for DECLARE ... CONDITION can be an SQLSTATE value (a 5-character string literal) or a MySQL error code (a number). You should not use SQLSTATE value '00000' or MySQL error code 0, because those indicate success rather than an error condition.

Below is an Example:

We have a table s1 as follows with ‘s’ as primary key

s	sname
1	Name1
2	Name2

Now try to insert a row with values (1,’name3’). We interpret it with the procedure

CREATE PROCEDURE p1 (IN p_s int (2), IN p_sname varchar (5), OUT p_status varchar (30))

BEGIN

DECLARE duplicates CONDITION FOR SQLSTATE '23000';

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

SET p_status ='Out in middle';

END;

Insert into s1 values (p_s, p_sname);

END;

Execute

Call p1 (1,'name3', @s);

Select @s;

OUTPUT:

Out in middle

In the above procedure we declared a condition for duplicates.

i.e. for returning a message when we try to insert duplicate values

DECLARE for Handlers

DECLARE handler_type HANDLER

    FOR condition_value [, condition_value] ...

    statement

handler_type:

    CONTINUE

  | EXIT

  | UNDO

condition_value:

    SQLSTATE [VALUE] sqlstate_value

  | condition_name

  | SQLWARNING

  | NOT FOUND

  | SQLEXCEPTION

  | mysql_error_code

The DECLARE ... HANDLER statement specifies handlers that each may deal with one or more conditions. If one of these conditions occurs, the specified statement is executed. Statement can be a simple statement (for example, SET var_name = value), or it can be a compound statement written using BEGIN and END

For a CONTINUE handler, execution of the current program continues after execution of the handler statement. For an EXIT handler, execution terminates for the BEGIN ... END compound statement in which the handler is declared. (This is true even if the condition occurs in an inner block.) The UNDO handler type statement is not supported.

If a condition occurs for which no handler has been declared, the default action is EXIT.

A condition_value for DECLARE ... HANDLER can be any of the following values:

·         An SQLSTATE value (a 5-character string literal) or a MySQL error code (a number). You should not use SQLSTATE value '00000' or MySQL error code 0, because those indicate success rather than an error condition. 

Create procedure p1 (in p_s int (2), in p_sname varchar (5), out p_status varchar (30))

Begin

Declare Continue handler for SQLSTATE '23000'

Begin

Set p_status = 'In SQLSTATE 23000';

End;

Insert into s1 Values (p_s, p_sname);

desc s1;

End;

//

Call P1 (2,'name3',@s);

Select @S//

Output

In SQLSTATE 23000

       Field                Type                       Null        Key         Default   Extra

s               int(2)                        NO        PRI         0

sname            varchar(5)               YES        NULL

The example associates a handler with SQLSTATE value '23000', which occurs for a duplicate-key error. Notice that p_status is In SQLSTATE 23000 after the procedure executes, and the describe command shows the structure of the table, which shows that execution continued to the end of the procedure. If the DECLARE ... HANDLER statement had not been present, MySQL would have taken the default path (EXIT) after the second INSERT failed due to the PRIMARY KEY constraint.

·         A condition name previously specified with DECLARE ... CONDITION.

See Declare for Conditions Example Above

·         SQLWARNING is shorthand for the class of SQLSTATE values that begin with '01'.

·         NOT FOUND is shorthand for the class of SQLSTATE values that begin with '02'. This is relevant only within the context of cursors and is used to control what happens when a cursor reaches the end of a data set. If no more rows are available, a No Data condition occurs with SQLSTATE value 02000. To detect this condition, you can set up a handler for it (or for a NOT FOUND condition).

·         This condition also occurs for SELECT ... INTO var_list statements that retrieve no rows.

·         SQLEXCEPTION is shorthand for the class of SQLSTATE values that do not begin with '00', '01', or '02'.

If you want to ignore a condition, you can declare a CONTINUE handler for it and associate it with an empty block. For example:

DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;

Source:

http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html