A CASE Statement in Oracle SQL / PLSQL is having the functionality of IF-THEN-ELSE Statement.
Syntax of the CASE Statement in Oracle SQL / PLSQL is:
WHEN condition_1 THEN result_1
WHEN consition_2 THEN result_2
WHEN condition_3 THEN result_3
WHEN condition_N THEN result_N
- expression is an optional value, if provided; it is used to compare with various conditions (e.g. condition_1, condition_2, . . condition_N)
- condtion_1 to condition_N must have the same data type, also the conditions are evaluated in the order in which they are listed, hence once a condition evaluates to true the CASE statement returns the result and does not evaluate further conditions.
- result_1 to result_N must also have the same data type, these are the values that will be returned once the condition evaluates to true.
- If no condition evaluates to true then the ‘default_result’ from the ELSE clause will be returned by CASE statement
- If the ELSE clause is omitted and none of the condition evaluates to true then NULL will be returned by CASE Statement.
Let’s see an example to understand how to use CASE in Oracle SQL / PLSQL SELECT Statement:
Suppose we have a table named ‘employee’ as shown below:
Now, if we write our query using CASE Statement in Oracle SQL / PLSQL as:
SELECT employee_id ,employee_name ,salary ,CASE WHEN salary = 30000 THEN 'Salary Between 20000 and 30000' ELSE 'Salary More Than 30000' END SALARY_STATUS FROM employee;
We will get the following result:
|101||Emp A||10000||Salary Less than 20000|
|102||Emp B||20000||Salary Less than 20000|
|103||Emp C||28000||Salary More Than 30000|
|104||Emp D||30000||Salary Between 20000 and 30000|
|105||Emp E||32000||Salary Between 20000 and 30000|
|106||Emp F||20000||Salary Less than 20000|
|107||Emp G||12000||Salary Less than 20000|
|108||Emp H||12000||Salary Less than 20000|
Here we can observe, that the CASE statement returns the text literals ‘Salary less than 20000’, ‘Salary between 20000 and 30000’ and ‘Salary More Than 30000’ for each record based on the salary amount as specified in the CASE Statement.