MySQL - 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 程序执行 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)
示例
以下是程序 -
$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
