CASE function in Oracle

CASE is an advanced function that the Oracle database supports. It is used to serve as an IF-THEN-ELSE statement. The CASE function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c, Oracle 11g, Oracle 10g and Oracle 9i.

Syntax:

CASE [ expression ]
   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n
   ELSE result
END

Parameters:
expression:
It is an optional parameter which is used to specify the value that will be compared to the list of conditions.
condition_1, condition_2, … condition_n:
It is used to specify the conditions. All the conditions are of the same datatype.
result_1, result_2, … result_n:
It is used to specify the value to be returned if a condition is found to be true. All the results are of the same datatype.

Example:

SELECT name,
CASE student
  WHEN 'PASS' THEN 'The student is PASS’
  WHEN 'FAIL' THEN 'The student is FAIL’
  ELSE 'The Result is on hold’
END
FROM students;

Explanation:
Here, we are passing 2 conditions and 3 results. If student = ‘PASS’, the result will be ‘The student is PASS’. If the first condition does not satisfy, it will move to the next one, and if none of the conditions follow the else result will be the final result.