16 January 2010

The Case for the Case Statement

Oracle 9i introduced the CASE Statement and CASE Expressions.
Some say that the CASE statement is a drop-in replacement of the IF statement, but there is a subtle difference between these two.
In this post I will explain the difference between the Case statement and the IF statement. Sometimes it's "better" to change the IF statement to a CASE statement.

The CASE statement, like the CASE expression, comes in two flavors: Simple and Searched. To illustrate, this is the Simple CASE

SQL> declare
2 selector varchar2(1);
3 begin
4 selector := 'Y';
5 case selector
6 when 'Y' then
7 dbms_output.put_line ('Yes!');
8 when 'N' then
9 dbms_output.put_line ('No..');
10 end case;
11 end;
12 /
Yes!

and this is the Searched Case

SQL> declare
2 selector varchar2(1);
3 begin
4 selector := 'Y';
5 case
6 when selector = 'Y' then
7 dbms_output.put_line ('Yes!');
8 when selector = 'N' then
9 dbms_output.put_line ('No..');
10 end case;
11 end;
12 /
Yes!

Notice the subtle difference in lines 5 and 6.
The difference is the type of comparison that you do. With the Simple Case, you do an equality comparison and with the Searched Case, you can do whatever you feel like - in this case also an equality comparison. When you want to use a comparison with NULL, than your only option is to use a Searched Case. An equality comparison with NULL is always False.
Also note that the Case Statement ends with an "END CASE", just like the IF statement ends with an "END IF".

Can you replace an IF statement with a CASE statement? Well, yes and no. Let me explain.
The IF statement and the CASE statement look very much alike, the difference is in whether or not a match is found.
Say you want to create a function which return a Yes or No depending on your input value. Of course this is a completely stupid example, but it's the idea that counts.


SQL> create or replace
2 function fun (p_in in varchar2)
3 return varchar2
4 is
5 l_in varchar2 (1);
6 retval varchar2 (20);
7 begin
8 l_in := upper (p_in);
9 if l_in = 'Y'
10 then
11 retval := 'Yes!';
12 elsif l_in = 'N'
13 then
14 retval := 'No...';
15 end if;
16 return retval;
17 end fun;
18 /

Function created.

So far, so good. Let's see if it works as expected.

SQL> begin
2 dbms_output.put_line ('Input Y: '||fun ('Y'));
3 dbms_output.put_line ('Input N: '||fun ('N'));
4 end;
5 /
Input Y: Yes!
Input N: No...

PL/SQL procedure successfully completed.

Looks good to me, but what if someone would call the function with a different input value?

SQL>
SQL> begin
2 dbms_output.put_line ('Input Z: '||fun ('Z'));
3 end;
4 /
Input Z:

PL/SQL procedure successfully completed.

Hmmmm,... the function is successfully completed, but the return value is NULL. If this alright, than you're done. If the Business Rule says it is not allowed, than you would need to change your code. For instance with an ELSE in the IF statement, which raises an exception. Or use the CASE statement and see how you can benefit from it.
First let's rewrite the function:

SQL> create or replace
2 function fun (p_in in varchar2)
3 return varchar2
4 is
5 l_in varchar2 (1);
6 retval varchar2 (20);
7 begin
8 l_in := upper (p_in);
9 case l_in
10 when 'Y' then
11 retval := 'Yes!';
12 when 'N' then
13 retval := 'No...';
14 end case;
15 return retval;
16 end fun;
17 /

Function created.

...and do the same test we did before

SQL>
SQL> begin
2 dbms_output.put_line ('Input Y: '||fun ('Y'));
3 dbms_output.put_line ('Input N: '||fun ('N'));
4 end;
5 /
Input Y: Yes!
Input N: No...

PL/SQL procedure successfully completed.

So far, so good. Now for the not allowed value:

SQL> begin
2 dbms_output.put_line ('Input Z: '||fun ('Z'));
3 end;
4 /
begin
*
ERROR at line 1:
ORA-06592: CASE not found while executing CASE statement
ORA-06512: at "ALEX.FUN", line 8
ORA-06512: at line 2

As you can see, it blows up in your face. Because there is not a match for the incoming value, the Case statement raises an exception - something that the IF statement doesn't do for you out-of-the-box.
This is just what the Business Rule ordered. No need for extra coding to raise an exception.
Can this exception be handled gracefully? Yes, it can. Oracle gives us the "CASE_NOT_FOUND" exception.

SQL> begin
2 dbms_output.put_line ('Input Z: '||fun ('Z'));
3 exception
4 when case_not_found
5 then
6 dbms_output.put_line ('Gracefully Handled');
7 end;
8 /
Gracefully Handled

So, can the IF statement be replaced: yes. Can it be done without thinking about the consequences: no.

7 comments:

  1. Alex,

    Thanks. I was not aware of CASE_NOT_FOUND exception. I always use CASE...WHEN OTHERS THEN.

    ReplyDelete
  2. Your welcome. I guess you mean the ELSE clause of the CASE statement, WHEN OTHERS can only appear in an Exception Block.

    ReplyDelete
  3. Please note that only case statements raise CASE_NOT_FOUND, case expressions return null:

    gDEV>r
    1 declare
    2 procedure test(
    3 i_testcase in varchar2,
    4 i_in in varchar2)
    5 is
    6 begin
    7 dbms_output.put_line(
    8 i_testcase||
    9 ': '||
    10 case i_in
    11 when 'Y' then 'Yes!'
    12 when 'N' then 'No...'
    13 end);
    14 end;
    15 begin
    16 test('Input Y', 'Y');
    17 test('Input N', 'N');
    18 test('Input Z', 'Z');
    19* end;
    Input Y: Yes!
    Input N: No...
    Input Z:

    PL/SQL-Prozedur erfolgreich abgeschlossen.

    ReplyDelete
  4. Indeed, thank you for this addition.

    ReplyDelete
  5. Hello there! Have you ever been in such a situation when a person has stolen your personal ideas? Can't wait to hear from you.

    ReplyDelete
  6. This is awesome - thanks! Nice one here too on searched case statements:

    http://www.programmerinterview.com/index.php/database-sql/sql-searched-case-expression/

    ReplyDelete