La reproduzco totalmente a continuación como referencia, pero la pueden observar en el siguiente link: http://stevenfeuersteinonplsql.blogspot.com/2016/03/the-case-of-confusing-case.html
TUESDAY, MARCH 22, 2016
The Case of the Confusing CASE
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 FALSE
Curious, 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.
Definitivamente curioso el caso. Por supuesto no me pude quedar tranquilo y tuve que experimentar por cuenta propia como resolver el problema y la buena noticia es que lo encontré.
Veamos, partamos de definir el escenario. Mi máquina virtual con OEL 6.5 y Oracle Database 12c R1 12.1.0.2 SE2
SQL> select * from v$version;
SQL> set linesize 200
SQL> /
BANNER
——————————————————————–
Oracle Database 12c Standard Edition 2 Release 12.1.0.2 – 64bit
PL/SQL Release 12.1.0.2
CORE 12.1.0.2
TNS for Linux: Version 12.1.0.2
NLSRTL Version 12.1.0.2
El seudo código que responde de manera adecuada al problema planteado por Steven es el siguiente:
SQL> host more 10.sql
DECLARE
my_flag BOOLEAN;
BEGIN
CASE 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;
/
SQL> @10
my_flag is NULL
PL/SQL procedure successfully completed.
SQL> host more 10.sql
DECLARE
my_flag BOOLEAN := TRUE;
BEGIN
CASE 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;
/
Ejecutando el seudo código anterior
SQL> @10
my_flag is TRUE
PL/SQL procedure successfully completed.
Al probar con el seudo código original, el valor es incorrectamente devuelto.
SCRIPT ORIGINAL
SQL> DECLARE
2 my_flag BOOLEAN;
3 BEGIN
4 CASE my_flag
5 WHEN my_flag IS NULL
6 THEN
7 DBMS_OUTPUT.PUT_LINE (‘my_flag is NULL’);
8 WHEN TRUE
9 THEN
10 DBMS_OUTPUT.PUT_LINE (‘my_flag is TRUE’);
11 ELSE
12 DBMS_OUTPUT.PUT_LINE (‘my_flag is FALSE’);
13 END CASE;
14 END;
15 /
my_flag is FALSE
PL/SQL procedure successfully completed.
SQL>
Resumen:
La forma correcta de evaluar la condición de una variable con condición boleana en un CASE es utilizando la sintaxis:
…CASE WHEN my_flag is NULL THEN
y no
CASE my_flag WHEN my_flag IS NULL THEN