MySQL的SQL语句 -条件处理语句（5） - SIGNAL 语句

数据库杂货铺 2021-04-12

2936

SIGNAL 语句

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
}


condition_name, simple_value_specification:
    (see following discussion)

SIGNAL 是“返回”错误的方法。SIGNAL 向句柄、应用程序的外部或客户端提供错误信息。此外，它还提供对错误特征（错误号、SQLSTATE 值、消息）的控制。在没有 SIGNAL 的情况下，有必要采取变通办法，例如故意引用不存在的表，以导致例程返回错误。

执行 SIGNAL 语句不需要任何权限。

要从诊断区域检索信息，请使用 GET DIAGNOSTICS 语句。

SIGNAL 概述

SIGNAL 语句中的 condition_value 表示要返回的错误值。它可以是一个 SQLSTATE 值（5个字符的字符串文字）或一个 condition_name，该条件名称引用了以前用 DECLARE ... CONDITION 定义的命名条件。

SQLSTATE 值可以表示错误、警告或 “未找到”。值的前两个字符表示其错误类别，如信号条件信息项中所述。某些信号值会导致语句终止。

SIGNAL 语句的 SQLSTATE 值不应以 '00' 开头，因为这样的值表示成功，对于错误信号无效。不管 SQLSTATE 值是在 SIGNAL 语句中直接指定的，还是在语句中引用的命名条件中指定的，都是这样。如果该值无效，则会发生 Bad SQLSTATE 错误。

要发送通用 SQLSTATE 值信号，请使用 '45000'，这意味着“未处理的用户定义异常”。

SIGNAL 语句可以选择使用包含多个信号项的 SET 子句，信号项在 condition_information_item_name = simple_value_specification 赋值的列表中，并用逗号分隔。

在 SET 子句中，每个 condition_information_item_name 只能指定一次。否则，将发生重复条件信息项错误。

可以使用存储过程或函数参数、用 DECLARE 声明的存储程序局部变量、用户定义变量、系统变量或字面量来指定有效的 simple_value_specification 指示符。字符字面量可以包括 _charset 导引。

以下过程根据其输入参数 pval 的值发出错误或警告信号：

CREATE PROCEDURE p (pval INT)
BEGIN
  DECLARE specialty CONDITION FOR SQLSTATE '45000';
  IF pval = 0 THEN
    SIGNAL SQLSTATE '01000';
  ELSEIF pval = 1 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'An error occurred';
  ELSEIF pval = 2 THEN
    SIGNAL specialty
      SET MESSAGE_TEXT = 'An error occurred';
  ELSE
    SIGNAL SQLSTATE '01000'
      SET MESSAGE_TEXT = 'A warning occurred', MYSQL_ERRNO = 1000;
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'An error occurred', MYSQL_ERRNO = 1001;
  END IF;
END;

如果 pval 为 0，则 p() 会发出警告信号，因为以 '01' 开头的 SQLSTATE 值是警告类中的信号。警告不会终止过程，并且在过程返回后可以通过 SHOW WARNINGS 看到。

如果 pval 为1，则 p() 发出错误信号并设置 MESSAGE_TEXT 条件信息项。这个错误终止这个过程，返回的文本带有错误信息。

如果 pval 为2，则发出相同的错误信号，尽管在本例中使用命名条件指定了 SQLSTATE 值。

如果 pval 是其他值，则 p() 首先发出警告信号，并设置消息文本和错误号条件信息项。此警告不会终止过程，因此继续执行，然后 p() 发出错误信号。错误终止过程。警告设置的消息文本和错误号将替换为错误设置的值，这些值随错误信息一起返回。

SIGNAL 通常在存储程序中使用，但它是允许在句柄上下文之外使用的 MySQL 扩展。例如，如果调用 mysql 客户端程序，可以在提示符处输入以下语句：

SIGNAL SQLSTATE '77777';


CREATE TRIGGER t_bi BEFORE INSERT ON t
  FOR EACH ROW SIGNAL SQLSTATE '77777';


CREATE EVENT e ON SCHEDULE EVERY 1 SECOND
  DO SIGNAL SQLSTATE '77777';

SIGNAL 按以下规则执行：

如果 SIGNAL 语句指示一个特定的 SQLSTATE 值，那么该值将用于表示指定的条件。例如：

CREATE PROCEDURE p (divisor INT)
BEGIN
  IF divisor = 0 THEN
    SIGNAL SQLSTATE '22012';
  END IF;
END;

如果 SIGNAL 语句使用命名条件，则必须在应用于 SIGNAL 语句的某个范围内声明该条件，并且必须使用 SQLSTATE 值而不是 MySQL 错误号来定义该条件。例子：

CREATE PROCEDURE p (divisor INT)
BEGIN
  DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
  IF divisor = 0 THEN
    SIGNAL divide_by_zero;
  END IF;
END;

如果命名的条件不在 SIGNAL 语句的范围内，则会发生 Undefined CONDITION 错误。

如果 SIGNAL 引用的命名条件是用 MySQL 错误号而不是 SQLSTATE 值定义的，则会引发 SIGNAL/RESIGNAL can only use a CONDITION defined with SQLSTATE 错误。以下语句导致该错误，因为命名条件与 MySQL 错误号关联：

DECLARE no_such_table CONDITION FOR 1051;
SIGNAL no_such_table;

如果在不同的作用域中多次声明具有给定名称的条件，则应用具有最局部作用域的声明。参考以下程序：

CREATE PROCEDURE p (divisor INT)
BEGIN
  DECLARE my_error CONDITION FOR SQLSTATE '45000';
  IF divisor = 0 THEN
    BEGIN
      DECLARE my_error CONDITION FOR SQLSTATE '22012';
      SIGNAL my_error;
    END;
  END IF;
  SIGNAL my_error;
END;

如果 divisor 为0，则执行第一个 SIGNAL 语句。将应用最内部的 my_error 条件声明，并引发 SQLSTATE '22012'。

如果 divisor 不是0，则执行第二个 SIGNAL 语句。将应用最外层的 my_error 条件声明，并引发 SQLSTATE '45000'。

可以在异常句柄中引发异常信号：

CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SIGNAL SQLSTATE VALUE '99999'
      SET MESSAGE_TEXT = 'An error occurred';
  END;
  DROP TABLE no_such_table;
END;

CALL p() 会执行 DROP TABLE 语句。因为没有名为 no_such_table 的表，因此错误句柄被激活。错误句柄将销毁原始错误（“no such table”），并生成新错误，SQLSTATE 为 '99999'，错误消息为 An error occurred。

信号条件信息项

下表列出了可以在 SIGNAL（或 RESIGNAL）语句中设置的诊断区域条件信息项的名称。所有项目都是基于标准的 SQL，除了 MYSQL_ERRNO，它是一个 MYSQL 扩展。

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

字符项的字符集是 UTF-8。

在 SIGNAL 语句中为条件信息项赋值 NULL 是非法的。

SIGNAL 语句总是指定 SQLSTATE 值，不管是直接指定还是通过用 SQLSTATE 值定义的命名条件间接引用。SQLSTATE 值的前两个字符代表它的类，该类确定条件信息项的默认值：

● 类别 = '00' (成功)

非法的。以 '00' 开头的 SQLSTATE 值表示成功，对 SIGNAL 无效。

● 类别 = '01' (警告)

MESSAGE_TEXT = 'Unhandled user-defined warning condition';
MYSQL_ERRNO = ER_SIGNAL_WARN

● 类别 = '02' (未找到)

MESSAGE_TEXT = 'Unhandled user-defined not found condition';
MYSQL_ERRNO = ER_SIGNAL_NOT_FOUND

● 类别 > '02' (异常)

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

对于合法类别，其他条件信息项设置如下：

CLASS_ORIGIN = SUBCLASS_ORIGIN = '';
CONSTRAINT_CATALOG = CONSTRAINT_SCHEMA = CONSTRAINT_NAME = '';
CATALOG_NAME = SCHEMA_NAME = TABLE_NAME = COLUMN_NAME = '';
CURSOR_NAME = '';

SIGNAL 执行后可以访问的是 SIGNAL 语句引发的错误值和 MESSAGE_TEXT 和 MYSQL_ERRNO 项目值。这些值可从 C API 获得：

● mysql_sqlstate() 返回 SQLSTATE 值。

● mysql_errno() 返回 MYSQL_ERRNO 值。

● mysql_error() 返回 MESSAGE_TEXT 值。

在SQL层面，SHOW WARNINGS 和 SHOW ERRORS 的输出列 Code 和 Message 中指明了 MYSQL_ERRNO 和 MESSAGE_TEXT 值。

要从诊断区域检索信息，请使用 GET DIAGNOSTICS 语句。

信号对句柄、游标和语句的影响

信号对语句执行的影响因信号类而异。类确定错误的严重程度。MySQL 忽略 sql_mode 系统变量的值；不管是否是严格的 SQL 模式。MySQL 还忽略了 IGNORE：SIGNAL 的目的是显式地引发用户定义的错误，因此信号永远不会被忽略。

在下面的描述中，“unhandled” 意味着没有用 DECLARE ... HANDLER 定义有相应 SQLSTATE 值的句柄。

● 类别 = '00' (成功)

非法，以 '00' 开头的 SQLSTATE 值表示成功，对 SIGNAL 无效。

● 类别 = '01' (警告)

warning_count 系统变量的值增加。SHOW WARNINGS 显示信号。SQLWARNING 句柄捕捉信号。

无法从存储函数返回警告，因为导致函数返回的 RETURN 语句清除了诊断区域。因此，该语句将清除可能存在的所有警告（并将 warning_count 重置为0）。

● 类别 = '02' (未找到)

NOT FOUND 句柄捕捉信号。对游标没有影响。如果信号在存储函数中未处理，则语句结束。

● 类别 > '02' (异常)

SQLEXCEPTION 句柄捕捉信号。如果信号在存储函数中未处理，则语句结束。

● 类别 = '40'

作为普通的异常处理。

官方网址：

https://dev.mysql.com/doc/refman/8.0/en/signal.html

mysql

文章转载自数据库杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的SQL语句 -条件处理语句（5） - SIGNAL 语句

评论