Chapter 5:PL/SQL CASE Statement

PL/SQL CASE Statement

The PL/SQL CASE statement allows you to execute a sequence of statements based on a selector. A selector can be anything such as variable, function, or expression that the CASE statement evaluates to a Boolean value.

You can use almost any PL/SQL data types as a selector except BLOB, BFILE and composite types.

Unlike the PL/SQL IF statement, PL/SQL CASE statement uses a selector instead of using a combination of multiple Boolean expressions.

CASE statement are following two types,

1. Simple CASE Statement

Syntax:

CASE [ expression ]  
WHEN expression_1 THEN result_1  
   WHEN expression_2 THEN result_2  
   ...  
   WHEN expression_n THEN result_n  
 ELSE result  
END   

Example:

DECLARE
    a number := 7;
BEGIN
    CASE a  
        WHEN 1 THEN
            DBMS_OUTPUT.PUT_LINE('value 1');    
        WHEN 2 THEN
            DBMS_OUTPUT.PUT_LINE('value 2');    
        WHEN 3 THEN
            DBMS_OUTPUT.PUT_LINE('value 3');        
        ELSE
            DBMS_OUTPUT.PUT_LINE('no matching CASE found'); 
    END CASE;  
 END;
/

Result:

no matching CASE found

PL/SQL procedure successfully operation

2.Searched CASE Statement

syntax:

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

Example:

DECLARE
    a number := 3;
BEGIN
    CASE    
        WHEN a = 1 THEN
            DBMS_OUTPUT.PUT_LINE('value 1');    
        WHEN a = 2 THEN
            DBMS_OUTPUT.PUT_LINE('value 2');    
        WHEN a = 3 THEN
            DBMS_OUTPUT.PUT_LINE('value 3');        
        ELSE
            DBMS_OUTPUT.PUT_LINE('no matching CASE found'); 
    END CASE;  
 END;
/

Result:

value 3
PL/SQL procedure successfully operation