澳门金沙vip 1

澳门金沙vipRaising Error Conditions with MySQL SIGNAL / RESIGNAL Statements

Summary: in this tutorial, you will learn how to use SIGNAL  and RESIGNAL  statements to raise error conditions
inside stored procedures.

Raising Error Conditions with MySQL SIGNAL / RESIGNAL Statements,raisingresignal

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
messageSQLSTATE.

The following illustrates syntax of the SIGNAL statement:

 

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

Following the SIGNAL keyword is a SQLSTATE value or a condition name declared by
the DECLARE CONDITION statement.
Notice that the SIGNAL statement must always specify
SQLSTATE value or a named
condition that defined with an  SQLSTATE value.

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.

The  condition_information_item_name can be MESSAGE_TEXTMYSQL_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.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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

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.

Notice that 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.

 

1
CALL AddOrderItem(10,’S10_1678′,1,95.7,1);

澳门金沙vip 1

Summary: in this tutorial, you will learn how to use SIGNAL  and RESIGNAL  statements to raise error conditions
inside stored procedures.