Decode function in Pl/SQL

Oracle PLSQL DECODE function in an inbuilt function which provides the same functionality as IF THEN ELSE

The syntax for a DECODE function is as below

DECODE( expression , search , result [, search , result]... [, default] )

Here expression is the value to be compared.

Search is the value compared against the expression value.

result is the actual output if expression matches the search value.

finally we have a default value which is used as an output in case expression does not matched the search value.

This will be very clear with an example.

Execute the following query and review the result

select (FIRST_NAME||' '||LAST_NAME) EmpName,
DECODE(EMPLOYEE_ID, 100, 'IBM',
102, 'Deloitte',
103, 'Oracle',
'Microsoft') RESULT
FROM employees;


This program can be also read as

select (FIRST_NAME||' '||LAST_NAME) EmpName from employee table

when Employee_ID =100 display result as IBM
when Employee_ID =101 display result as Deloitte
when Employee_ID =103 display result as Oracle

When the employee_id doesn' match the list provided the default result is microsoft.

This you can also validate from the result.