Shulou(Shulou.com)06/01 Report--

Let's learn how to deal with exceptions in MySQL stored procedures. I'm sure you will benefit a lot after reading it. There are not many words in the text. I hope you want this short article on how to deal with exceptions in MySQL stored procedures.

Experimental environment:

Mysql > use TENNIS

Reading table information for completion of table and column names

You 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)

Because most of the previous experiments use this database, the structure of the database table will not be repeated.

Example: create the process and insert a duplicate No. 2 team

Mysql > delimiter $$

Mysql > create procedure duplicate_teams (- > out p_processed smallint)-> begin-> set packs processed1;-> insert into TEAMS values (2 people 27);-> set packs processeds2;-> end $$

Mysql > delimiter

Mysql > call duplicate_teams (@ processed); ERROR 1062 (23000): Duplicate entry'2' for key 'PRIMARY'mysql > select @ processed;+-+

| | @ processed |

+-+

| | NULL |

+-+

Parsing: the client calls the stored procedure, runs an error, prints an error message, and the process is terminated with no output.

1. Define exception handling:

DECLARE... HANDLER statement:

Through the definition and treatment of conditions, we can make corresponding steps to deal with the problems that may be encountered in the process of definition. (that is, define an exception handler that specifies what action to take when a statement in the procedure goes wrong)

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

Note: declare... The handler statement must appear after the variable or condition declaration.

When an error (condition_value) occurs-> executes the specified statement (statement-- logs the error message), and then decides how to do it (handler_action).

1 、 handler_action

Continue: continue with the current program (then execute the next statement of the faulty SQL)

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

The undo feature is not currently supported.

2 、 statement

It can be a single statement or a compound statement.

3. Condition_value indicates the condition under which handler is triggered; if the condition is triggered and no handler is declared to handle the condition, the progress of the program will depend on the type of condition.

[top]

Second, a single exception handler

The SQLSTATE code of the error message is included in the output parameter.

Example 1: the creation process, inserting a duplicate No. 2 team; continue

Mysql > DELIMITER $$

Mysql > CREATE PROCEDURE small_mistake1 (

-> OUT error VARCHAR (5))

-> BEGIN-> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'

-> SET error = '23000 errors; # is used to record some information when an error occurs->

-> select error;-> SET error = '0000000';-> select error;-> INSERT INTO TEAMS VALUES (2Lie27 last third'); # sentences that can make mistakes-> SET error = '23001'

-> END$$

Mysql > DELIMITER

Mysql > call small_mistake1 (@ a); +-+

| | error |

+-+

| | NULL |

+-+

+-+

| | error |

+-+

| | 00000 |

+-+ mysql > select @ a

| | @ a |

+-+

| | 23001 |

+-+

Lab example parsing:

In the begin end block, define declare... Handler statement is used to catch errors (standby ing), select, set, select execution sequentially, insert statement error, SQLSTATE code 23000, capture, exception handling (assignment record), and then continue to execute the next statement of the error insert statement.

Example 2: the creation process, inserting a duplicate No. 2 team; exit

Mysql > DELIMITER $$

Mysql > CREATE PROCEDURE small_mistake2 (

-> OUT error VARCHAR (5))

-> BEGIN-> DECLARE EXIT HANDLER FOR SQLSTATE '23000'

-> SET error = '23000 hours;->

-> select error;-> SET error = '00000';-> select error;-> INSERT INTO TEAMS VALUES (2Lie27);-> SET error = '23001'

-> END$$

Mysql > DELIMITER

Mysql > call small_mistake2 (@ a); +-+

| | error |

+-+

| | NULL |

+-+

+-+

| | error |

+-+

| | 00000 |

+-+ mysql > select @ a

| | @ a |

+-+

| | 23000 |

+-+

The only difference from example 1 is that handler_action chooses exit, indicating that the statement after the error statement will not be executed after the exception handling, and exits the begin end statement block directly.

[top]

3. Multiple exception handlers

Multiple exception handlers can be defined in a process and handled differently for different errors.

Example 1:

Mysql > INSERT INTO TEAMS VALUES (2, 27, 13, 13, 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, 13, 7, 5); # error statement

-> END$$

Mysql > DELIMITER

Mysql > call small_mistake3 (@ error)

Mysql > select @ error;+-+

| | @ error |

+-+

| | 21S01 |

+-+

Parsing:

If the SQLSTATE code is 23000, the exception handling executes SET error = '23000'

If the SQLSTATE code is 21S01, exception handling executes SET error = '21S01'

Of course, exception handling, which can be custom, is generally recorded in the error message 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 (adopted by ANSI SQL).

The corresponding handler for SQLSTATE code:

1. SQLWARNING handler: all sqlstate codes starting with '01' correspond to them.

2. NOT FOUND handler: all sqlstate codes starting with '02' correspond to them.

3. SQLEXCEPTION handlers: all sqlstate codes that do not start with '01' or' 02', that is, all SQLSTATE that are not captured by SQLWARNING or NOT FOUND (frequently encountered MySQL errors that do not start with '01' or' 02')

Note: there is a difference between the beginning of '01' and' 02' and the beginning of'1' and'2'. It is a different error sqlate code.

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

E.g:DECLARE CONTINUE HANDLER FOR SQLWARNING,NOT FOUND,SQLEXCEPTION

Mysql > DELIMITER $$

Mysql > CREATE PROCEDURE small_mistake4 (

-> OUT error VARCHAR (5))

-> BEGIN-> DECLARE CONTINUE HANDLER FOR SQLWARNING,NOT FOUND,SQLEXCEPTION

-> SET error = 'xxxxx'

->

-> INSERT INTO teams VALUES (2, 27, 2, 7, 7, 3, 3, 3, 3)

-> END$$

Mysql > DELIMITER

Mysql > call small_mistake4 (@ a)

Mysql > select @ a

| | @ a |

+-+

| | xxxxx |

+-+

If you want to ignore a condition when defining a handler

E.g:DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END

In other words, when you encounter a problem with SQLWARNING, the exception handling is the begin end block, because there is nothing in it, which is similar to ignoring it directly.

[top]

IV. Naming of exception handling

To improve readability, you can give a name to a sqlstate code or mysql error code and use that name in later exception handlers.

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

2. The basic format of naming:

BEGIN

DECLARE no_such_table CONDITION FOR 1051

DECLARE CONTINUE HANDLER FOR no_such_table-body of handler

END

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 = '23000mm;-> select error;-> end;->

-> INSERT INTO TEAMS VALUES (2, 27, 7, 3, 3); # error-prone statements

-> END$$

Mysql > DELIMITER

Mysql > call small_mistake5 (@ error); +-+

| | error |

+-+

| | 23000 |

+-+

[top]

5. Abnormal propagation

In the case of a nested block, an exception occurs in the internal block, which is first handled by the exception handler of this block, and if the block is not handled, it is handled by the exception handler of the external block.

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;-> set @ test=123321;-> END;-> END$$mysql > DELIMITER; mysql > call small_mistake6;mysql > select @ processed,@test +-+-+ | @ processed | @ test | +-+-+ | 123321 | +-+-+

After reading this article on how to handle exceptions in MySQL stored procedures, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.