The CASE statement is used to construct complex conditional statements.
Syntax
The CASE statement supports the following two syntax structures:
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
Parameters
Parameter |
Description |
|---|---|
| case_value | An expression used to evaluate each WHEN in the clause when_value for comparison. This option is used only in the first syntax. |
| when_value | With case_value The value to be compared. When case_value equal to when_value When the time comes, execute the corresponding THEN clause. |
| search_condition | A conditional expression that evaluates to True or False. This syntax is used only in the second syntax. |
| statement_list | The list of SQL/PL statements to be executed. Each statement must end with a semicolon (;). |
| ELSE | Optional. A list of statements to execute when none of the preceding conditions is met. |
Note that the CASE operator is different from the CASE statement. A CASE statement cannot have an ELSE NULL clause and is terminated by END CASE.
In the first syntax, case_value is an expression that is compared with each when_value expression in a WHEN clause until a match is found. When a value equal to a when_value is found, the corresponding THEN clause's statement_list is executed. If no when_value matches case_value, the ELSE clause's statement_list (if present) is executed. This syntax cannot be used to test equality with NULL, because NULL=NULL evaluates to False.
For the second syntax, the search_condition expressions in each WHEN clause are evaluated until one of them evaluates to True. The corresponding THEN clause's statement_list is then executed. If none of the search_condition expressions evaluate to True, the ELSE clause's statement_list (if present) is executed.
If no result matches the when_value or search_condition, and the CASE statement does not contain an ELSE clause, an error "Case not found for CASE statement" is returned.
Each statement_list consists of one or more SQL statements. An empty statement_list is not allowed.
To handle cases where no value matches any WHEN clause, use an ELSE clause containing an empty BEGIN ... END block.
Example 1: Use the first syntax (with case_value).
obclient> DELIMITER //
obclient> CREATE FUNCTION get_grade_level(score INT)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
DECLARE grade VARCHAR(20);
CASE score
WHEN 90 THEN SET grade = 'A';
WHEN 80 THEN SET grade = 'B';
WHEN 70 THEN SET grade = 'C';
WHEN 60 THEN SET grade = 'D';
ELSE SET grade = 'F';
END CASE;
RETURN grade;
END //
obclient> DELIMITER ;
obclient> SELECT get_grade_level(85);
The query result is as follows:
+------------------+
| get_grade_level(85) |
+------------------+
| F |
+------------------+
1 row in set
Example 2: Use the second syntax (without case_value).
obclient> DELIMITER //
obclient> CREATE FUNCTION get_discount_rate(order_amount DECIMAL(10,2))
RETURNS DECIMAL(3,2)
DETERMINISTIC
BEGIN
DECLARE discount DECIMAL(3,2);
CASE
WHEN order_amount >= 1000 THEN SET discount = 0.20;
WHEN order_amount >= 500 THEN SET discount = 0.15;
WHEN order_amount >= 200 THEN SET discount = 0.10;
ELSE SET discount = 0.05;
END CASE;
RETURN discount;
END //
obclient> DELIMITER ;
obclient> SELECT get_discount_rate(750);
The query result is as follows:
+----------------------+
| get_discount_rate(750) |
+----------------------+
| 0.15 |
+----------------------+
1 row in set
Example 3: Use the CASE statement in a stored procedure.
obclient> DELIMITER //
obclient> CREATE PROCEDURE update_employee_status(IN emp_id_param INT)
BEGIN
DECLARE current_salary DECIMAL(10,2);
SELECT salary INTO current_salary FROM employee_info WHERE emp_id = emp_id_param;
CASE
WHEN current_salary >= 10000 THEN
UPDATE employee_info SET status = 'Senior' WHERE emp_id = emp_id_param;
WHEN current_salary >= 5000 THEN
UPDATE employee_info SET status = 'Middle' WHERE emp_id = emp_id_param;
ELSE
UPDATE employee_info SET status = 'Junior' WHERE emp_id = emp_id_param;
END CASE;
END //
obclient> DELIMITER ;
Example 4: Handle cases where no match is found.
obclient> DELIMITER //
obclient> CREATE PROCEDURE proc()
BEGIN
DECLARE x INT DEFAULT 1;
CASE x
WHEN 2 THEN SELECT 'Value is 2';
WHEN 3 THEN SELECT 'Value is 3';
ELSE
BEGIN
END;
END CASE;
END //
obclient> DELIMITER ;