Added SIGNAL Exception Handling in MySQL 5.5

MySQL 5.5 has added SIGNAL Exception Handling. MySQL has been supporting stored procedures since MySQL 5.0 and has been in the latest version 5.5 for several years. Although the exception handling in MYSQL is not perfect, it is sufficient to handle most applications. Www.2cto.com has the following statements for exception handling: 1. DECLARE... CONDITION.2. DECLARE... HANDLER.3. RESIGNAL.4. SIGNAL. The first one is called condition declaration. DECLARE condition_name condition for condition_value here, condition_name is the standard variable name, And condition_value is the SQLSTATE value or MySQL's own error code. Separate condition statements cannot be run directly. It can only be part of the second type. Www.2cto.com. The first statement is ready, and the next step is to handle it. DECLARE handler_action handler for condition_value [, condition_value]... statement handler_action, continue and exit directly. There are several condition_value types, including SQLSTATE, mysql eeror code, condition_name, and other SQLWARNING types in the preceding condition declaration. SQLEXCEPTION. not found indicates any nonexistent WARNING or ERROR. Third, it indicates the error information and code of the disguised system, and refresh the current warning buffer area. RESIGNAL [condition_value] [SET signal_information_item [, signal_information_item]...] condition_value: SQLSTATE [VALUE] sqlstate_value | condition_name condition: condition = condition: CLASS_ORIGIN | SUBCLASS_ORIGIN | MESSAGE_TEXT | MYSQL_ERRNO | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CATALO G_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAME special note that many specific items can be disguised, including the error code (MYSQL's own stuff), error content, wrong Database Name, table Name, column name, and so on. Fourth, there is little difference between SIGNAL and RESIGNAL. We remember that signal cannot run independently, and resignal can run independently. SIGNAL condition_value [SET signal_information_item [, signal_information_item]...] condition_value: SQLSTATE [VALUE] sqlstate_value | condition_name condition: condition = condition: CLASS_ORIGIN | SUBCLASS_ORIGIN | MESSAGE_TEXT | MYSQL_ERRNO | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CATALOG_NA ME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAME Here are several examples. Do not use RESIGNAL/SIGNAL. [SQL] DELIMITER $ USE't _ girl '$ DROP PROCEDURE IF EXISTS 'SP _ signal1' $ CREATE DEFINER = 'root' @ 'localhost' PROCEDURE 'SP _ signal1 '(IN f_id INT) begin declare con1 tinyint default 0; DECLARE con1 condition for sqlstate '200'; declare continue handler for con1 begin select 'error Code: 22 \ r \ nDuplicated! '; END; insert into user_info VALUES (f_id); END $ DELIMITER; CALL sp_signal1 (12); query result resultError Code: 22 Duplicated! Refresh the current error diagnosis area with RESIGNAL/SIGNAL. [SQL] DELIMITER $ www.2cto.com USE 't_ gir' $ DROP PROCEDURE IF EXISTS 'SP _ signal1' $ CREATE DEFINER = 'root' @ 'localhost' PROCEDURE 'SP _ signal1' (IN f_id INT) begin declare con1 tinyint default 0; DECLARE con1 condition for sqlstate '000000'; declare continue handler for con1 BEGIN resignal SET schema_name = 'action', table_name = 'Action _ tb ', message_text = 'duplicated! ', Mysql_errno = 22;/* signal con1 SET schema_name = 'action', table_name = 'Action _ tb', message_text = 'duplicated! ', 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