«Back to Home

Oracle Jump Start
Topics

How To Use Conditional Statement In Oracle

Description
 
There are three types of conditional statement, which are-
  • IF-THEN-ELSE statement
  • IF-THEN-ELSIF statement
  • CASE
IF-THEN-ELSE Statement
 
If then else statement is used, when a condition is true, the first statement is executed and second statement is skipped. When the condition is false the first statement is skipped and second statement is executed.
 
Syntax
  1. If condition then  
  2. statement 1;  
  3. statement 2;  
  4. else  
  5. statement 3;  
  6. statement 4;  
  7. End if;  
Example
  1. SET SERVEROUTPUT ON;  
  2. DECLARE  
  3. V_NUM NUMBER := &ENTER_A_NUMBER;  
  4. BEGIN  
  5. IF MOD(V_NUM, 2)=0  
  6. THEN  
  7. DBMS_OUTPUT.PUT_LINE(V_NUM||'This is even Number');  
  8. ELSE  
  9. DBMS_OUTPUT.PUT_LINE(V_NUM||'This is odd number');  
  10. END IF;  
  11. END;  
1

For odd number
2 

IF-THEN-ELSIF Statement
 
IF you have some situation, where you have several alternatives in the code…, if then statement can be used in the code.
If a condition for statement is true, it will run, else it goes to next, else if ….if all fail, it executes else statement.
 
Syntax
  1. IF condition 1 THEN  
  2. statement 1;  
  3. statement 2;  
  4. ELSIF condition 2 THEN  
  5. statement 3;  
  6. statement 4;  
  7. ELSIF condition 3 then  
  8. statement 5;  
  9. statement 6;  
  10. ………  
  11. ELSE  
  12. statement N;  
  13. END IF;  
Example
  1. SET SERVEROUTPUT ON;  
  2. DECLARE   
  3. V_color varchar2(20) := '&ENTER_A_color_name';  
  4. BEGIN   
  5. IF v_color ='red'   
  6. THEN   
  7. DBMS_OUTPUT.PUT_LINE('the sun is red');  
  8. ELSIF  
  9. v_color='blue'   
  10. THEN   
  11. DBMS_OUTPUT.PUT_LINE('the ocean is blue');  
  12. ELSIF  
  13. v_color='green'   
  14. THEN   
  15. DBMS_OUTPUT.PUT_LINE('the leaf is green');  
  16. ELSE   
  17. DBMS_OUTPUT.PUT_LINE('this is natural color');  
  18. END IF;   
  19. DBMS_OUTPUT.PUT_LINE('the world is very beautiful');  
  20. END;   
Enter a Red color

3 
Enter a Blue color
4 

CASE Statement
 
In Oracle, CASE statement started with the version ORACLE 9i. You can use the case statement within a SQL statement.
 
There are two types of case expression, which are-
  • Simple case expression
  • Searched case expression
Syntax
  1. CASE [Expression]  
  2. When condition 1 THEN Result 1  
  3. When condition 2 THEN Result 2  
  4. ……..  
  5. When condition n THEN Result n  
  6. ELSE Result  
  7. END  

Example
 
Simple case expression
  1. SELECT EMP_ID, EMP_NAME,    
  2. (    
  3. CASE EMP_ID    
  4. WHEN 7001 THEN 'PRESIDENT'    
  5. WHEN 7002 THEN 'CLERK'    
  6. WHEN 7003 THEN 'PRESIDENT'    
  7. WHEN 7004 THEN 'SALESMAN'    
  8. WHEN 7005 THEN 'SALESMAN'    
  9. WHEN 7006 THEN 'CLERK'    
  10. WHEN 7007 THEN 'CLERK'    
  11. ELSE 'SORRY'    
  12. END    
  13. )    
  14. FROM EMPLOYEE;    
5


Searched case expression
  1. SELECT  
  2. (  
  3. CASE 'SMITH'  
  4. WHEN 'KING' THEN 'FALSE'  
  5. WHEN 'SMITH' THEN '2 TRUE'  
  6. WHEN 'ALLEY' THEN '3 FALSE'  
  7. WHEN 'ANDERSON'THEN '4 TRUE'  
  8. ELSE 'SORRY'  
  9. END  
  10. )  
  11. FROM DUAL;  
6
 
7 

Summary
 
Thus, we learnt, that the conditional statements are of three types such as- IF-THEN-ELSE, IF-THEN-ELSIF, case. We learnt, how to use these conditional statements in Oracle with the example.