Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use the new SIGNAL exception handling in MySQL 5.5. what is introduced in the article is very detailed and has a certain reference value. Interested friends must read it!

MySQL 5.5 added SIGNAL exception handling

MySQL has supported stored procedures since 5. 0, and it has been several years since the latest version 5. 5. Although the exception handling of MYSQL is not perfect, it is sufficient to handle most applications.

There are several statements for exception handling:

1. DECLARE... CONDITION.

2. DECLARE... HANDLER.

3. RESIGNAL.

4. SIGNAL.

The first is called conditional declaration.

DECLARE condition_name CONDITION FOR condition_value

Here condition_name is named for the standard variable, and condition_value is the SQLSTATE value or the ERROR CODE of MySQL itself.

Individual condition statements cannot be run directly. Can only be part of the second kind.

Www.2cto.com

The second is called conditional processing.

The first kind of declaration is good, and then naturally we have to deal with it accordingly.

DECLARE handler_action HANDLER

FOR condition_value [, condition_value]...

Statement

Handler_action:

CONTINUE

| | EXIT |

| | UNDO |

Condition_value:

Mysql_error_code

| | SQLSTATE [VALUE] sqlstate_value |

| | condition_name |

| | SQLWARNING |

| | NOT FOUND |

| | SQLEXCEPTION |

Handler_action represents the actions to be processed, and there are currently two valid actions, continue and directly exit.

There are several kinds of condition_value, including SQLSTATE, MYSQL EEROR CODE, condition_name in the previous conditional statement, and the other two types of scope confusion

SQLWARNING,SQLEXCEPTION. NOT FOUND represents any WARNING or ERROR that does not exist.

Third, it represents the error message and code of the camouflage system and refreshes the current warning buffer area.

RESIGNAL [condition_value]

[SET signal_information_item

[, signal_information_item]...]

Condition_value:

SQLSTATE [VALUE] sqlstate_value

| | condition_name |

Signal_information_item:

Condition_information_item_name = simple_value_specification

Condition_information_item_name:

CLASS_ORIGIN

| | SUBCLASS_ORIGIN |

| | MESSAGE_TEXT |

| | MYSQL_ERRNO |

| | CONSTRAINT_CATALOG |

| | CONSTRAINT_SCHEMA |

| | CONSTRAINT_NAME |

| | CATALOG_NAME |

| | SCHEMA_NAME |

| | TABLE_NAME |

| | COLUMN_NAME |

| | CURSOR_NAME |

In particular, there are many specific things that can be camouflaged, including error codes (things of MYSQL itself), error contents, wrong names, table names, column names, and so on.

Fourth, there is little difference between SIGNAL and RESIGNAL. Let's keep in mind that signal cannot be run alone, and resignal can be run alone.

SIGNAL condition_value

[SET signal_information_item

[, signal_information_item]...]

Condition_value:

SQLSTATE [VALUE] sqlstate_value

| | condition_name |

Signal_information_item:

Condition_information_item_name = simple_value_specification

Condition_information_item_name:

CLASS_ORIGIN

| | SUBCLASS_ORIGIN |

| | MESSAGE_TEXT |

| | MYSQL_ERRNO |

| | CONSTRAINT_CATALOG |

| | CONSTRAINT_SCHEMA |

| | CONSTRAINT_NAME |

| | CATALOG_NAME |

| | SCHEMA_NAME |

| | TABLE_NAME |

| | COLUMN_NAME |

| | CURSOR_NAME |

Let's give you a few examples.

No RESIGNAL/SIGNAL.

[sql]

DELIMITER $$

USE `t _ room` $$

DROP PROCEDURE IF EXISTS `sp_ 1` $$

CREATE DEFINER= `root` @ `localhost` PROCEDURE `root` sp_ room1` (

IN f_id INT

)

BEGIN

DECLARE con1 TINYINT DEFAULT 0

DECLARE con1 CONDITION FOR SQLSTATE '23000'

DECLARE CONTINUE HANDLER FOR con1

BEGIN

SELECT 'Error Code: 22\ r\ nDuplicatedbirds'

END

INSERT INTO user_info VALUES (f_id)

END$$

DELIMITER

CALL sp_signal1 (12)

Query result

Result

Error Code: 22

Duplicated!

Rebrush the current error diagnosis area with RESIGNAL/SIGNAL.

[sql]

DELIMITER $$

Www.2cto.com

USE `t _ room` $$

DROP PROCEDURE IF EXISTS `sp_ 1` $$

CREATE DEFINER= `root` @ `localhost` PROCEDURE `root` sp_ room1` (

IN f_id INT

)

BEGIN

DECLARE con1 TINYINT DEFAULT 0

DECLARE con1 CONDITION FOR SQLSTATE '23000'

DECLARE CONTINUE HANDLER FOR con1

BEGIN

Resignal SET schema_name = 'action'

Table_name = 'action_tb'

Message_text = 'Duplicatedbirds'

_ errno = 22

/ *

Signal con1 SET schema_name = 'action'

Table_name = 'action_tb'

Message_text = 'Duplicatedbirds'

Mysql_errno = 22

Www.2cto.com

, /

END

INSERT INTO user_info VALUES (f_id)

END$$

DELIMITER

CALL sp_signal1 (12)

Www.2cto.com

Query: call sp_signal1 (12)

Error Code: 22

Duplicated!

Execution Time: 00:00:00:000

Transfer Time: 00:00:00:000

Total Time: 00:00:00:000

These are all the contents of the article "how to use SIGNAL exception handling in MySQL 5.5.5.Thank you for reading!" Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.