Oracle Decode Function

June 3, 2020Posted inORACLE, SQL

Oracle DECODE() function compares the first argument with the second argument. If they are equal, the function returns the third argument otherwise returns the default value.

The Oracle/PLSQL DECODE function has the functionality of an IF-THEN-ELSE statement:

SELECT DECODE (1, 1, 'ONE', 'TWO') FROM DUAL;
  • Decode works like the following if statement:
Copy Code
BEGIN
   IF 1 = 1
   THEN
      DBMS_OUTPUT.put_line ('one');
   ELSE
      DBMS_OUTPUT.put_line ('two');
   END IF;
END;
  • If you want to specify a default value when the first argument is not equal to the second one, the default value to the argument list as shown below:
SELECT DECODE (1, 2, 'ONE', 'TWO') FROM DUAL;

Result: TWO
  • This example also works like an if-else statement:
Copy Code
BEGIN
   IF 1 = 2
   THEN
      DBMS_OUTPUT.put_line ('one');
   ELSE
      DBMS_OUTPUT.put_line ('two');
   END IF;
END;
Result: TWO

Oracle Functions

You might also like:

    Tags: