The CASE statement is used to construct complex conditional statements.
Syntax
The CASE statement supports two syntax structures, as follows:
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 to compare with each when_value in the WHEN clause. This parameter is used only in the first syntax. |
| when_value | The value to compare with case_value. If case_value equals when_value, the corresponding THEN clause is executed. |
| search_condition | A condition expression that evaluates to True or False. This parameter is used only in the second syntax. |
| statement_list | A list of SQL/PL statements to execute. Each statement is terminated with a semicolon (;). |
| ELSE | Optional. A list of statements to execute when none of the conditions are met. |
Note that the CASE operator is different from the CASE statement. The CASE statement cannot have an ELSE NULL clause and is terminated with END CASE.
In the first syntax, case_value is an expression that is compared with each when_value expression in the WHEN clauses until a match is found. When a match is found, the corresponding THEN clause's statement_list is executed. If no match is found, the ELSE clause's statement_list is executed (if present). This syntax cannot be used to test for equality with NULL, because NULL = NULL evaluates to False.
In the second syntax, each search_condition expression in the WHEN clauses is evaluated until one evaluates to True. At this point, the corresponding THEN clause's statement_list is executed. If no search_condition evaluates to True, the ELSE clause's statement_list is executed (if present).
If no match is found for 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. Empty statement_list is not allowed.
To handle cases where no value matches any WHEN clause, use an ELSE clause that includes an empty BEGIN ... END block.
Example 1: Using 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: Using 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: Using 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: Handling cases where no value matches.
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 ;