Added SIGNAL exception handling in MySQL5.5 _ MySQL

Source: Internet
Author: User
MySQL5.5 added SIGNAL exception handling bitsCN.com
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. The following statements are used 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. The second type is conditional processing. 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 $ 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 '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; */END; insert into user_info VALUES (f_id); END $ DELIMITER; CALL sp_signal1 (12); 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.