PL/SQL Case Statement

The PL/SQL CASE statement is helps you to execute a sequence of statement based on a selector. A CASE statement is start execution from top to bottom. Unlike the PL/SQL IF statement, PL/SQL CASE statement uses a selector instead of multiple IF statement to evaluate boolean value.

In PL/SQL you can use data type such as expression, variable and function that the CASE statement evaluates to a Boolean value but can not use BLOB, BFILE and composite types.

PL/SQL Case Statement Syntax

To execute a sequence of statement based on a selector, use the following syntax:

Syntax

CASE (expression)
WHEN condition1 THEN result1  
   WHEN condition2 THEN result2  
   ...  
   WHEN condition-n THEN result-n  
 ELSE result  
END

PL/SQL Case Statement example

The following PL/SQL uses CASE statement to execute a sequence of statements based on a selector:

Example

DECLARE  
   salary number(7) := 22000;  
BEGIN  
   CASE grade  
      WHEN salary > 15000 && <= 20000 THEN DBMS_OUTPUT.PUT_LINE('You fall under 10% income tax bracket');  
      WHEN salary > 20001 && <= 80000 THEN DBMS_OUTPUT.PUT_LINE('You fall under 20% income tax bracket');  
      WHEN salary > 80001 THEN DBMS_OUTPUT.PUT_LINE('You fall under 30% income tax bracket');   
      ELSE DBMS_OUTPUT.PUT_LINE('You fall under 0% income tax bracket');  
   END CASE;  
END;

« Previous