CASE Statement in MySQL
MySQL CASE vs. IF
Both IF and CASE statements allow executing a block of code based
on a specific condition.
- A
simple
CASEstatement is more readable and efficient than anIFstatement when comparing a single expression against a range of unique values. - When
checking complex expressions based on multiple values, the
IFstatement is easier to understand. - If
use the
CASEstatement, have to make sure that at least one of theCASEconditions is matched. Otherwise, need to define an error handler to catch the error. (not do this with theIFstatement). - In
some situations, use both
IFandCASEto make the code more readable and efficient.
·
Use
for complex conditional statements inside stored procedures.
·
An alternative conditional statement.
·
Make
the code more readable and efficient.
·
The CASE statement has two forms: simple CASE and searched CASE statements.
CASE Statement
Simple CASE statement
Syntax:
CASE case_value
WHEN when_value1 THEN statements
WHEN when_value2 THEN statements
...
[ELSE else-statements]
END CASE;
·
Simple CASE statement sequentially compares the case_value with the when_value1, when_value2, … until it finds one is equal.
·
When
the CASE finds a case_value equal to a when_value, it executes statements in the corresponding
·
THEN clause.
·
If CASE cannot find any when_value equal to the case_value, it executes the else-statements in the ELSE clause if the ELSE clause is available.
·
When
the ELSE clause does not exist and
the CASE cannot find any when_value equal to the case_value, it issues an error: “ Case not found for CASE statement”.
Note:-
That the case_value can
be an exact value or an expression. The statements can be one or more SQL
statements, and cannot have zero statements.
·
To
avoid the error when the case_value, use an empty BEGIN END block in the ELSE clause:
CASE case_value
WHEN when_value1 THEN ...
WHEN when_value2 THEN ...
ELSE
BEGIN
END;
END CASE;
·
The
simple CASE statement tests for equality
( =), not use it with NULL (NULL = NULL returns FALSE).
Example:-
DELIMITER $$
CREATE PROCEDURE GetCustomerShipping( IN pCustomerNUmber INT, OUT pShipping VARCHAR(50) )
BEGIN
DECLARE customerCountry VARCHAR(100);
SELECT country INTO customerCountry FROM customers WHERE customerNumber = pCustomerNUmber;
CASE customerCountry
WHEN 'USA' THEN
SET pShipping = '2-day Shipping';
WHEN 'Canada' THEN
SET pShipping = '3-day Shipping';
ELSE
SET pShipping = '5-day Shipping';
END CASE;
END$$
DELIMITER ;
Explanation:-
·
The getcustomershipping() stored procedure accepts two parameters: pcustomernumber as an IN parameter and pshipping as an OUT parameter.
·
In
this stored procedure:
·
First, select the country of the customer
from the customers table by the input customer
number.
·
Second, use the simple CASE statement to determine the shipping time based on
the country of the customer. If the customer locates in USA , the shipping time is 2-day shipping . If the customer locates
in Canada , the shipping time is 3-day shipping . The customers from other
countries have 5-day shipping .
·
The
following flowchart demonstrates the logic of the CASE statement for
determining the shipping time:
CALL GetCustomerShipping(112,@shipping);
the statement returns the shipping time of the customer 112:
SELECT @shipping;
Output:
+----------------+
| @shipping |
+----------------+
| 2-day Shipping |
+----------------+
1 row in set (0.00 sec)
Searched CASE statement
·
The
simple CASE the statement only allows comparing
a value with a set of distinct values.
·
If
To perform more complex matches such as ranges, then use the searched CASE statement.
·
The
searched CASE statement is equivalent to
the IF statement, but it’s more
readable than the IF statement.
CASE
WHEN search_condition1 THEN statements
WHEN search_condition1 THEN statements
...
[ELSE else-statements]
END CASE;
In this syntax: -
· Searched CASE evaluates each search_condition in the WHEN clause until it finds to TRUE, then it executes the corresponding THEN clause statements.
· If no search_condition evaluates to TRUE, the CASE will execute else-statements in the ELSE clause if an ELSE clause is available.
· Similar to the simple CASE statement, if don’t specify an ELSE clause and no condition is TRUE, then MySQL raises the same error:
· Case not found for CASE statement
· Mysql does not allow to have empty statements in the THEN or ELSE clause. For preventing MySQL raising error, use an empty BEGIN END block in the ELSE clause.
Example
Use a searched case statement to find customer level silver, gold, or platinum-based on the customer’s credit limit.
DELIMITER $$
CREATE PROCEDURE GetDeliveryStatus(IN pOrderNumber INT,
OUT pDeliveryStatus VARCHAR(100) )
BEGIN
DECLARE waitingDay INT DEFAULT 0;
SELECT DATEDIFF(requiredDate, shippedDate) INTO waitingDay FROM orders WHERE orderNumber = pOrderNumber;
CASE
WHEN waitingDay = 0 THEN
SET pDeliveryStatus = 'On Time';
WHEN waitingDay >= 1 AND waitingDay < 5 THEN
SET pDeliveryStatus = 'Late';
WHEN waitingDay >= 5 THEN
SET pDeliveryStatus = 'Very Late';
ELSE
SET pDeliveryStatus = 'No Information';
END CASE;
END$$
DELIMITER ;
Explanations :-
·
The
stored procedure getdeliverystatus() accepts an order number as an IN parameter and returns the delivery status as
an OUT parameter.
·
First, calculate the number of days between
the required date and shipped date.
·
Second, determine the delivery status based
on the number of waiting days using the searched CASE
·
Statement:
- If
the number of waiting days is zero, then the delivery is on time.
- When
the number of waiting days is between 1 and 5, the delivery is late.
- When
the number of waiting days is more than 5 days, then the delivery is very
late.
- If
the number of waiting days is NULL or else, the delivery has the status of
no information specified in the
ELSEclause.
Uses the stored procedure GetDeliveryStatus() to get the delivery status of
the order 10100 :
CALL GetDeliveryStatus(10100,@delivery);
Output:-
0 Comments