Explained: CASE in Oracle SQL PL SQL

In this post, we see how to use CASE in Oracle with examples expressions.

USE

CASE statement is an example of IF then ELSE condition in SQL programming

Syntax

CASE [ expression ]

   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n

   ELSE result

END

Characteristics of CASE in Oracle

• CASE is a Statement in SQL / PL SQL
• CASE can use comparison operators like ( IF > )
• CASE can be used as constructs in PL SQL blocks
• CASE can handle predicates (like IN) and searchable subqueries
• You can use Parameter as CASE statements to procedure calls
• CASE treats NULL statement differently
• CASE expects datatype consistency
• CASE is ANSI SQL-compliant
• CASE helps in building conditional logic

Returns value of using CASE in Oracle

• The CASE statement can return value of any datatype like string, numeric, date but all the conditions should the same data type else it will error out.
• If all conditions are not the same datatype, an ORA-00932 error will come.
• If all results are not the same datatype, an ORA-00932 error will come.
• If no condition is satisfied to be true, then the CASE statement will return the value in the ELSE clause.
• If the ELSE clause is omitted and no condition is found to be true, then the CASE statement will return NULL.

EXAMPLES of using CASE Statement in SQL PL SQL

Example 1

DECLARE 
GRADE CHAR(1) :='A'; 
MARKS NUMBER(2) := 85; 
BEGIN 
CASE 
 WHEN GRADE='A' THEN 
 DBMS_OUTPUT.PUT_LINE('YOUR GRADE IS Excellent'); 
END CASE; 
CASE  
 WHEN MARKS>80 THEN 
 DBMS_OUTPUT.PUT_LINE('YOUR MARKS IS ALSO Excellent'); 
END CASE; 
END; 
/

Output: 
YOUR GRADE IS Excellent
YOUR MARKS IS ALSO Excellent

Example 2

DECLARE 
GRADE CHAR(1) :='A'; 
MARKS NUMBER(2) := 85; 
BEGIN 
CASE 
 WHEN GRADE='A' THEN 
 DBMS_OUTPUT.PUT_LINE('YOUR GRADE IS Excellent'); 
 WHEN GRADE='B' THEN 
 DBMS_OUTPUT.PUT_LINE('YOUR GRADE IS GOOD'); 
END CASE; 
CASE  
 WHEN MARKS>80 THEN 
 DBMS_OUTPUT.PUT_LINE('YOUR MARKS IS ALSO Excellent'); 
END CASE; 
END; 
/

Output:
YOUR GRADE IS Excellent
YOUR MARKS IS ALSO Excellent

Example 3: Using CASE in UPDATE Statement SQL

UPDATE scott.emp set sal =
(
CASE  deptno

when 10 then sal*1.10

when 20 then sal *1.20

when 30 then sal*1.30

End

)

Example 4: Using CASE Statement in Select SQL

Example where we need to find employees with salary greater , less or equal to 3000. and mark as high , equal or less

select ENAME,SAL , CASE  SIGN(SAL -3000) 
             WHEN  1 THEN 'HIGH'
             WHEN  0 THEN 'EQUAL'
             WHEN -1 THEN 'LESS'
             END

FROM SCOTT.EMP

FAQs on CASE statement

Related post CASE vs Decode

oracle ebs