MySQL - RESIGNAL 语句

Table of content
上一页
下一页

在使用 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
医学博士

如果将无效值传递给过程，它将生成如下错误消息：

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)

Learn MySQL in-depth with real-world projects through our MySQL certification course. Enroll and become a certified expert to boost your career.

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

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

语法

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....!\n");
}
//lets call the above procedure
$sql = "CALL example_new('BSC', @fullform)";
if($mysqli->query($sql)){
    printf("Procedure called successfully...!\n");
}
//lets retirve the value variable using SELECT statement...
$sql = "SELECT @fullform";
if($result = $mysqli->query($sql)){
    printf("Variable value is: \n");
    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\node_modules\mysql2\lib\packets\packet.js:728:17)
    at Query.execute (C:\Users\Lenovo\desktop\JavaScript\node_modules\mysql2\lib\commands\command.js:29:26)
    at Connection.handlePacket (C:\Users\Lenovo\desktop\JavaScript\node_modules\mysql2\lib\connection.js:478:34)
    at PacketParser.onPacket (C:\Users\Lenovo\desktop\JavaScript\node_modules\mysql2\lib\connection.js:97:12)
    at PacketParser.executeStart (C:\Users\Lenovo\desktop\JavaScript\node_modules\mysql2\lib\packet_parser.js:75:16)
    at Socket. (C:\Users\Lenovo\desktop\JavaScript\node_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\node_modules\mysql2\lib\commands\command.js:39:14)
    at Connection.handlePacket (C:\Users\Lenovo\desktop\JavaScript\node_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
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
cursorObj = connection.cursor()
# Using the RESIGNAL Statement to generate an error message
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 the above procedure
call = "CALL example('BSC', @fullform);"
cursorObj.execute(call)
print("Procedure 'example' called successfully!")
# You can retrieve the value of the variable using SELECT statement
retrieve = "SELECT @fullform;"
cursorObj.execute(retrieve)
result = cursorObj.fetchone()
print("Retrieved full_form value:", result[0])
# If you pass an invalid value to the procedure, it will generate an error message
pass_invalid = "CALL procedureEx ('BBC', @fullform);"
try:
    cursorObj.execute(pass_invalid)
except mysql.connector.Error as err:
    print("Error occurred:", err)
# Closing the cursor and connection
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
打印页面
上一页
下一页
广告