[数据库]Raising Error Conditions with MySQL SIGNAL / RESIGNAL Statements
Summary: in this tutorial, you will learn how to use
RESIGNAL statements to raise error conditions inside stored procedures.
MySQL SIGNAL statement
You use the
SIGNAL statement to return an error or warning condition to the caller from a stored program e.g., stored procedure, stored function, trigger or event. The
SIGNAL statement provides you with control over which information for returning such as value and message
The following illustrates syntax of the
|SIGNAL SQLSTATE | condition_name;|
SET condition_information_item_name_1 = value_1,
condition_information_item_name_1 = value_2, etc;
SIGNAL keyword is a
SQLSTATE value or a condition name declared by the
DECLARE CONDITION statement. Notice that the
SIGNAL statement must always specify a
SQLSTATE value or a named condition that defined with an
To provide the caller with information, you use the
SET clause. If you want to return multiple condition information item names with values, you need to separate each name/value pair by a comma.
condition_information_item_name can be
CURSOR_NAME , etc.
The following stored procedure adds an order line item into an existing sales order. It issues an error message if the order number does not exist.
CREATE PROCEDURE AddOrderItem(
in orderNo int,
in productCode varchar(45),
in qty int,
in price double,
in lineNo int )
DECLARE C INT;
SELECT COUNT(orderNumber) INTO C
WHERE orderNumber = orderNo;
-- check if orderNumber exists
IF(C != 1) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Order No not found in orders table';
-- more code below
First, it counts the orders with the input order number that we pass to the stored procedure.
Second, if the number of order is not 1, it raises an error with
SQLSTATE 45000 along with an error message saying that order number does not exist in the orders table.
45000 is a generic
SQLSTATE value that illustrates an unhandled user-defined exception.
If we call the stored procedure
AddOrderItem() and pass a nonexistent order number, we will get an error message.
MySQL RESIGNAL statement
SIGNAL statement, MySQL also provides the
RESIGNAL statement used to raise a warning or error condition.
RESIGNAL statement is similar to
SIGNAL statement in term of functionality and syntax, except that:
- You must use the
RESIGNAL statement within an error or warning handler, otherwise, you will get an error message saying that “RESIGNAL when handler is not active”. Notice that you can use
SIGNAL statement anywhere inside a stored procedure.
- You can omit all attributes of the
RESIGNAL statement, even the
If you use the
RESIGNAL statement alone, all attributes are the same as the ones passed to the condition handler.
The following stored procedure changes the error message before issuing it to the caller.
CREATE PROCEDURE Divide(IN numerator INT, IN denominator INT, OUT result double)
DECLARE division_by_zero CONDITION FOR SQLSTATE '22012';
DECLARE CONTINUE HANDLER FOR division_by_zero
RESIGNAL SET MESSAGE_TEXT = 'Division by zero / Denominator cannot be zero';
IF denominator = 0 THEN
SET result := numerator / denominator;
Let’s call the
Divide() stored procedure.
In this tutorial, we have shown you how to raise error conditions inside stored programs using