Rechercher dans le manuel MySQL

13.6.7.5 SIGNAL Syntax

  1. SIGNAL condition_value
  2.     [SET signal_information_item
  3.     [, signal_information_item] ...]
  4.  
  5. condition_value: {
  6.     SQLSTATE [VALUE] sqlstate_value
  7.   | condition_name
  8. }
  9.  
  10. signal_information_item:
  11.     condition_information_item_name = simple_value_specification
  12.  
  13. condition_information_item_name: {
  14.     CLASS_ORIGIN
  15.   | SUBCLASS_ORIGIN
  16.   | MESSAGE_TEXT
  17.   | MYSQL_ERRNO
  18.   | CONSTRAINT_CATALOG
  19.   | CONSTRAINT_SCHEMA
  20.   | CONSTRAINT_NAME
  21.   | CATALOG_NAME
  22.   | SCHEMA_NAME
  23.   | TABLE_NAME
  24.   | COLUMN_NAME
  25.   | CURSOR_NAME
  26. }
  27.  
  28. condition_name, simple_value_specification:
  29.     (see following discussion)

SIGNAL is the way to return an error. SIGNAL provides error information to a handler, to an outer portion of the application, or to the client. Also, it provides control over the error's characteristics (error number, SQLSTATE value, message). Without SIGNAL, it is necessary to resort to workarounds such as deliberately referring to a nonexistent table to cause a routine to return an error.

No privileges are required to execute the SIGNAL statement.

To retrieve information from the diagnostics area, use the GET DIAGNOSTICS statement (see Section 13.6.7.3, “GET DIAGNOSTICS Syntax”). For information about the diagnostics area, see Section 13.6.7.7, “The MySQL Diagnostics Area”.

SIGNAL Overview

The condition_value in a SIGNAL statement indicates the error value to be returned. It can be an SQLSTATE value (a 5-character string literal) or a condition_name that refers to a named condition previously defined with DECLARE ... CONDITION (see Section 13.6.7.1, “DECLARE ... CONDITION Syntax”).

An SQLSTATE value can indicate errors, warnings, or not found. The first two characters of the value indicate its error class, as discussed in Signal Condition Information Items. Some signal values cause statement termination; see Effect of Signals on Handlers, Cursors, and Statements.

The SQLSTATE value for a SIGNAL statement should not start with '00' because such values indicate success and are not valid for signaling an error. This is true whether the SQLSTATE value is specified directly in the SIGNAL statement or in a named condition referred to in the statement. If the value is invalid, a Bad SQLSTATE error occurs.

To signal a generic SQLSTATE value, use '45000', which means unhandled user-defined exception.

The SIGNAL statement optionally includes a SET clause that contains multiple signal items, in a list of condition_information_item_name = simple_value_specification assignments, separated by commas.

Each condition_information_item_name may be specified only once in the SET clause. Otherwise, a Duplicate condition information item error occurs.

Valid simple_value_specification designators can be specified using stored procedure or function parameters, stored program local variables declared with DECLARE, user-defined variables, system variables, or literals. A character literal may include a _charset introducer.

For information about permissible condition_information_item_name values, see Signal Condition Information Items.

The following procedure signals an error or warning depending on the value of pval, its input parameter:

  1.   DECLARE specialty CONDITION FOR SQLSTATE '45000';
  2.   IF pval = 0 THEN
  3.     SIGNAL SQLSTATE '01000';
  4.   ELSEIF pval = 1 THEN
  5.     SIGNAL SQLSTATE '45000'
  6.       SET MESSAGE_TEXT = 'An error occurred';
  7.   ELSEIF pval = 2 THEN
  8.     SIGNAL specialty
  9.       SET MESSAGE_TEXT = 'An error occurred';
  10.   ELSE
  11.     SIGNAL SQLSTATE '01000'
  12.       SET MESSAGE_TEXT = 'A warning occurred', MYSQL_ERRNO = 1000;
  13.     SIGNAL SQLSTATE '45000'
  14.       SET MESSAGE_TEXT = 'An error occurred', MYSQL_ERRNO = 1001;
  15.   END IF;

If pval is 0, p() signals a warning because SQLSTATE values that begin with '01' are signals in the warning class. The warning does not terminate the procedure, and can be seen with SHOW WARNINGS after the procedure returns.

If pval is 1, p() signals an error and sets the MESSAGE_TEXT condition information item. The error terminates the procedure, and the text is returned with the error information.

If pval is 2, the same error is signaled, although the SQLSTATE value is specified using a named condition in this case.

If pval is anything else, p() first signals a warning and sets the message text and error number condition information items. This warning does not terminate the procedure, so execution continues and p() then signals an error. The error does terminate the procedure. The message text and error number set by the warning are replaced by the values set by the error, which are returned with the error information.

SIGNAL is typically used within stored programs, but it is a MySQL extension that it is permitted outside handler context. For example, if you invoke the mysql client program, you can enter any of these statements at the prompt:

  1. SIGNAL SQLSTATE '77777';
  2.  
  3. CREATE TRIGGER t_bi BEFORE INSERT ON t
  4.   FOR EACH ROW SIGNAL SQLSTATE '77777';
  5.  
  6. CREATE EVENT e ON SCHEDULE EVERY 1 SECOND
  7.   DO SIGNAL SQLSTATE '77777';

SIGNAL executes according to the following rules:

If the SIGNAL statement indicates a particular SQLSTATE value, that value is used to signal the condition specified. Example:

  1. CREATE PROCEDURE p (divisor INT)
  2.   IF divisor = 0 THEN
  3.     SIGNAL SQLSTATE '22012';
  4.   END IF;

If the SIGNAL statement uses a named condition, the condition must be declared in some scope that applies to the SIGNAL statement, and must be defined using an SQLSTATE value, not a MySQL error number. Example:

  1. CREATE PROCEDURE p (divisor INT)
  2.   DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
  3.   IF divisor = 0 THEN
  4.     SIGNAL divide_by_zero;
  5.   END IF;

If the named condition does not exist in the scope of the SIGNAL statement, an Undefined CONDITION error occurs.

If SIGNAL refers to a named condition that is defined with a MySQL error number rather than an SQLSTATE value, a SIGNAL/RESIGNAL can only use a CONDITION defined with SQLSTATE error occurs. The following statements cause that error because the named condition is associated with a MySQL error number:

  1. DECLARE no_such_table CONDITION FOR 1051;
  2. SIGNAL no_such_table;

If a condition with a given name is declared multiple times in different scopes, the declaration with the most local scope applies. Consider the following procedure:

  1. CREATE PROCEDURE p (divisor INT)
  2.   DECLARE my_error CONDITION FOR SQLSTATE '45000';
  3.   IF divisor = 0 THEN
  4.     BEGIN
  5.       DECLARE my_error CONDITION FOR SQLSTATE '22012';
  6.       SIGNAL my_error;
  7.     END;
  8.   END IF;
  9.   SIGNAL my_error;

If divisor is 0, the first SIGNAL statement executes. The innermost my_error condition declaration applies, raising SQLSTATE '22012'.

If divisor is not 0, the second SIGNAL statement executes. The outermost my_error condition declaration applies, raising SQLSTATE '45000'.

For information about how the server chooses handlers when a condition occurs, see Section 13.6.7.6, “Scope Rules for Handlers”.

Signals can be raised within exception handlers:

  1.   DECLARE EXIT HANDLER FOR SQLEXCEPTION
  2.     SIGNAL SQLSTATE VALUE '99999'
  3.       SET MESSAGE_TEXT = 'An error occurred';
  4.   END;
  5.   DROP TABLE no_such_table;

CALL p() reaches the DROP TABLE statement. There is no table named no_such_table, so the error handler is activated. The error handler destroys the original error (no such table) and makes a new error with SQLSTATE '99999' and message An error occurred.

Contents Haut

Signal Condition Information Items

The following table lists the names of diagnostics area condition information items that can be set in a SIGNAL (or RESIGNAL) statement. All items are standard SQL except MYSQL_ERRNO, which is a MySQL extension. For more information about these items see Section 13.6.7.7, “The MySQL Diagnostics Area”.

Item Name             Definition
---------             ----------
CLASS_ORIGIN          VARCHAR(64)
SUBCLASS_ORIGIN       VARCHAR(64)
CONSTRAINT_CATALOG    VARCHAR(64)
CONSTRAINT_SCHEMA     VARCHAR(64)
CONSTRAINT_NAME       VARCHAR(64)
CATALOG_NAME          VARCHAR(64)
SCHEMA_NAME           VARCHAR(64)
TABLE_NAME            VARCHAR(64)
COLUMN_NAME           VARCHAR(64)
CURSOR_NAME           VARCHAR(64)
MESSAGE_TEXT          VARCHAR(128)
MYSQL_ERRNO           SMALLINT UNSIGNED

The character set for character items is UTF-8.

It is illegal to assign NULL to a condition information item in a SIGNAL statement.

A SIGNAL statement always specifies an SQLSTATE value, either directly, or indirectly by referring to a named condition defined with an SQLSTATE value. The first two characters of an SQLSTATE value are its class, and the class determines the default value for the condition information items:

  • Class = '00' (success)

    Illegal. SQLSTATE values that begin with '00' indicate success and are not valid for SIGNAL.

  • Class = '01' (warning)

    1. MESSAGE_TEXT = 'Unhandled user-defined warning condition';
    2. MYSQL_ERRNO = ER_SIGNAL_WARN
  • Class = '02' (not found)

    1. MESSAGE_TEXT = 'Unhandled user-defined not found condition';
    2. MYSQL_ERRNO = ER_SIGNAL_NOT_FOUND
  • Class > '02' (exception)

    1. MESSAGE_TEXT = 'Unhandled user-defined exception condition';
    2. MYSQL_ERRNO = ER_SIGNAL_EXCEPTION

For legal classes, the other condition information items are set as follows:

  1. CLASS_ORIGIN = SUBCLASS_ORIGIN = '';
  2. CONSTRAINT_CATALOG = CONSTRAINT_SCHEMA = CONSTRAINT_NAME = '';
  3. CATALOG_NAME = SCHEMA_NAME = TABLE_NAME = COLUMN_NAME = '';
  4. CURSOR_NAME = '';

The error values that are accessible after SIGNAL executes are the SQLSTATE value raised by the SIGNAL statement and the MESSAGE_TEXT and MYSQL_ERRNO items. These values are available from the C API:

At the SQL level, the output from SHOW WARNINGS and SHOW ERRORS indicates the MYSQL_ERRNO and MESSAGE_TEXT values in the Code and Message columns.

To retrieve information from the diagnostics area, use the GET DIAGNOSTICS statement (see Section 13.6.7.3, “GET DIAGNOSTICS Syntax”). For information about the diagnostics area, see Section 13.6.7.7, “The MySQL Diagnostics Area”.

Contents Haut

Effect of Signals on Handlers, Cursors, and Statements

Signals have different effects on statement execution depending on the signal class. The class determines how severe an error is. MySQL ignores the value of the sql_mode system variable; in particular, strict SQL mode does not matter. MySQL also ignores IGNORE: The intent of SIGNAL is to raise a user-generated error explicitly, so a signal is never ignored.

In the following descriptions, unhandled means that no handler for the signaled SQLSTATE value has been defined with DECLARE ... HANDLER.

  • Class = '00' (success)

    Illegal. SQLSTATE values that begin with '00' indicate success and are not valid for SIGNAL.

  • Class = '01' (warning)

    The value of the warning_count system variable goes up. SHOW WARNINGS shows the signal. SQLWARNING handlers catch the signal.

    Warnings cannot be returned from stored functions because the RETURN statement that causes the function to return clears the diagnostic area. The statement thus clears any warnings that may have been present there (and resets warning_count to 0).

  • Class = '02' (not found)

    NOT FOUND handlers catch the signal. There is no effect on cursors. If the signal is unhandled in a stored function, statements end.

  • Class > '02' (exception)

    SQLEXCEPTION handlers catch the signal. If the signal is unhandled in a stored function, statements end.

  • Class = '40'

    Treated as an ordinary exception.


Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-signal.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

  1. View the html document Language of the document:en Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut