PL/SQL Case Statement

PL/SQL CASE statement comparing one by one sequencing conditions. CASE statement attempt to match expression that is specified in one or more WHEN condition. CASE statement are following two forms,

  1. Simple CASE Statement
  2. Searched CASE Statement

PL/SQL CASE Statement

Simple CASE Statement

PL/SQL simple CASE statement evaluates selector and attempt to match one or more WHEN condition.

Syntax

CASE selector
    WHEN value-1 
        THEN statement-1;
    WHEN value-2
        THEN statement-2;
    ELSE
        statement-3;
END CASE

Example

SQL>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.

Searched CASE Statement

PL/SQL searched CASE statement has not selector and attempt to match one or more WHEN clauses condition.

Syntax

Syntax

CASE
    WHEN condition-1 THEN 
        statement-1;
    WHEN condition-2 THEN 
        statement-2;
    ELSE
        statement-3;
END CASE;

Example

SQL>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