"Exception handling" for MySQL stored procedures

Q: What is an exception?

A: The program may go wrong during execution and the run-time error is called an exception.

By default, when a stored procedure runs out of error, the procedure terminates immediately and the system error message is printed.

Lab Environment:

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

mysql> use tennisreading table information for completion of table and column namesyou can turn off this feature to get A quicker startup With-adatabase changedmysql> show tables;+-------------------+| Tables_in_tennis |+-------------------+| Committee_members | | MATCHES | | Penalties | | PLAYERS | | TEAMS |+-------------------+5 rows in Set (0.00 sec)

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Because most of the previous experiments use this database, the library table structure is no longer described.

Example: Creating a process to insert a duplicate team of number 2nd

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Mysql> delimiter $ $mysql > CREATE PROCEDURE duplicate_teams (out p_processed smallint), Begin-    > Set p_processed=1;    INSERT into TEAMS values (2,27, ' third ');    Set p_processed=2; -End $ $mysql > delimiter;mysql> call Duplicate_teams (@processed); ERROR 1062 (23000): Duplicate entry ' 2 ' for key ' PRIMARY ' mysql> select @processed; +------------+| @processed |+------------+| NULL |+------------+

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Parse: Client calls stored procedure, run error, print error message, process is terminated, no output.

First, define exception handling:

DECLARE ... Handler statement:

Through the definition and processing of the condition, it is possible to make corresponding processing steps in the definition process for the problems that may be encountered. (That is, define an exception handler that specifies what action to take when a statement in the procedure goes wrong)

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

DECLARE handler_action handler for Condition_value [, Condition_value] ... statementhandler_action:continue | Exitcondition_value:mysql_error_code | SQLSTATE [VALUE] sqlstate_value | Condition_name | sqlwarning | Not FOUND | SQLEXCEPTION

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Note: The Declare......handler statement must appear after the variable or conditional declaration.

When an error (Condition_value) occurs---> executes the specified statement (statement--logs an error message) and then decides what to do (handler_action).

1, Handler_action

Continue: Continue executing the current program (followed by the next statement of the SQL that went wrong);

Exit: The current program terminates (exits the begin end where the current declare is located);

The Undo feature is not currently supported.

2, statement

Can be a single statement or compound statement.

3, Condition_value indicates what conditions handler is triggered, if the condition is triggered, but no handler is declared to handle the condition, the procedure will depend on the condition type.

"Top"

Second, a single exception handling program

The SQLSTATE code that contains the error message in the output parameter.

Example 1: Create a process to insert a duplicate team of number 2nd; continue

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

mysql> delimiter $ $mysql > create  procedure small_mistake1 (      ->  Out error varchar (5))       ->  begin    ->  declare continue handler for sqlstate  ' 23000 '      ->  set error =  ' 23000 ';     # Used to record some information when an error occurred     ->               ->  select error;    ->  set error =  ' 00000 ';    ->  select error;    ->     insert into teams values (2,27, ' third ');   #会出错的语句     ->     SET error =  ' 23001 ';          -> end$$Mysql> delimiter ;mysql> call small_mistake1 (@a); +-------+| error |+------ -+| null  |+-------+ +-------+| error |+-------+| 00000 |+-------+mysql>  select @a;+-------+| @a    |+-------+| 23001 |+-------+

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Examples of experiments are resolved:

Begin end block, define Declare......handler statement to catch error (standby ing), select, set, select Order execution, insert statement error, SQLSTATE code 23000, capture, exception handling (Assignment record), After the end, the next statement of the INSERT statement will continue to execute in error ...

Example 2: Create a process and insert a duplicate team of number 2nd; exit

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

mysql> delimiter $ $mysql > create  procedure small_mistake2 (      ->  Out error varchar (5))       ->  begin    ->  declare exit handler for sqlstate  ' 23000 '      ->  set error =  ' 23000 ';    ->                       ->  select error;    ->  set error =  ' 00000 ';    ->  select error;    ->  INSERT  Into teams values (2,27, ' third ');    ->  set error =  ' 23001 ';          -> end$ $mysql > delimiter ;mysql > call&nBsp;small_mistake2 (@a), +-------+| error |+-------+| null  |+-------+ +-------+|  error |+-------+| 00000 |+-------+mysql> select @a;+-------+| @a     |+-------+| 23000 |+-------+

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

The only difference from Example 1 is that handler_action chooses exit, indicating that the statement following the error statement is not resumed after the exception processing ends and exits the begin END statement block directly.

"Top"

Three, multiple exception handlers

Multiple exception handlers can be defined in one procedure, and different processing is done for different errors.

Example 1:

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Mysql> insert into teams values (2,27, ' third ', 5); error 1136  (21s01):  column count doesn ' T match value count at  row 1mysql> delimiter $ $mysql > create  procedure small_mistake3 (     ->  Out error varchar (5))       - > BEGIN    ->  declare continue handler for sqlstate   ' 23000 '       ->  set error =  ' 23000 ';      ->       ->  Declare continue handler  FOR SQLSTATE  ' 21s01 '      ->  set error =  ' 21s01 ';    ->         ->  INSERT  Into teams values (2,27, ' third ', 5);    #错误语句        -> end$ $mysql > DELIMITER ;mysql>  call small_mistake3 (@error);mysql> select  @error, +--------+|  @error  |+------ --+| 21s01  |+--------+

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Analytical:

If the SQLSTATE code is 23000, exception handling performs set error = ' 23000 ';

If the SQLSTATE code is 21S01, exception handling performs set error = ' 21s01 ';

Of course, exception handling, which can be custom, is usually the error message record in the above way.

The exception handler in Example 3 can also use the error number

For example:

DECLARE CONTINUE HANDLER for 1062 SET error = ' 23000 '; DECLARE CONTINUE HANDLER for 1136 SET error = ' 21s01 ';

About the error number and SQLSTATE code:

Each MySQL error has a unique numeric error number (Mysql_error_code), and each error corresponds to a 5-character SQLState code (ANSI SQL Adoption).

SQLState code corresponding to the processing program:

1, sqlwarning processing program: "01" to start with all SQLSTATE code corresponding;

2, not found processing program: the ' 02 ' beginning with all sqlstate code corresponding;

3. SqlException handler: All SQLSTATE codes that do not start with ' 01 ' or ' 02 ', that is, all sqlstate that are not captured by sqlwarning or not found (often encountered MySQL errors that begin with non-' 01 ', ' 02 ')

Note: ' 01 ', ' 02 ' and ' 1 ', ' 2 ' start with a difference, is not the same error sqlsate code.

When you do not want to define a handler for each error, you can use 3 handlers

E.g:declare CONTINUE HANDLER for Sqlwarning,not found,sqlexception

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

Mysql> DELIMITER $ $mysql > CREATE PROCEDURE small_mistake4 (with out error VARCHAR (5)), BEGIN-& 　　Gt      DECLARE CONTINUE HANDLER for Sqlwarning,not found,sqlexception, SET error = ' xxxxx ';       INSERT into teams VALUES (2,27, ' third '); -end$ $mysql > DELIMITER;mysql> call Small_mistake4 (@a);mysql> Select @a;+-------+| @a |+-------+| XXXXX |+-------+

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

If you are defining a handler, you want to ignore a condition

E.g:declare CONTINUE HANDLER for sqlwarning BEGIN END;

In other words, when encountering a sqlwarning problem, the exception handling is the begin end block, because there is nothing in it, just like ignoring it directly.

"Top"

Iv. Naming of exception handling

For readability, you can give a name to a SQLSTATE code or MySQL error code, and use that name in a later exception handler.

DECLARE condition_name condition for Condition_valuecondition_value:mysql_error_code | SQLSTATE [VALUE] Sqlstate_value

1. Unnamed basic format:

BEGIN DECLARE CONTINUE HANDLER for 1051-body of handlerend;

2. A named basic format:

BEGIN DECLARE no_such_table CONDITION for 1051; DECLARE CONTINUE HANDLER for no_such_table-Body of handlerend;

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

mysql> delimiter $ $mysql > create  procedure small_mistake5 (      ->  Out error varchar (5))       ->  begin    ->  declare non_unique condition for sqlstate  ' 23000 ';      ->  declare continue handler for non_ unique    ->  begin     ->  SET error =   ' 23000 ';    ->  select error;    ->  end;     ->    ->  insert into teams values (2,27, ' Third ');   #会出错语句       -> end$ $mysql > DELIMITER ; Mysql> call small_mistake5 (@error); +-------+| error |+-------+| 23000 |+----- --+

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

"Top"

V. Abnormal propagation

In the case of nested blocks, an exception occurs in the inner block, first handled by the exception handler for this block, and if this block is not processed, it is handled by an exception handler for the external block.

650) this.width=650; "src="/img/fz.gif "alt=" Copy Code "style=" Margin:0px;padding:0px;border:none; "/>

mysql> delimiter $ $mysql > create  procedure small_mistake6 ()        -> BEGIN        ->  DECLARE  CONTINUE HANDLER FOR SQLSTATE  ' 23000 '      ->  SET　　  @processed  = 100;      ->    ->  begin          ->  DECLARE CONTINUE HANDLER  FOR SQLSTATE  ' 21000 '      ->  set  @processed  =  200;        ->  insert into teams values (2,27, ' Third ');       ->  set  @test =123321;       ->    end;    -> end$ $mysql > delimiter& NBsp;; mysql> call small_mistake6;mysql> select  @processed, @test; +------------+--------+|   @processed  |  @test  |+------------+--------+| 300 | 123321 |+--------- ---+--------+

"Exception handling" for MySQL stored procedures