Summary of Oracle case usage

The following articles mainly introduce the actual usage of Oracle case. We all know that the case expression can be used in SQL to implement the actual application logic of the if-then-else type, PL/SQL does not have to be used. Oraclecase works in a similar way as DECODE (), but case should be used because it is ANSI compatible.

Case has two expressions:

1. Use a simple Oraclecase expression to determine the return value.

Syntax:

 
 

  
  
case search_expression  
  
  
WHEN expression1 THEN result1  
  
  
WHEN expression2 THEN result2  
  
  
...  
  
  
WHEN expressionN THEN resultN  
  
  
ELSE default_result  
  
  
END  
 
 

Example:

 
 

  
  
select product_id,product_type_id,  
  
  
case product_type_id  
  
  
when 1 then 'Book'  
  
  
when 2 then 'Video'  
  
  
when 3 then 'DVD'  
  
  
when 4 then 'CD'  
  
  
else 'Magazine'  
  
  
end  
  
  
from products  
 
 

Result:

PRODUCT_ID PRODUCT_TYPE_ID OraclecasePROD

---------------------------------

1 Book

2 1 Book

3 2 Video

4 2 Video

5 2 Video

6 2 Video

7 3 DVDs

8 3 DVDs

9 4 CD

10 4 CD

11 4 CD

12 Magazine

12 rows selected.

2. Search for the case expression and use the condition to determine the return value.

Syntax:

 
 

  
  
case  
  
  
WHEN condition1 THEN result1  
  
  
WHEN condistion2 THEN result2  
  
  
...  
  
  
WHEN condistionN THEN resultN  
  
  
ELSE default_result  
  
  
END  
 
 

Example:

 
 

  
  
select product_id,product_type_id,  
  
  
Oraclecase  
  
  
when product_type_id=1 then 'Book'  
  
  
when product_type_id=2 then 'Video'  
  
  
when product_type_id=3 then 'DVD'  
  
  
when product_type_id=4 then 'CD'  
  
  
else 'Magazine'  
  
  
end  
  
  
from products  
 
 

The result is the same as above.