The PL/SQL CASE Statement
The CASE statement
was introduced in Oracle8i SQL, but it was not until Oracle9i
that we could use the CASE statement inside PL/SQL. The CASE
statement is like a series of IF statements, only using the key
word WHEN. A CASE statement is evaluated from top to bottom.
If a condition is true, then corresponding THEN clause
is executed and execution jumps to the END CASE (short circuit
evaluation) clause.
case
when n_numb = 1 then v_status := ‘very small’;
when n_numb < 4 then v_status := ‘small’;
when n_numb = 5 then v_status := ‘even’;
when n_numb > 4 then v_status := ‘large’;
else v_status := ‘very large’;
end case;
Note that the ELSE clause is optional, but
it is similar to the IF statement because the CASE statement
must end with an END CASE clause.
SQL>
declare
2 n_numb number := &Entry;
3 v_status varchar2(40);
4 begin
5
6 case
7 when n_numb = 1
8 then v_status := 'very small';
9 when n_numb < 4
10 then v_status := 'small';
11 when n_numb = 5
12 then v_status := 'even';
13 when n_numb > 4
14 then v_status := 'large';
15 else v_status := 'very large';
16 end case;
17
18 dbms_output.put_line ('Status: '|| v_status);
19 end;
20 /
Enter
value for entry: 3
old 2: n_numb number := &Entry;
new 2: n_numb number := 3;
Status: small
SQL> /
Enter value for entry: 1
old 2: n_numb number := &Entry;
new 2: n_numb number := 1;
Status: very small
Notice that on the second run, the entered
value was 1. The condition on line 7 was true, the status was
set and execution jumped to line 12. Had the statement in line
8 executed, its condition would also evaluate to true and the
status would have been set to ‘small’. Thus, once a condition
evaluates to true, the corresponding THEN clause
is executed, and the code execution jumps to the END CASE
clause.
Each WHEN clause
in the CASE statement
executes a separate evaluation and the evaluation need not use
the same formula or variables.
SQL>
declare
2 n_1 number := &Entry;
3 n_2 number := 0;
4 n_3 number := 4;
5 v_results varchar2(40);
6 begin
7 case
8 when (n_1 + n_2 > 10)
9 then v_results := 'Case 1 true';
10 when ((n_1 + 6) < 10)
11 then v_results := 'Case 2 true';
12 when ((n_2*n_3) < 5)
13 then v_results := 'Case 3 true';
14 else v_results := 'No case is true';
15 end case;
16
17 dbms_output.put_line (v_results);
18 end;
19 /
Enter value for entry: 3
old 2: n_1 number := &Entry;
new 2: n_1 number := 3;
Case 2 true
SQL> /
Enter value for entry: 11
old 2: n_1 number := &Entry;
new 2: n_1 number := 11;
Case 1 true
SQL> /
Enter value for entry: 5
old 2: n_1 number := &Entry;
new 2: n_1 number := 5;
Case 3 true
In the above example each WHEN clause
has a condition that test a different set of variables. Again,
the case exhibits the short circuit evaluation, with execution
jumping to the END CASE clause once a condition evaluates as
true and the corresponding THEN clause is
executed.
If all the conditions evaluate to false and
there is no ELSE clause a CASE_NOT_FOUND exception is raised.
Exceptions are covered in Chapter 3, “Procedures, Functions and
Exception Handling”. Another form of the CASE statement
evaluates a single expression against a set of results.
SQL>
declare
2 n_numb number := &Entry;
3 v_results varchar2(40);
4 begin
5 case n_numb
6 when 3 then v_results := 'very small';
7 when 4 then v_results := 'small';
8 when 5 then v_results := 'even';
9 when 6 then v_results := 'large';
10 else v_results := 'Not a valid value';
11 end case;
12
13 dbms_output.put_line (v_results);
14 end;
15 /
Enter value for entry: 3
old 2: n_numb number := &Entry;
new 2: n_numb number := 3;
very small
SQL> /
Enter value for entry: 6
old 2: n_numb number := &Entry;
new 2: n_numb number := 6;
large
SQL> /
Enter value for entry: 8
old 2: n_numb number := &Entry;
new 2: n_numb number := 8;
Not a valid value
Here the variable n_numb is evaluated
against the numbers in the WHEN clause
. Using the CASE statement is
neither more nor less efficient than the IF/THEN/ELSIF
/ELSE. It should be used to improve the readability or
maintainability of your code.
