CASE

The CASE statement in Oracle PL/SQL is a control flow statement that allows you to conditionally execute one or more statements based on a specified condition or expression. The basic syntax for a CASE statement is as follows:

CASE 
WHEN condition1 THEN result1 
WHEN condition2 THEN result2 
... 
ELSE result_else 
END CASE;

In the above syntax, the conditions are evaluated in the order they are specified. When a condition evaluates to TRUE, the corresponding result is executed and the CASE statement is exited. If none of the conditions evaluate to TRUE, then the ELSE result is executed.

CASE can also be used in SELECT statement as an expression.

SELECT column1, column2, 
CASE column3 
WHEN 'value1' THEN 'result1' 
WHEN 'value2' THEN 'result2' 
ELSE 'result_else' 
END as column4 
FROM table_name;

CASE statement can also be used in SQL statement as well.

SELECT ename, job, 
CASE job 
WHEN 'MANAGER' THEN 'MANAGERIAL' 
WHEN 'SALESMAN' THEN 'SALES' 
ELSE 'OTHER' 
END "JOB CATEGORY" 
FROM emp;

CASE example

  
DECLARE 
	v_num NUMBER:=5;
	v_out VARCHAR2(12);
BEGIN
	CASE v_num
		WHEN 1 THEN v_out:='A'; 
		WHEN 5 THEN v_out:='B';
		WHEN 8 THEN v_out:='C';
		ELSE v_out:='D';
	END CASE;
	DBMS_OUTPUT.put_line(v_out);
END;

Output:

B

CASE statement can be a powerful tool for adding logic and complexity to your PL/SQL programs and SQL statements.