What do you think will be displayed after executing the following block?
DECLARE my_flag BOOLEAN; BEGIN CASE my_flag WHEN my_flag IS NULL THEN DBMS_OUTPUT.PUT_LINE ('my_flag is NULL'); WHEN TRUE THEN DBMS_OUTPUT.PUT_LINE ('my_flag is TRUE'); ELSE DBMS_OUTPUT.PUT_LINE ('my_flag is FALSE'); END CASE; END; /At first glance (if you are like me), you would say "my_flag is NULL", right?
After all, my_flag is initialized to NULL when declared, and I don't change the value.
But, lo and behold, you will see:
my_flag is FALSECurious, right?
So what's going on? Well, we have a very confused and confusing piece of code: I have written a simple CASE (which is of the form CASE expression WHEN ...), but then my WHEN clauses follow a typical searched CASE format (CASE WHEN expr1 ... WHEN expr2 ...).
CASE is a really wonderful feature in PL/SQL (and many other languages, of course), but you need to make sure you use it properly.