DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003

    PL/SQL IF versus DECODE

    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.

  2. #2
    Join Date
    Dec 2000
    Ljubljana, Slovenia
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.