Summary: in this tutorial, you will learn how to use the MySQL RESIGNAL
statement to re-raise exceptions within stored programs.
Introduction to MySQL RESIGNAL statement
The MySQL RESIGNAL
statement allows you to handle and re-raise an error condition within stored programs, including stored procedures, stored functions, triggers, and events.
To re-raise the error condition alone, you use the RESIGNAL
statement:
RESIGNAL;
Code language: SQL (Structured Query Language) (sql)
To re-raise an exception with new signal information, you use the RESIGNAL
statement with a SET
clause:
RESIGNAL
SET signal_information_item
[, signal_information_item] ...;
Code language: SQL (Structured Query Language) (sql)
To re-raise a new condition value with new signal information:
RESIGNAL condition_value
[SET signal_information_item
[, signal_information_item] ...];
Code language: SQL (Structured Query Language) (sql)
MySQL RESIGNAL statement examples
Let’s explore some examples to learn how to use the RESIGNAL
statement effectively.
1) Using MySQL RESIGNAL to re-raise the same exception
The following example defines a stored procedure that drops a non-existing table XYZ
:
DELIMITER //
CREATE PROCEDURE DropTableXYZ()
BEGIN
-- reraise the error
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
RESIGNAL;
END;
-- drop a table that doesn't exist
DROP TABLE XYZ;
END//
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)
Inside the handler section, we use the RESIGNAL
statement to re-raise the exception. If any SQLEXCEPTION
occurs, the RESGINAL
statement re-raises the same exception.
For example:
CALL DropTableXYZ();
Code language: SQL (Structured Query Language) (sql)
Output:
ERROR 1051 (42S02): Unknown table 'classicmodels.xyz'
Code language: SQL (Structured Query Language) (sql)
2) Using RESIGNAL statement with new signal information
The following example uses the RESIGNAL
statement to re-raise the exception but with new information by setting the MYSQL_ERRNO
to 5:
DROP PROCEDURE DropTableXYZ;
DELIMITER //
CREATE PROCEDURE DropTableXYZ()
BEGIN
-- reraise the error
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
RESIGNAL SET MYSQL_ERRNO = 5;
END;
-- drop a table that doesn't exist
DROP TABLE XYZ;
END//
DELIMITER ;
CALL DropTableXYZ();
Code language: SQL (Structured Query Language) (sql)
If you call the DropTableXYZ()
stored procedure, you’ll see a different error number:
CALL DropTableXYZ();
Code language: SQL (Structured Query Language) (sql)
Output:
ERROR 5 (42S02): Unknown table 'classicmodels.xyz'
Code language: SQL (Structured Query Language) (sql)
Notice that the original error is 1051 not 5.
3) Re-rasing exception with new condition value and signal information:
The following example modifies the stored procedure to re-raise an exception with a new condition and signal information:
DELIMITER //
CREATE PROCEDURE DropTableXYZ()
BEGIN
-- reraise the error
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
RESIGNAL SQLSTATE '45000' SET MYSQL_ERRNO = 5;
END;
-- drop a table that doesn't exist
DROP TABLE XYZ;
END//
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)
Calling the stored procedure will issue a different error:
CALL DropTableXYZ();
Code language: SQL (Structured Query Language) (sql)
Error:
ERROR 5 (45000): Unknown table 'classicmodels.xyz'
Code language: SQL (Structured Query Language) (sql)
Summary
- Use the
RESIGNAL
statement to re-raise an exception.