The DECODE function in Oracle SQL / PLSQL has the functionality of IF-THEN-ELSE statement.
The Syntax for the DECODE function in Oracle SQL / PLSQL is:
SELECT DECODE(expression,search,result,[search, result]…[default])
FROM table_name;
- ‘expression’ is the value to be compared
- ‘search’ is the value to be compared with expression
- ‘result’ is the value which is returned if the expression matches search
- ‘default’ is option and is returned if search in not equal to expression. If default is omitted then DECODE will return NULL.
Let’s take an example for understanding:
Suppose we have a table named ‘employee’ as shown below:
| Employee_Id | Decoded | |||
| 101 | A | 10000 | Sales | 10 |
| 102 | Emp B | 20000 | IT | 20 |
| 103 | Emp C | 28000 | IT | 20 |
| 104 | Emp D | 30000 | Support | |
| 105 | Emp E | 32000 | Sales | 10 |
Example 1:
Using DECODE in SELECT Statement
Suppose we write our query as:
SELECT employee_id
,DECODE(employee_id,101,'A'
,102,'B'
,103,'C'
,104,'D'
,'F') Decoded
FROM employee;
We will get the following output:
| Employee_Id | Decoded |
| 101 | A |
| 102 | B |
| 103 | C |
| 104 | D |
| 105 | F |
The above query is similar to writing
IF employee_id =101 THEN Result = A ELSE IF employee_id = 102 THEN Result = B ELSE IF employee_id = 104 THEN Result = C ELSE IF employee_id = 104 THEN Result = D ELSE Result = F
Here we can see that we have used DECODE function as an IN THEN ELSE loop.