Monday, April 21, 2008

A CASE of Functional Programming

Chris mentioned in response to my post on functional programming that the CASE construct is useful in such situations. You can use CASE expressions either in PL/SQL subprograms or in SQL queries.

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. CASE expressions force you to use the ELSE clause, so you're guaranteed to return a value no matter how strange the input arguments. [See correction/digression at end.]

In a SQL query, CASE can take the place of a function call, either for transformation of the output results:

select
case region_code
when 'NL' then 'Newfoundland'
when 'ON' then 'Ontario'
when 'CA' then 'California'
else 'Some other place'
end
from ...

or for a condition in the WHERE clause:

select ...
where vacation_days >
case
when emp_type = 'VP' then 10
when years_service > 5 then 5
else 2
end
...

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
else credit_card_number
end
)


Correction



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:

begin
case
when 1 = 0 then null;
end case;
end;

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
begin
case
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;
end case;
end;

4 comments:

Boneist said...

Er, case statements don't force you to add the else statement...

This works, for example:

select case when 1 = 0 then 'hurray' end from dual;

Chris said...

From a logical viewpoint I tend to agree, case statements should behave like IF/ELSIF/END IF, i.e. not raise an exception. I assumed that, i.e. was wrong, by the way. Regarding case expressions, especially in SQL, I saw them as another and sometimes better kind of decode, which defaults to NULL. Nice examples, by the way ;-).

PL/SQL is, however, a member of the ADA language family, like VHDL, for example. Ignoring personal preferences, I think it's important that the syntax and semantics of language idioms stay the same in the family. So if it's been decided that a missing ELSE in a CASE expression or statement throws an error (see for example http://www.cs.mu.oz.au/~fjh/ada/rm95html-1.0/rm9x-05-04.html, see paragraph 13), that should be it. PL/SQL differs in syntax and semantics. Three-valued logic (true/false/null) might be an explanation for the changed semantics of case expressions, but I'd like to hear the arguments, while not necessarilly disagreeing. Do you have a good connection to the PL/SQL team? I'd really like to hear their point of view, expecially regarding PL/SQL vs. ADA.

There already do exist several differences, for example regarding the case statements, probably for historical reasons. Language design is an interesting topic for at least both of us, as I suspect, but I dont't want to hijack your blog ;-).

Chris said...

I decided to investigate into that a little bit further. CASE statements seem to raise exceptions if no condition is reached (according to the ADA standard), while CASE expressions default to null (contraring the standard):

SQL> r
1 begin
2 case
3 when 1=2 then dbms_output.put_line('weird');
4 end case;
5* end;
begin
*
FEHLER in Zeile 1:
ORA-06592: CASE bei Ausführung von CASE-Anweisung nicht gefunden
ORA-06512: in Zeile 2

SQL> !oerr ora 6592
06592, 00000, "CASE not found while executing CASE statement"
// *Cause: A CASE statement must either list all possible cases or have an
// else clause.
// *Action: Add all missing cases or an else clause.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

5 Zeilen ausgewählt.

SQL> r
1 select *
2 from dual
3 where 1=case
4 when 1=2 then 2
5* end

Es wurden keine Zeilen ausgewählt

SQL> begin
2 dbms_output.put_line(
3 case
4 when 1=2 then 'weird'
5 end
6 );
7 end;
8 /


PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> r
1 select *
2 from dual
3 where case
4 when 1=2 then 'weird'
5 end
6* is null

DUM
---
X

John Russell said...

According to the PL/SQL team:

PL/SQL's Ada-ness began way back with Ada 82, which was before Ada acquired CASE expressions. It only had CASE statements at the time.

Raising an expression in the middle of an expression evaluation wouldn't play nicely with the SQL layer, since CASE can be evaluated as part of a SQL statement.