"Exception handling" for MySQL stored procedures

Lab Environment:

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

Mysql> Use Tennis
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a

Database changed
Mysql> 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] ...
Statement

Handler_action:
CONTINUE | EXIT

Condition_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; "/>

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; "/>

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

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

-Out Error VARCHAR (5))
BEGIN, DECLARE CONTINUE HANDLER for Sqlwarning,not found,sqlexception
SET error = ' xxxxx ';
-

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_value
Condition_value:mysql_error_code | SQLSTATE [VALUE] Sqlstate_value

1. Unnamed basic format:

BEGIN
DECLARE CONTINUE HANDLER for 1051
--Body of handler
END;

1. Unnamed basic format:

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

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

Mysql> DELIMITER $$

-Out Error VARCHAR (5))
DECLARE non_unique CONDITION for SQLSTATE ' 23000 ';

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 HANDLE R for SQLSTATE ' 23000 '  , SET @processed = 100;
DECLARE CONTINUE HANDLER for SQLSTATE ' 21000 '  SET @processed =        200;  INSERT into TEAMS VALUES (2,27, ' third ');        set @test = 123321;    -END; -end$ $mysql > DELIMITER;mysql> call small_mistake6;mysql> select @processed, @test; +------------+------- -+| @processed | @test |+------------+--------+| 300 | 123321 |+------------+--------+

"Exception handling" for MySQL stored procedures