PL/SQL Case

In Oracle PL/SQL, the CASE statement is used to perform different actions based on the value of an expression. The CASE statement can be used in two forms: simple and searched.

The simple form of the CASE statement has the following syntax:

CASE syntax

CASE 
	WHEN operator {content_operator THEN {statement;} ... ...} 
	[ELSE {statement;}...] 
END CASE ;

The expression is evaluated, and the corresponding statement is executed based on the value of the expression. If the value of the expression matches one of the values specified in the WHEN clauses, the corresponding statement is executed. If the value of the expression does not match any of the values specified in the WHEN clauses, the statement following the ELSE clause is executed.

CASE example

For example, the following code uses a simple CASE statement to print a message based on the value of a variable:

DECLARE 
   var CHAR(100); 
BEGIN 
   var := 'Y'; 
   CASE var 
     WHEN 'X' THEN var:='1'; 
     WHEN 'Y' THEN var:='2'; 
     WHEN 'Z' THEN var:='3';
     ELSE var:='No data found'; 
   END CASE; 
   DBMS_OUTPUT.PUT_LINE('The output is: '||var);
END;

The output is: 2  

CASE example

DECLARE
	gen      students.gender%TYPE;
	stu      students.student_id%TYPE := 3;
	message  VARCHAR2(250);
BEGIN
	SELECT gender INTO gen from students WHERE student_id = stu;
	CASE
		WHEN gen = 'M' THEN message := 'MALE';
		WHEN gen = 'F' THEN message := 'FEMALE';
		ELSE message := 'Empty column';
	END CASE;
	DBMS_OUTPUT.PUT_LINE('Message: '||message);  
END;

Message: FEMALE