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