exception handling of MySQL stored procedures

When insert fails, you may want to record its error information in the log file, such as the cause of the error, the time of the error, and so on. The following code creates a primary key table and a foreign key table. Then the value thrown into the non-primary key table fails:

1Mysql> CREATE TABLET2 (2S1INT,PRIMARY KEY(S1)3) engine=InnoDB//4Mysql> CREATE TABLET3 (5S1INT,KEY(S1),6              FOREIGN KEY(S1)REFERENCEST2 (S1)7) engine=InnoDB//8Mysql> INSERT  intoT3VALUES(5);//9 ...TenERROR1216(23000): CannotAdd or UpdateA child row:aForeign Key  constraintFails (this shows the system error message)

Next, create a table that stores error messages when an Insert action goes wrong: create table Error_log (error_message CHAR) // , and the stored procedure can be written as follows:

1 CREATE PROCEDUREP22 (Parameter1INT)  2 BEGIN3     DECLARE EXITHANDLER for 1216 INSERT  intoError_logVALUES(CONCAT ('Time :',current_date,'. Foreign Key Reference Failure for Value =', Parameter1));4     INSERT  intoT3VALUES(Parameter1); 5 END;//

The first sentence of the above code declare EXIT handler is used to handle exceptions, meaning that if a 1215 error occurs, the program throws a row into the log table. Exit means to exit the compound statement when the action is successfully committed.

1. Syntax for exception handling:

1 DECLARE EXIT | CONTINUE  {Error-number| {SQLSTATE error-|

MySQL allows two types of processors: Exit and continue, which exits after the error-handling code is executed, and the latter can still continue execution.

2. Define conditional exception handling:

1 CREATE PROCEDUREp24 ()2 BEGIN3     DECLARE`ConstraintViolation ' CONDITION forSQLSTATE'23000';4     DECLARE EXITHANDLER for`ConstraintViolation 'ROLLBACK;5STARTTRANSACTION;6     INSERT  intoT2VALUES(1);7     INSERT  intoT2VALUES(1);8     COMMIT; 9 END;// 

The above code can give sqlstate or other name of the error code, in order to use the custom name in processing, the T2 primary key to the table insert the same value will cause SQLSTATE 23000 (constraint error).

DECLARE 'Constraint violation ' CONDITION for SQLSTATE '23000  '; Declares a "condition" for the specified error, paying attention to the syntax format.

DECLARE EXIT HANDLER for 'Constraint violation ' ROLLBACK; To define exception handling using the claims ' conditions '

You can also use pre-declared conditions as follows:

1Mysql>CREATEPROCEDUREp9 ()2 -BEGIN  3 -DECLAREEXITHANDLER for notFOUNDBEGIN ...END;/*No rows found*/4  -DECLAREEXITHANDLER forSQLEXCEPTIONBEGIN ...END;/*Error*/5 -DECLAREEXITHANDLER forSQLWarningBEGIN ...END;/*Warning*/6 -END;//

exception handling of MySQL stored procedures