example
Notice the five Boolean expressions. In each instance, we test whether the same variable, grade, is equal to one of five values: 'A', 'B', 'C', 'D', or 'F'. Let us rewrite the preceding code using the CASE statement, as follows:
CASE category
WHEN 'M' THEN dbms_output.put_line('MANAGER');
WHEN 'P' THEN dbms_output.put_line('PROGRAMMER');
WHEN 'C' THEN dbms_output.put_line('CEO');
WHEN 'D' THEN dbms_output.put_line('DBA');
ELSE dbms_output.put_line('No such grade');
END CASE;
The CASE statement is more readable and more efficient. So, when possible, rewrite lengthy IF-THEN-ELSIF statements as CASE statements.
The CASE statement begins with the keyword CASE. The keyword is followed by a selector, which is the variable grade in the last example. The selector expression can be arbitrarily complex. For example, it can contain function calls. Usually, however, it consists of a single variable. The selector expression is evaluated only once. The value it yields can have any PL/SQL datatype other than BLOB, BFILE, an object type, a PL/SQL record, an index-by-table, a varray, or a nested table.
The selector is followed by one or more WHEN clauses, which are checked sequentially. The value of the selector determines which clause is executed. If the value of the selector equals the value of a WHEN-clause expression, that WHEN clause is executed. For instance, in the last example, if grade equals 'C', the program outputs 'Good'. Execution never falls through; if any WHEN clause is executed, control passes to the next statement.
The ELSE clause works similarly to the ELSE clause in an IF statement. In the last example, if the grade is not one of the choices covered by a WHEN clause, the ELSE clause is selected, and the phrase 'No such grade' is output. The ELSE clause is optional. However, if you omit the ELSE clause, PL/SQL adds the following implicit ELSE clause:
ELSE RAISE CASE_NOT_FOUND;
If the CASE statement selects the implicit ELSE clause, PL/SQL raises the predefined exception CASE_NOT_FOUND. So, there is always a default action, even when you omit the ELSE clause.
The keywords END CASE terminate the CASE statement. These two keywords must be separated by a space. The CASE statement has the following form:
[<
CASE selector
WHEN expression1 THEN sequence_of_statements1;
WHEN expression2 THEN sequence_of_statements2;
...
WHEN expressionN THEN sequence_of_statementsN;
[ELSE sequence_of_statementsN+1;]
END CASE [label_name];
CASE statements can be labeled.Exceptions raised during the execution of a CASE statement are handled in the usual way. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram.
An alternative to the CASEstatement is the CASE expression, where each WHEN clause is an expression.
Searched CASE Statement
PL/SQL also provides a searched CASE statement, which has the form:
[<
CASE
WHEN search_condition1 THEN sequence_of_statements1;
WHEN search_condition2 THEN sequence_of_statements2;
...
WHEN search_conditionN THEN sequence_of_statementsN;
[ELSE sequence_of_statementsN+1;]
END CASE [label_name];
The searched CASE statement has no selector. Also, its WHEN clauses contain search conditions that yield a Boolean value, not expressions that can yield a value of any type. An example follows:
CASE
WHEN grade = 'M' THEN dbms_output.put_line('MANAGER');
WHEN grade = 'P' THEN dbms_output.put_line('PROGRAMMER');
WHEN grade = 'C' THEN dbms_output.put_line('CEO');
WHEN grade = 'D' THEN dbms_output.put_line('DBA');
ELSE dbms_output.put_line('No such grade');
END CASE;
The search conditions are evaluated sequentially. The Boolean value of each search condition determines which WHEN clause is executed. If a search condition yields TRUE, its WHEN clause is executed. If any WHEN clause is executed, control passes to the next statement, so subsequent search conditions are not evaluated.
If none of the search conditions yields TRUE, the ELSE clause is executed. The ELSE clause is optional. However, if you omit the ELSE clause, PL/SQL adds the following implicit ELSE clause:
ELSE RAISE CASE_NOT_FOUND;
Exceptions raised during the execution of a searched CASE statement are handled in the usual way. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram
