DECODE Function In Oracle
DECODE is an advanced function that the Oracle database supports. It is used to work as an IF-THEN-ELSE statement. The DECODE function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c, Oracle 11g, Oracle 10g and Oracle 9i.
DECODE( expression, search, result, search, result... , default )
expression: It is used to specify the value to be compared.
search: It is used to specify the value to be compared against expression.
result: It is used to specify the value to return, if expression is equal to search.
default: It is used to specify the default value to be returned if no matches are found.
SELECT name, DECODE ( student_id, 1, 'Tom', 2, 'Mike', 3, 'Harry', 'Jim') result FROM students;
Here, each student_id value will be compared one by one by the DEFAULT function. The default value ( ‘Jim’ ) will be returned if no matches are found.