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.