Raising Error Conditions with MySQL SIGNAL/RESIGNAL Statements, raisingresignal
Http://www.mysqltutorial.org/mysql-signal-resignal/
Summary: In this tutorial, you will learn how to useSIGNAL
AndRESIGNAL
Statements to raise error conditions inside stored procedures.
MySQL SIGNAL statement
You useSIGNAL
Statement to return an error or warning condition to the caller from a stored program e.g., stored procedure, stored function, trigger or event.SIGNAL
Statement provides you with control over which information for returning such as value and messageSQLSTATE
.
The following extends strates syntax ofSIGNAL
Statement:
123 |
Signal sqlstate | condition_name; SET condition_information_item_name_1 = value_1, condition_information_item_name_1 = value_2, etc; |
FollowingSIGNAL
Keyword isSQLSTATE
Value or a condition name declared byDECLARE CONDITION
Statement. Notice thatSIGNAL
Statement must always specifySQLSTATE
Value or a named condition that defined withSQLSTATE
Value.
To provide the caller with information, you useSET
Clause. If you want to return multiple condition information item names with values, you need to separate each name/value pair by a comma.
Thecondition_information_item_name
Can beMESSAGE_TEXT
,MYSQL_ERRORNO
,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.
1234567891011121314151617181920212223 |
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 '200' SET MESSAGE_TEXT = 'order No not found in orders table'; end if; -- more code below --... END |
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 errorSQLSTATE 45000
Along with an error message saying that order number does not exist in the orders table.
Notice that45000
Is a genericSQLSTATE
Value that has strates an unhandled user-defined exception.
If we call the stored procedureAddOrderItem()
And pass a nonexistent order number, we will get an error message.
1 |
CALL AddOrderItem (10, 's10 _ 1678 ', 1, 95.7, 1 ); |
MySQL RESIGNAL statement
BesidesSIGNAL
Statement, MySQL also providesRESIGNAL
Statement used to raise a warning or error condition.
TheRESIGNAL
Statement is similarSIGNAL
Statement in term of functionality and syntax, doesn t that:
- You must use
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 useSIGNAL
Statement anywhere inside a stored procedure.
- You can omit all attributes of
RESIGNAL
Statement, evenSQLSTATE
Value.
If you useRESIGNAL
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.
123456789101112131415 |
DELIMITER $ create procedure Divide (IN numerator INT, IN denominator INT, OUT result double) begin declare division_by_zero condition for sqlstate '200 '; declare continue handler for processing resignal set MESSAGE_TEXT = 'division by zero/Denominator cannot be 0'; -- IF denominator = 0 then signal division_by_zero; else set result: = numerator/denominator; end if; END |
Let's callDivide()
Stored procedure.
1 |
CALL Divide (10, 0, @ result ); |
In this tutorial, we have shown you how to raise error conditions inside stored programs usingSIGNAL
AndRESIGNAL
Statements.