Using IF in Oracle

DbIcon

“How do I do an IF statement in SQL” this is one of the most common search terms when someone is first learning to use Oracle. The answer is there really isn’t a function called IF that works the way you’d expect in SQL. You can use it in PL/SQL but not really in SQL. There are other methods such as using DECODE or CASE statements that provides this functionality.

But, what if you need IF in SQL for some reason? Can it be done? Of course, you just need to create your own conditional logic function. Recently in migrating from a legacy scripting application to a new system I found that I actually needed to the same “IF” keyword as Excel and VBA users were accustom too. The below function closely mimics that functionality for values values that are expected to be returned as numbers such as IF(1>2,3,4).

Oracle Excel style IF function:

FUNCTION FAKE_IF( In_Predicate_Text IN VARCHAR2,
In_True_Value IN NUMBER DEFAULT 0,
In_False_Value IN NUMBER DEFAULT 0)RETURN NUMBER
IS
Return_Count PLS_INTEGER;
BEGIN

EXECUTE IMMEDIATE 'SELECT COUNT (ROWID) FROM DUAL WHERE '
|| In_Predicate_Text INTO Return_Count;

IF Return_Count = 1 THEN
RETURN NVL(In_True_Value,0);
ELSE
RETURN NVL(In_False_Value,0);
END IF;

END EVAL_IF;


But Remember…



Unless your business need really requires you to take this approach I would avoid creating your own functions if they already exist in the system. Doing so increases both your maintenance burden and the complexity of the system. Just looking at performance DECODE and CASE statements are at least 30% faster. Below is some more information about Oracle’s build in conditional functions.



DECODE:



Oracle has a function called DECODE. This function is how they have implemented IF-THEN-ELSE logic. The syntax follows this format: decode( expression , search , result [, search , result]... [, default] ) Similar to the above IF expression the expression is your logic to evaluate (1>2). The following parameters allow you to specify a result returned for the expression and a default if none of the conditions have been met.



CASE Statement



In Oracle 9i, they introduced the ability to perform CASE statements. Similar to VB case statements, or C# switch statements you can perform complex conditional logic.



The syntax follows the format below:



CASE  [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END

0 comments:

Post a Comment