Home » PL/SQL

Case Statements in PL/SQL

PL/SQL - Case statements in PL/SQL: In this article, we will learn about case statement (similar as switch case statements in C/C++) with examples.
Submitted by Yash Kumar, on October 03, 2017

The PL/SQL case statement is used to execute sequence of statements based on given selector. In PL/SQL selector can be anything like such as function, variable and expression.

Here, selector's value matches with the case values, if matched then statements associated with that case evaluates otherwise else block evaluates.

Reference: http://www.plsqltutorial.com/plsql-case-statement/

Syntax

case <expression>
when condition1  then result1;
when condition2  then result2;
when condition3  then result3;
....................number of statements;
else result;
end case;

Example

declare
per integer;
begin
per:=38;       
case 
	when (per>=60)AND(per<=100) then 
		dbms_output.put_line('first divison');
	when (per>=48)AND(per<60) then 
		dbms_output.put_line('second divison');
	when (per>=33)AND(per<48) 
		then dbms_output.put_line('third divison');
	when (per>=0)AND(per<33) 
		then dbms_output.put_line('failed');
else 
	dbms_output.put_line('invalid percentage');
end case;
end;

Output

third divison

Statement processed.

0.00 seconds

Let's see how it works?

Firstly per(for percentage ) is initialized with 38. When control is in the case block it checks all the conditions. When any condition becomes true it prints the result of respective condition. After that the control further does not check remaining conditions.