Oracle PL/SQL: CASE Statement with Examples

What is CASE Statement?

A CASE statement is similar to IF-THEN-ELSIF statement that selects one alternative based on the condition from the available options.

• CASE statement uses "selector" rather than a Boolean expression to choose the sequence.
• The value of the expression in the CASE statement will be treated as a selector.
• The expression could be of any type (arithmetic, variables, etc.)
• Each alternative is assigned with a certain pre-defined value (selector), and the alternative with selector value that matches the conditional expression value will get executed.
• Unlike IF-THEN-ELSIF, the CASE statement can also be used in SQL statements.
• ELSE block in CASE statement holds the sequence that needs to be executed when none of the alternatives got selected.

Syntax:

CASE (expression)
 WHEN <valuel> THEN action_blockl;
 WHEN <value2> THEN action_block2;
 WHEN <value3> THEN action_block3;
 ELSE action_block_default;
END CASE;

• In the above syntax, the expression will return a value that could be of any type (variable, number, etc.).
• Each 'WHEN' clause is treated as an alternatives which have <value> and <action_block>.
• The 'WHEN' clause which matches the value as that of the expression will be selected, and the corresponding <action_block> will be executed.
• 'ELSE' block is optional which hold the <action_block_default> that needs to be executed when none of the alternatives match the expression value.
• The 'END' marks the end of the CASE statement, and it is a mandatory part of the CASE.

Example 1: Arithmetic Calculation using Case

In this example, we are going to do arithmetic calculation between two numbers 55 and 5.

DECLARE
a NUMBER :=55;
b NUMBER :=5;
arth_operation VARCHAR2(20) :='MULTIPLY’;
BEGIN
dbms_output.put_line(‘Program started.' );
CASE (arth_operation)
WHEN ‘ADD’ THEN dbms_output.put_line(‘Addition of the numbers are: '|| a+b );
WHEN ‘SUBTRACT' THEN dbms_output.put_line(‘Subtraction of the numbers are: '||a-b ); 
WHEN ‘MULTIPLY' THEN dbms_output.put_line(‘Multiplication of the numbers are: '|| a*b
);
WHEN ‘DIVIDE' THEN dbms_output.put_line(‘Division of the numbers are:'|| a/b);
ELSE dbms_output.put_line(‘No operation action defined. Invalid operation');
END CASE;
dbms_output.put_line(‘Program completed.' );
END;
/

Code Explanation:

• Code line 2: Declaring the variable 'a' as 'NUMBER' data type and initializing it with value '55'.
• Code line 3: Declaring the variable 'b' as 'NUMBER' data type and initializing it with value '5.'
• Code line 4: Declaring the variable 'arth_operation' as 'VARCHAR2' data type of size 20 and initializing it with value 'MULTIPLY'.
• Code line 6: Printing the statement "Program started".
• Code line 7: CASE checks the value of the expression. In this case, the value of the variable 'arth_operation' is 'MULTIPLY'. This value will be treated as a selector for this CASE statement now.
• Code line 10: The WHEN clause with value 'MULTIPLY' matches with the selector value, hence controller will select this action_block and will print the message 'Multiplication of the numbers are: 275'.
• Code line13: Marks the end of the CASE statement.
• Code line14: Printing the statement "Program completed".

Code Output:

Program started.
Multiplication of the numbers are: 275
Program completed.

SEARCHED CASE Statement

The SEARCHED CASE statement is similar to the CASE statement, rather than using the selector to select the alternative, SEARCHED CASE will directly have the expression defined in the WHEN clause.

• The first WHEN clause that satisfies the condition will be executed, and the controller will skip the remaining alternatives.

Syntax:

CASE
WHEN <expression1> THEN action_blockl; 
WHEN <expression2> THEN action_block2; 
WHEN <expression3> THEN action_block3; 
ELSE action_block_default;
END CASE;

• In the above syntax, each WHEN clause has the separate <expression> and <action_block>.
• The WHEN clause for which the expression returns TRUE will be executed.
• 'ELSE' block is optional which hold the <action_block_default> that needs to be executed when none of the alternatives satisfies.
• The 'END' marks the end of the CASE statement and, it is a mandatory part of CASE.

Example 1: Arithmetic Calculation using Searched CaseIn this example, we are going to do arithmetic calculation between two numbers 55 and 5.

DECLARE a NUMBER :=55;
b NUMBER :=5;
arth_operation VARCHAR2(20) :='DIVIDE';
BEGIN
dbms_output.put_line(‘Program started.' );
CASE
WHEN arth_operation = 'ADD'
THEN dbms_output.put_line(‘Addition of the numbers are: '||a+b );
WHEN arth_operation = ‘SUBTRACT'
THEN dbms_output.put_line(‘Subtraction of the numbers are: '|| a-b);
WHEN arth_operation = ‘MULTIPLY’
THEN dbms_output.put_line(‘Multiplication of the numbers are: '|| a*b ); 
WHEN arth_operation = ’DIVIDE'
THEN dbms_output.put_line(‘Division of the numbers are: '|| a/b ):
ELSE dbms_output.put_line(‘No operation action defined. Invalid operation'); 
END CASE;
dbms_output.put_line(‘Program completed.' );
END;
/

Code Explanation:

• Code line 2: Declaring the variable 'a' as 'NUMBER' data type and initializing it with value '55'.
• Code line 3: Declaring the variable 'b' as 'NUMBER' data type and initializing it with value '5'.
• Code line 4: Declaring the variable 'arth_operation' as 'VARCHAR2' data type of size 20 and initializing it with value 'DIVIDE.'
• Code line 6: Printing the statement "Program started".
• Code line 7: SEARCHED CASE statement begins. The code from line8 to line 13 is skipped as their selector value (ADD, SUBTRACT, MULTIPLY) doesn't match with the value of 'arth_operation'.
• Code line 14: The WHEN clause expression "arth_operation = 'DIVIDE'" satisfied and the expression returns TRUE.
• Code line 15: Action_block of the WHEN clause will be executed, and message 'Division of the numbers are: 11' will be printed.
• Code line 17: Marks the end of CASE statement.
• Code line 18: Printing the statement "Program completed".

Code Output:

Program started.
Division of the numbers are: 11
Program completed.

Summary

TYPE	DESCRIPTION	USAGE
CASE	Similar to IF-THEN-ELSIF statement. A 'SELECTOR' is used to choose the alternatives instead of Boolean expression.	Used to select from several alternatives using 'SELECTOR'
SEARCHED CASE	CASE statement with no actual 'SELECTOR'. Instead, it contains the actual condition (which evaluates to TRUE/FALSE) that will select the alternatives.	Used to choose from more than two alternatives mostly.