Example analysis of mysql stored procedures that trigger error conditions in stored procedures (SIGNAL and RESIGNAL statements)

This article uses examples to illustrate the error conditions (SIGNAL and RESIGNAL statements) that MySQL causes in stored procedures. Share with you for your reference, the details are as follows:

In mysql, we can use SIGNAL and RESIGNAL statements to raise error conditions in stored procedures.

Let’s look at the SIGNAL statement first. We typically use the SIGNAL statement to return an error or warning condition to the caller in a stored program such as a stored procedure, stored function, trigger, or event. The SIGNAL statement provides control over the information returned (such as the value and message SQLSTATE). Let's take a look at its grammatical structure:

SIGNAL SQLSTATE | condition_name;
SET condition_information_item_name_1 = value_1,
  condition_information_item_name_1 = value_2, etc;

The SIGNAL keyword is an SQLSTATE value or condition name declared by the DECLARE CONDITION statement. Note, however, that the SIGNAL statement must always specify an SQLSTATE value or a named condition defined using an SQLSTATE value. If we want to provide information to the caller, we have to use the SET clause. If we want to return multiple condition information item names using values, we need to separate each name/value pair with a comma. In the above SQL, condition_information_item_name can be MESSAGE_TEXT, MYSQL_ERRORNO, CURSOR_NAME, etc. Let's look at a stored procedure that adds an order line item to an existing sales order, and issues an error message if the order number does not exist:

DELIMITER $$
CREATE PROCEDURE AddOrderItem(in orderNo int,
 in productCode varchar(45),
 in qty int,in price double, in lineNo int )
BEGIN
 DECLARE C INT;
 SELECT COUNT(orderNumber) INTO C
 FROM orders 
 WHERE orderNumber = orderNo;
 -- check if orderNumber exists
 IF(C != 1) THEN 
 SIGNAL SQLSTATE '45000'
 SET MESSAGE_TEXT = 'Order No not found in orders table';
 END IF;
 -- more code below
 -- ...
END $$
DELIMITER ;

Initially, it counts the orders using the input order number passed to the stored procedure, and then if the number of orders is not 1, it raises an SQLSTATE 45000 error along with an error message that the order number does not exist in the orders table. Where 45000 is a generic SQLSTATE value used to indicate an unhandled user-defined exception.

Let's call the stored procedure AddOrderItem(), but pass a non-existent order number, and we'll get an error message:

CALL AddOrderItem(10,'S10_1678',1,95.7,1);

Execute the above code and get the following results:

mysql> CALL AddOrderItem(10,'S10_1678',1,95.7,1);
1644 - Order No not found in orders table
mysql>

Let's look at the RESIGNAL statement again. It is similar in function and syntax to the SIGNAL statement, with the following differences:

• You must use the RESIGNAL statement in an error or warning handler, otherwise you will receive an error message stating " RESIGNAL when handler is not active ". Note that you can use the SIGNAL statement anywhere in a stored procedure.
• All attributes of the RESIGNAL statement can be omitted, even the SQLSTATE value.

If the RESIGNAL statement is used alone, all attributes are the same as those passed to the condition handler. Let's look at a stored procedure that changes the error message before it is sent to the caller:

DELIMITER $$
CREATE PROCEDURE Divide(IN numerator INT, IN denominator INT, OUT result double)
BEGIN
 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
 SIGNAL division_by_zero;
 ELSE
 SET result := numerator / denominator;
 END IF;
END $$
DELIMITER ;

Then, let's try calling:

mysql> CALL Divide(10,0,@result);
1644 - Division by zero / Denominator cannot be zero

Well, this record ends here. I wonder if you have gained anything from it.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL stored procedure skills", "MySQL common function summary", "MySQL log operation skills", "MySQL transaction operation skills summary" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.