MySQL - RESIGNAL 语句

更新于 2025/6/21 6:22:17

MySQL RESIGNAL 语句
使用 RESIGNAL 处理警告
使用客户端程序的 RESIGNAL 语句

在 MySQL 中使用存储过程时，管理执行过程中可能出现的异常非常重要。否则，这些异常可能会导致存储过程突然终止。

为了解决这个问题，MySQL 提供了一种通过错误处理程序来处理异常的方法。这些处理程序可以使用 DECLARE ... HANDLER 语句声明。

MySQL RESIGNAL 语句

MySQL RESIGNAL 语句用于在存储过程中发生异常时向处理程序、应用程序或客户端提供错误信息。

RESIGNAL 专门用于错误处理程序，并且必须始终包含属性。这些属性指定与引发的错误关联的 SQL 状态、错误代码和错误消息。

自定义错误消息

RESIGNAL 语句允许您使用 SET MESSAGE_TEXT 命令自定义错误消息，以确保过程更顺畅地执行。

语法

以下是 MySQL RESIGNAL 语句的语法 -

RESIGNAL condition_value [SET signal_information_item]

其中:

condition_value 表示要返回的错误值，可以是"sqlstate_value"或"condition_name"。
signal_information_item 允许您设置与错误条件相关的其他信息。您可以指定各种信号信息项，例如 CLASS_ORIGIN、SUBCLASS_ORIGIN、MESSAGE_TEXT、MYSQL_ERRNO、CONSTRAINT_CATALOG、CONSTRAINT_SCHEMA、CONSTRAINT_NAME、CATALOG_NAME、SCHEMA_NAME、TABLE_NAME、COLUMN_NAME 或 CURSOR_NAME。

示例

在此示例中，我们创建一个过程，该过程接受学位的缩写形式并返回其完整形式。如果我们提供的学位无效，即除 BBA、BCA、MD 和 ITI 以外的值，则使用 RESIGNAL 语句生成错误消息 -

DELIMITER //
CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form VARCHAR(50))
BEGIN
DECLARE wrong_choice CONDITION FOR SQLSTATE '45000';
DECLARE EXIT HANDLER FOR wrong_choice
RESIGNAL SET MESSAGE_TEXT = 'Given degree is not valid', MYSQL_ERRNO = 1001;
IF degree='BBA' THEN SET full_form = 'Bachelor of Business Administration'; 
ELSEIF degree='BCA' THEN SET full_form = 'Bachelor of Computer Applications'; 
ELSEIF degree='MD' THEN SET full_form = 'Doctor of Medicine';
ELSEIF degree='ITI' THEN SET full_form = 'Industrial Training Institute';
ELSE 
SIGNAL wrong_choice;
END IF;
END //
DELIMITER ;

您可以调用上述过程来检索如下所示的结果 -

CALL example('MD', @fullform);

您可以使用以下 SELECT 语句检索变量的值 -

SELECT @fullform;

以下是获得的输出 -

@fullform
Doctor of Medicine

如果向该过程传递无效值，将生成如下错误消息 -

CALL example ('IIT', @fullform);

输出结果如下 -

ERROR 1001 (45000): Given degree is not valid

使用 RESIGNAL 处理警告

让我们看另一个示例，其中我们不向 RESIGNAL 语句传递可选属性 -

DELIMITER //
CREATE PROCEDURE testexample (num INT)
BEGIN
DECLARE testCondition1 CONDITION FOR SQLSTATE '01000';
DECLARE EXIT HANDLER FOR testCondition1 RESIGNAL;
IF num < 0 THEN
SIGNAL testCondition1;
END IF;
END //
DELIMITER ;

您可以通过传递两个值来调用上述过程。但是，任何以"01"开头的 SQLSTATE 值都表示警告，因此查询执行时会显示警告，如下所示 -

CALL testexample(-15);

获得的输出如下 -

Query OK, 0 rows affected, 1 warning (0.00 sec)

使用客户端程序发出信号语句

我们也可以使用客户端程序执行重新发出信号。

语法

PHP NodeJS Java Python

要通过 PHP 程序执行 resignal 语句，我们需要使用 mysqli 函数 query() 执行"存储过程"，如下所示 -

$sql = "CREATE PROCEDURE example_new1(IN degree VARCHAR(20), OUT full_form Varchar(50))
BEGIN
   IF degree='B-Tech' THEN SET full_form = 'Bachelor of Technology'; ELSEIF degree='M-Tech' THEN SET full_form = 'Master of Technology'; ELSEIF degree='BSC' THEN SET full_form = 'Bachelor of Science';
   ELSEIF degree='MSC' THEN SET full_form = 'Master of Science';
   ELSE 
      RESIGNAL SQLSTATE '01000'
   SET MESSAGE_TEXT = 'Choose from the existing values', MYSQL_ERRNO = 12121;
      RESIGNAL SQLSTATE '45000'
   SET MESSAGE_TEXT = 'Given degree is not valid', MYSQL_ERRNO = 1001;
   END IF;
END";
$mysqli->query($sql);

要通过 JavaScript 程序执行 resignal 语句，我们需要使用 mysql2 库的 query() 函数执行"存储过程"，如下所示 -

var createProcedureSql = `
CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form Varchar(50))
BEGIN
    IF degree='B-Tech' THEN SET full_form = 'Bachelor of Technology'; 
    ELSEIF degree='M-Tech' THEN SET full_form = 'Master of Technology'; 
    ELSEIF degree='BSC' THEN SET full_form = 'Bachelor of Science';
    ELSEIF degree='MSC' THEN SET full_form = 'Master of Science';
    ELSE 
        RESIGNAL SQLSTATE '01000' -- Raise a warning
        SET MESSAGE_TEXT = 'Choose from the existing values', MYSQL_ERRNO = 12121;
        RESIGNAL SQLSTATE '45000' -- Raise an error
        SET MESSAGE_TEXT = 'Given degree is not valid', MYSQL_ERRNO = 1001;
    END IF;
END`;
con.query(createProcedureSql);

要通过 Java 程序执行 resignal 语句，我们需要使用 JDBC 函数 execute() 执行"存储过程"，如下所示 -

String sql = "CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form Varchar(50))
BEGIN IF degree='B-Tech' THEN SET full_form = 'Bachelor of Technology'; 
ELSEIF degree='M-Tech' THEN SET full_form = 'Master of Technology'; 
ELSEIF degree='BSC' THEN SET full_form = 'Bachelor of Science'; 
ELSEIF degree='MSC' THEN SET full_form = 'Master of Science'; 
ELSE RESIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'Choose from the existing values', MYSQL_ERRNO = 12121; 
RESIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Given degree is not valid', MYSQL_ERRNO = 1001; 
END IF; 
END";
statement.execute(sql);

要通过 Python 程序执行 resignal 语句，我们需要使用 MySQL Connector/Python 的 execute() 函数执行"存储过程"，如下所示 -

resignal_statement = 'CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form VARCHAR(50))
BEGIN
IF degree='B-Tech' THEN SET full_form = 'Bachelor of Technology';
ELSEIF degree='M-Tech' THEN SET full_form = 'Master of Technology';
ELSEIF degree='BSC' THEN SET full_form = 'Bachelor of Science';
ELSEIF degree='MSC' THEN SET full_form = 'Master of Science';
ELSE 
RESIGNAL SQLSTATE '01000'
SET MESSAGE_TEXT = 'Choose from the existing values', MYSQL_ERRNO = 12121;
RESIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Given degree is not valid', MYSQL_ERRNO = 1001;
END IF;
END;'
cursorObj.execute(resignal_statement)

示例

以下是程序 -

PHP NodeJS Java Python

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.
');
//lets generate error messege using RESIGNAL statement
$sql = "
CREATE PROCEDURE example_new1(IN degree VARCHAR(20), OUT full_form Varchar(50))
   BEGIN
      IF degree='B-Tech' THEN SET full_form = 'Bachelor of Technology'; ELSEIF degree='M-Tech' THEN SET full_form = 'Master of Technology'; ELSEIF degree='BSC' THEN SET full_form = 'Bachelor of Science';
      ELSEIF degree='MSC' THEN SET full_form = 'Master of Science';
      ELSE 
         RESIGNAL SQLSTATE '01000'
      SET MESSAGE_TEXT = 'Choose from the existing values', MYSQL_ERRNO = 12121;
         RESIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Given degree is not valid', MYSQL_ERRNO = 1001;
      END IF;
   END";
if($mysqli->query($sql)){
    printf("Resignal statement created successfully....!
");
}
//lets call the above procedure
$sql = "CALL example_new('BSC', @fullform)";
if($mysqli->query($sql)){
    printf("Procedure called successfully...!
");
}
//lets retirve the value variable using SELECT statement...
$sql = "SELECT @fullform";
if($result = $mysqli->query($sql)){
    printf("Variable value is: 
");
    while($row = mysqli_fetch_array($result)){
        print_r($row);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

输出

获得的输出如下所示 -

Resignal statement created successfully....!
Procedure called successfully...!
Variable value is: 
Array
(
   [0] => Bachelor of Science
   [@fullform] => Bachelor of Science
)

var mysql = require('mysql2');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "Nr5a0204@123"
});

// Connecting to MySQL
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   console.log("--------------------------");
   // Create a new database
   sql = "Create Database TUTORIALS";
   con.query(sql);
   sql = "USE TUTORIALS";
   con.query(sql);
   // Create the example procedure
   var createProcedureSql = `
       CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form Varchar(50))
       BEGIN
           IF degree='B-Tech' THEN SET full_form = 'Bachelor of Technology'; 
           ELSEIF degree='M-Tech' THEN SET full_form = 'Master of Technology'; 
           ELSEIF degree='BSC' THEN SET full_form = 'Bachelor of Science';
           ELSEIF degree='MSC' THEN SET full_form = 'Master of Science';
           ELSE 
               RESIGNAL SQLSTATE '01000' -- Raise a warning
               SET MESSAGE_TEXT = 'Choose from the existing values', MYSQL_ERRNO = 12121;
               RESIGNAL SQLSTATE '45000' -- Raise an error
               SET MESSAGE_TEXT = 'Given degree is not valid', MYSQL_ERRNO = 1001;
           END IF;
       END;
   `;
   con.query(createProcedureSql, function (err) {
       if (err) throw err;
       console.log("Procedure example created!");
       console.log("--------------------------");
     });
   //Passing BSC value to the procedure to get the fullform
   callExampleProcedureSql = "CALL example('BSC', @fullform);";
   con.query(callExampleProcedureSql)
   selectFullFormSql = 'SELECT @fullform;';
   con.query(selectFullFormSql, function (err, result) {
       if (err) throw err;
       console.log("Full form of degree:");
       console.log(result);
       console.log("--------------------------");
      });
     
   //Passing an invalid value to the procedure
   callNonExistingProcedureSql = "CALL procedureEx ('BBC', @fullform);";
   con.query(callNonExistingProcedureSql);
   con.query(selectFullFormSql, function (err, result) {
         if (err) throw err;
         console.log("Full form of BBC will leads to an error:");
         console.log(result);
         con.end();
      });
});

输出

获得的输出如下所示 -

Connected!
--------------------------
Procedure example created!
--------------------------
Full form of degree:
[ { '@fullform': 'Bachelor of Science' } ]
--------------------------
C:\Users\Lenovo\desktop\JavaScript\connectDB.js:61
          if (err) throw err;
                             ^

Error: PROCEDURE tutorials.procedureEx does not exist
    at Packet.asError (C:\Users\Lenovo\desktop\JavaScript
ode_modules\mysql2\lib\packets\packet.js:728:17)
    at Query.execute (C:\Users\Lenovo\desktop\JavaScript
ode_modules\mysql2\lib\commands\command.js:29:26)
    at Connection.handlePacket (C:\Users\Lenovo\desktop\JavaScript
ode_modules\mysql2\lib\connection.js:478:34)
    at PacketParser.onPacket (C:\Users\Lenovo\desktop\JavaScript
ode_modules\mysql2\lib\connection.js:97:12)
    at PacketParser.executeStart (C:\Users\Lenovo\desktop\JavaScript
ode_modules\mysql2\lib\packet_parser.js:75:16)
    at Socket. (C:\Users\Lenovo\desktop\JavaScript
ode_modules\mysql2\lib\connection.js:104:25)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
Emitted 'error' event on Query instance at:
    at Query.execute (C:\Users\Lenovo\desktop\JavaScript
ode_modules\mysql2\lib\commands\command.js:39:14)
    at Connection.handlePacket (C:\Users\Lenovo\desktop\JavaScript
ode_modules\mysql2\lib\connection.js:478:34)
    [... lines matching original stack trace ...]
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  code: 'ER_SP_DOES_NOT_EXIST',
  errno: 1305,
  sqlState: '42000',
  sqlMessage: 'PROCEDURE tutorials.procedureEx does not exist',
  sql: "CALL procedureEx ('BBC', @fullform);",
  fatal: true
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Resignal {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String user = "root";
      String password = "password";
      ResultSet rs;
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //lets generate error message using RESIGNAL statement
            String sql = "CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form Varchar(50))
            BEGIN IF degree='B-Tech' THEN SET full_form = 'Bachelor of Technology'; 
            ELSEIF degree='M-Tech' THEN SET full_form = 'Master of Technology'; 
            ELSEIF degree='BSC' THEN SET full_form = 'Bachelor of Science'; 
            ELSEIF degree='MSC' THEN SET full_form = 'Master of Science'; 
            ELSE RESIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'Choose from the existing values', MYSQL_ERRNO = 12121; 
            RESIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Given degree is not valid', MYSQL_ERRNO = 1001; 
            END IF; 
            END";
            st.execute(sql);
            System.out.println("Resignal statement created successfully....!");
            //lets call the above procedure
            String sql1 = "CALL example('BSC', @fullform)";
            st.execute(sql1);
            System.out.println("Procedure called successfully....!");
            //lets retrieve the value variable using SELECT statement...
            String sql2 = "SELECT @fullform";
            rs = st.executeQuery(sql2);
            System.out.println("variable value is: ");
            while(rs.next()) {
               String var = rs.getNString(1);
               System.out.println(var);
            }
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

输出

获得的输出如下所示 -

Resignal statement created successfully....!
Procedure called successfully....!
variable value is: 
Bachelor of Science

import mysql.connector
# 建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# 创建游标对象
cursorObj = connection.cursor()
# 使用 RESIGNAL 语句生成错误消息
resignal_statement = '''
CREATE PROCEDURE example(IN degree VARCHAR(20), OUT full_form VARCHAR(50))
BEGIN
IF degree='B-Tech' THEN SET full_form = 'Bachelor of Technology';
ELSEIF degree='M-Tech' THEN SET full_form = 'Master of Technology';
ELSEIF degree='BSC' THEN SET full_form = 'Bachelor of Science';
ELSEIF degree='MSC' THEN SET full_form = 'Master of Science';
ELSE 
RESIGNAL SQLSTATE '01000'
SET MESSAGE_TEXT = 'Choose from the existing values', MYSQL_ERRNO = 12121;
RESIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Given degree is not valid', MYSQL_ERRNO = 1001;
END IF;
END;
'''
cursorObj.execute(resignal_statement)
print("Stored procedure 'example' created successfully!")
# 调用上述过程
call = "CALL example('BSC', @fullform);"
cursorObj.execute(call)
print("Procedure 'example' called successfully!")
# 您可以使用 SELECT 语句检索变量的值
retrieve = "SELECT @fullform;"
cursorObj.execute(retrieve)
result = cursorObj.fetchone()
print("Retrieved full_form value:", result[0])
# 如果向过程传递无效值，它将生成错误消息
pass_invalid = "CALL procedureEx ('BBC', @fullform);"
try:
    cursorObj.execute(pass_invalid)
except mysql.connector.Error as err:
    print("Error occurred:", err)
# 关闭游标和连接
cursorObj.close()
connection.close()

输出

获得的输出如下所示 -

Stored procedure 'example' created successfully!
Procedure 'example' called successfully!
Retrieved full_form value: Bachelor of Science
Error occurred: 1305 (42000): PROCEDURE tut.procedureEx does not exist

❮ MySQL SIGNAL 语句 MySQL 字符集 ❯

MySQL 8 教程

MySQL 8 数据库

MySQL 8 用户

MySQL 8 表

MySQL 8 查询

MySQL 8 视图

MySQL 8 索引

MySQL 运算符和子句

MySQL 连接

MySQL 键

MySQL 触发器

MySQL 8 数据类型

MySQL 正则表达式

MySQL 全文搜索

MySQL8 函数和运算符

MySQL 8 其他概念

MySQL 8 实用资源

MySQL - RESIGNAL 语句

MySQL RESIGNAL 语句

自定义错误消息

语法

示例

使用 RESIGNAL 处理警告

使用客户端程序发出信号语句

语法

示例

输出

输出

输出

输出

颜色选择器

读后有收获微信请站长喝咖啡

错误报告

您的建议:

感谢您的帮助！