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)

使用客戶端程式重新發出訊號語句

我們還可以使用客戶端程式執行重新發出訊號。

語法

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://: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
列印頁面
上一頁
下一頁
廣告