Introduction PL/SQL

Basic PL/SQL

Advance PL/SQL


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 Code

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;
/

Example 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

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

Example Code

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;
/

Example Result

value 3