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:
1 | SELECT employee_id |
2 | ,DECODE(employee_id,101, 'A' |
3 | ,102, 'B' |
4 | ,103, 'C' |
5 | ,104, 'D' |
6 | , 'F' ) Decoded |
7 | 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
1 | IF employee_id =101 THEN |
2 | Result = A |
3 | ELSE IF employee_id = 102 THEN |
4 | Result = B |
5 | ELSE IF employee_id = 104 THEN |
6 | Result = C |
7 | ELSE IF employee_id = 104 THEN |
8 | Result = D |
9 | ELSE |
10 | Result = F |
Here we can see that we have used DECODE function as an IN THEN ELSE loop.