PL/SQL IF versus DECODE

[luciffer]

What is actually better (provide an explanantion) in Oracle 9i2, an IF...ELSE...END IF statement or a DECODE.

I've read that you should use decode instead of IF in some places and read that you should IF instead of DECODE in other places.

Some clarification for an oracle noob would be nice.

[jmodic]

It is not a question of which one is better, because they are mutualy exclusive.

DECODE is an SQL function that you can use only in an SQL DML statement, but you can't use it in an PL/SQL procedural construct (you can use it in an SQL statement inside the PL/SQL construct, but that is still SQL statement).

IF-ELSE-ENDIF on the other hand is a PL/SQL flow-controll structure that you can only use in a pure PL/SQL construct and can never be used inside any SQL statement.

I don't say that you can't sometime use a clever DECODE inside a SELECT in order to avoid unnecessary PL/SQL's IF-ELSE logic or vice versa, but you can't compare them in general as to which one is better. Comparing DECODE versus CASE yes, but DECODE versus IF-ELSE not.