Shulou(Shulou.com)06/01 Report--

This article gives an example of error conditions (SIGNAL and RESIGNAL statements) in a stored procedure that mysql throws. 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 take a look at the SIGNAL statement first. We usually 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 that returns values, such as values and message SQLSTATE. 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 the SQLSTATE value or condition name declared by the DECLARE CONDITION statement. Note, however, that the SIGNAL statement must always specify the SQLSTATE value or naming condition defined with the SQLSTATE value. When we're done, if we want to provide information to the caller, we have to use the SET clause, and if we want to return multiple conditional information item names with 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 and so on. Let's look at a stored procedure that adds order line items 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

At first, it counts the order using the input order number passed to the stored procedure, and if the order number is not 1, it raises an error in SQLSTATE 45000 and an error message that there is no order number in the orders table. Where 45000 is a generic SQLSTATE value that describes unhandled user-defined exceptions.

If we call the stored procedure AddOrderItem (), but pass an order number that does not exist, we will receive an error message:

CALL AddOrderItem (10 pencils, S10, 1678, 1, 95.7, 1)

Execute the above code to get the following results:

Mysql > CALL AddOrderItem (10 recordings S10, 1678, 1, 95.7); 1644-Order No not found in orders tablemysql >

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

You must use the restore statement in the error or warning handler, or you will receive an error message indicating "RESIGNAL when handler is not active". Note that you can use the signal statement anywhere in the stored procedure. You can omit all attributes of the RESIGNAL statement, or even the SQLSTATE value.

If you use the declare statement alone, all properties 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 '22012Denominator cannot be zero';; 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 to call:

Mysql > CALL Divide; 1644-Division by zero / Denominator cannot be zero

All right, that's all for this record. I don't know if you've got anything.

More readers who are interested in MySQL-related content can check out this site topic: "MySQL stored procedure skills Collection", "MySQL Common function Summary", "MySQL Log Operation skills Collection", "MySQL transaction Operation skills Summary" and "MySQL Database Lock related skills Summary".

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.