CASE Expressions
Topics
- Simple CASE Expression
- Searched CASE Expression
1. Simple CASE Expression
For this explanation, assume that a simple CASE expression has this syntax:
CASE selector
WHEN { selector_value_1a | dangling_predicate_1a }[ , ..., { selector_value_1n | dangling_predicate_1n } ] THEN result_1
WHEN { selector_value_2a | dangling_predicate_2a }[ , ..., { selector_value_2n | dangling_predicate_2n } ] THEN result_2
...
WHEN { selector_value_na | dangling_predicate_na }[ , ..., { selector_value_nn | dangling_predicate_nn }] THEN result_n
[ ELSEelse_result ]
END;
The selector is an expression (typically a single variable). Each selector_value and each result can be either a literal or an expression. A dangling_predicate can also be used either instead of or in combination with one or multiple selector_values. At least one result must not be the literal NULL.
A dangling_predicate is an ordinary expression with its left operand missing, for example < 2. Using a dangling_predicate allows for more complicated comparisons that would otherwise require a searched CASE statement.
The simple CASE expression returns the first result for which the selector_value or dangling_predicate matches selector. Remaining expressions are not evaluated. If no selector_value or dangling_predicate matches selector, the CASE expression returns else_result if it exists and NULL otherwise.
A list of comma-separated selector_values and or dangling_predicates can be used with each WHEN clause if multiple choices map to a single result. As with selector_values and dangling_predicates listed in separate WHEN clauses, only the first selector_value or dangling_predicate to match the selector is evaluated.
1.1 Simple CASE Expression
This example assigns the value of a simple CASE expression to the variable appraisal. The selector is grade.
DECLAREgrade CHAR(1) := 'B';appraisal VARCHAR2(20);
BEGINappraisal :=CASE gradeWHEN 'A' THEN 'Excellent'WHEN 'B' THEN 'Very Good'WHEN 'C' THEN 'Good'WHEN 'D' THEN 'Fair'WHEN 'F' THEN 'Poor'ELSE 'No such grade'END;DBMS_OUTPUT.PUT_LINE ('Grade ' || grade || ' is ' || appraisal);
END;
/
-- run result
Grade B is Very Good1.2 Simple CASE Expression with WHEN NULL
If selector has the value NULL, it cannot be matched by WHEN NULL, as this example shows.
Instead, use a searched CASE expression with WHEN boolean_expression IS NULL
DECLAREgrade CHAR(1); -- NULL by defaultappraisal VARCHAR2(20);
BEGINappraisal :=CASE gradeWHEN NULL THEN 'No grade assigned'WHEN 'A' THEN 'Excellent'WHEN 'B' THEN 'Very Good'WHEN 'C' THEN 'Good'WHEN 'D' THEN 'Fair'WHEN 'F' THEN 'Poor'ELSE 'No such grade'END;DBMS_OUTPUT.PUT_LINE ('Grade ' || grade || ' is ' || appraisal);
END;
/
-- run result
Grade is No such grade
2.3 Simple CASE Expression with List of selector_values
oracle 23ai 新特性，oracle19c及以下版本不支持
DECLAREsalary NUMBER := 7000;salary_level VARCHAR2(20);
BEGINsalary_level :=CASE salaryWHEN 1000, 2000 THEN 'low'WHEN 3000, 4000, 5000 THEN 'normal'WHEN 6000, 7000, 8000 THEN 'high'ELSE 'executive pay'END;DBMS_OUTPUT.PUT_LINE('Salary level is: ' || salary_level);
END;
/
-- run result
Salary level is: highPL/SQL procedure successfully completed.
2.4 Simple CASE Expression with Dangling Predicates
oracle 23ai 新特性，oracle19c及以下版本不支持
The value of data_val/2 is used as the left operand during evaluation of the dangling_predicates. Using a simple CASE expression as opposed to a searched CASE expression in this situation avoids repeated computation of the selector expression. You can use a list of conditions with any combination of selector_values and dangling_predicates.
DECLAREdata_val NUMBER := 30;status VARCHAR2(20);
BEGINstatus :=CASE data_val/2WHEN < 0, > 50 THEN 'outlier'WHEN BETWEEN 10 AND 30 THEN 'good'ELSE 'bad'END;DBMS_OUTPUT.PUT_LINE('The data status is: ' || status);
END;
/
-- run reslut
The data status is: goodPL/SQL procedure successfully completed.
2. Searched CASE Expression
For this explanation, assume that a searched CASE expression has this syntax:
CASE
WHEN boolean_expression_1 THEN result_1
WHEN boolean_expression_2 THEN result_2
...
WHEN boolean_expression_n THEN result_n
[ ELSEelse_result ]
END]2.1 Searched CASE Expression
This example assigns the value of a searched CASE expression to the variable appraisal.
DECLAREgrade CHAR(1) := 'B';appraisal VARCHAR2(120);id NUMBER := 8429862;attendance NUMBER := 150;min_days CONSTANT NUMBER := 200;FUNCTION attends_this_school (id NUMBER)RETURN BOOLEAN ISBEGINRETURN TRUE;END;
BEGINappraisal :=CASEWHEN attends_this_school(id) = FALSETHEN 'Student not enrolled'WHEN grade = 'F' OR attendance < min_daysTHEN 'Poor (poor performance or bad attendance)'WHEN grade = 'A' THEN 'Excellent'WHEN grade = 'B' THEN 'Very Good'WHEN grade = 'C' THEN 'Good'WHEN grade = 'D' THEN 'Fair'ELSE 'No such grade'END;DBMS_OUTPUT.PUT_LINE('Result for student ' || id || ' is ' || appraisal);
END;
/
-- run result
Result for student 8429862 is Poor (poor performance or bad attendance)2.2 Searched CASE Expression with WHEN … IS NULL
This example uses a searched CASE expression
DECLAREgrade CHAR(1); -- NULL by defaultappraisal VARCHAR2(20);
BEGINappraisal :=CASEWHEN grade IS NULL THEN 'No grade assigned'WHEN grade = 'A' THEN 'Excellent'WHEN grade = 'B' THEN 'Very Good'WHEN grade = 'C' THEN 'Good'WHEN grade = 'D' THEN 'Fair'WHEN grade = 'F' THEN 'Poor'ELSE 'No such grade'END;DBMS_OUTPUT.PUT_LINE ('Grade ' || grade || ' is ' || appraisal);
END;
/
-- run result
Grade is No grade assignedPL/SQL procedure successfully completed.
