options

Alternative to the if clause on PLSQL

Hi all !

If you’re working with Oracle PLSQL like me, you’ll probably find very boring to be always using

IF(condition) THEN
     dbms_output.println(" result 1");
ELSIF(condition) THEN
     dbms_output.println(" result 2");
ELSE
     dbms_output.println("No result");
END IF;

This is specially annoying when you have lots of conditions to be evaluated. However, there is an alternative to the if clause: CASE-WHEN syntax. 🙂

The CASE-WHEN alternative

The use of the CASE-WHEN syntax is quite straightforward :

CASE
   WHEN condition1 = 'A VALUE' THEN
   dbms_output.println(" result 1");
   WHEN condition1 = 'OTHER VALUE' THEN
   dbms_output.println(" result 2");
   ELSE
   dbms_output.println("No result");
END CASE;

Final thoughts

No more writing endless ‘ELSIF’ !  Another advantage of this syntax is that you can use the ‘CASE-WHEN’ syntax to evaluate segments of a SQL query. If you don’t believe me just try :


SELECT CASE WHEN table1.colmn = 1 THEN
            'HELLO Table 1'
            WHEN table2.colmn = 2 THEN
            'Hello Table 2'
        END test_column
FROM
(select 1 as colmn from dual) table1,
(select 1 as colmn from dual) table2;

This query will output HELLO Table 1 !

Hope you enjoy this syntax as much as I do.

References

[1] PL/SQL User’s Guide and Reference 10g ;4 Using PL/SQL Control Structures;  http://docs.oracle.com/cd/B12037_01/appdev.101/b10807/04_struc.htm