I agree, although I'm not sure if my agreement is more because of developer convenience or the pragmatic (performance) aspect. When you use a CASE expression, you skip the step of putting the IF/THEN test into its own function, so a purist might say it's bad that you end up duplicating code if you use the same CASE in several different queries. However, I have found CASE to be faster than I expected in queries producing big result sets, so I use it sometimes in preference to setting up a real function and making a function-based index.
As Chris mentioned, CASE lets you turn a PL/SQL function into essentially a one-liner, just a RETURN statement that covers all the possible bases.
In a SQL query, CASE can take the place of a function call, either for transformation of the output results:
when 'NL' then 'Newfoundland'
when 'ON' then 'Ontario'
when 'CA' then 'California'
else 'Some other place'
or for a condition in the WHERE clause:
where vacation_days >
when emp_type = 'VP' then 10
when years_service > 5 then 5
or (my favorite) in some clause where you wouldn't normally think of it, such as putting a dynamic ORDER BY clause into a query inside a PL/SQL procedure:
for item in (select ... where ...
order by case
when param = 'by_date' then date_added
when param = 'by_name' then last_name
As Boneist pointed out, my original assertion that CASE forces you to cover all possibilities is not correct for CASE expressions. For example:
select nvl(case when 1 = 0 then 'foo' end, 'bar') from dual;
returns 'bar', demonstrating that if the CASE expression "falls through" without matching any WHEN clauses, the result is null.
It is true for a CASE statement rather than an expression:
when 1 = 0 then null;
That will throw an exception, because of the lack of an else clause. I can never remember whether that behavior is for the statement or the expression, because it's the opposite of what I would like. If I'm selecting actions, I'd like a default ELSE condition of "do nothing", so I could leave it out. But if I'm selecting values, I'd prefer to be forced to consider all cases.
I fretted about the CASE statement vs. expression difference when I first wrote it up in the PL/SQL User's Guide. Is there enough cross-linking so that people will see both forms? Is there too much cross-linking so that people will get the expression and the statement mixed up?
Also, do we really need to use a special term "searched case" for one variety of syntax? I suppose it is a long-standing term, since I see it used in SQL Server and MySQL contexts too. I just can't imagine going into a code review and arguing with someone that they should or shouldn't use "searched case", without having to define it and clarify whether that meant it just dives into the WHEN clauses, or starts with CASE something.
Anyway, knowing about the statement as opposed to the expression does have some point in the functional programming environment. Let's say you wanted to rewrite the COMPARE function from my earlier post using CASE, but if something went wrong, it should raise an exception rather than return a value. Instead of coding that as a single statement starting "RETURN CASE ...", you'd write:
function compare(x number, y number) return number is
when x > 0 then return 1;
when x < 0 then return -1;
when x = 0 then return 0;
-- If there is some unexpected combination of parameters, NULLs or NaNs or
-- what have you, need to handle that case also.
else raise some_package.weird_math_error;