PL/SQL Case Statement

The PL/SQL CASE statement facilitates you to execute a sequence of satatements based on a selector. A selector can be anything such as variable, function or an expression that the CASE statement checks to a boolean value.

The CASE statement works like the IF statement, only using the keyword WHEN. A CASE statement is evaluated from top to bottom. If it get the condition TRUE, then the corresponding THEN calause is executed and the execution goes to the END CASE clause.

Syntax for the CASE Statement:

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

Example of PL/SQL case statement

Let's take an example to make it clear:

DECLARE
   grade char(1) := 'A';
BEGIN
   CASE grade
      when 'A' then dbms_output.put_line('Excellent');
      when 'B' then dbms_output.put_line('Very good');
      when 'C' then dbms_output.put_line('Good');
      when 'D' then dbms_output.put_line('Average');
      when 'F' then dbms_output.put_line('Passed with Grace');
      else dbms_output.put_line('Failed');
   END CASE;
END;

After the execution of above code, you will get the following result:

Excellent
PL/SQL procedure successfully completed.

Next TopicPL/SQL Loop

← prev next →

PL/SQL Tutorial

Control Statements

PL/SQL Procedure

PL/SQL Function

PL/SQL Cursor

PL/SQL Exception

PL/SQL Trigger

Interview Questions

Quiz

PL/SQL Case Statement

Example of PL/SQL case statement