CASE Statement in Oracle PL/SQL with Examples

What is a CASE Statement?

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

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

Syntax

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

• In the above syntax, the expression returns a value that could be of any type.
• Each ‘WHEN’ clause is an alternative that has a <value> and an <action_block>.
• The ‘WHEN’ clause whose value matches the expression is selected, and its <action_block> is executed.
• The ‘ELSE’ block is optional and holds the <action_block_default> to execute when no alternative matches.
• ‘END’ marks the end of the CASE statement and is a mandatory part of CASE.

Example 1: Arithmetic Calculation Using CASE

In this example, we do an 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’ and initializing it with value ’55’.
• Code line 3: Declaring the variable ‘b’ as ‘NUMBER’ and initializing it with value ‘5’.
• Code line 4: Declaring the variable ‘arth_operation’ as ‘VARCHAR2’ 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. Here, the value of ‘arth_operation’ is ‘MULTIPLY’, which is treated as the selector.
• Code line 10: The WHEN clause with value ‘MULTIPLY’ matches the selector, so the controller selects this action block and prints ‘Multiplication of the numbers are: 275’.
• Code line 13: Marks the end of the CASE statement.
• Code line 14: 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, but rather than using a selector to choose the alternative, the searched CASE has the condition defined directly in the WHEN clause.

• The first WHEN clause that satisfies the condition is executed, and the controller skips the remaining alternatives.

Syntax

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

• In the above syntax, each WHEN clause has a separate <expression> and <action_block>.
• The WHEN clause for which the expression returns TRUE is executed.
• The ‘ELSE’ block is optional and holds the <action_block_default> to execute when none of the alternatives is satisfied.
• ‘END’ marks the end of the CASE statement and is a mandatory part of CASE.

Example 1: Arithmetic Calculation Using Searched CASE

In this example, we do an 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’ and initializing it with value ’55’.
• Code line 3: Declaring the variable ‘b’ as ‘NUMBER’ and initializing it with value ‘5’.
• Code line 4: Declaring the variable ‘arth_operation’ as ‘VARCHAR2’ of size 20 and initializing it with value ‘DIVIDE’.
• Code line 6: Printing the statement “Program started”.
• Code line 7: The searched CASE statement begins. The clauses for ADD, SUBTRACT, and MULTIPLY are skipped, as their conditions do not match the value of ‘arth_operation’.
• Code line 14: The WHEN clause expression “arth_operation = ‘DIVIDE'” is satisfied and returns TRUE.
• Code line 15: The action block of the WHEN clause is executed, and ‘Division of the numbers are: 11’ is printed.
• Code line 17: Marks the end of the CASE statement.
• Code line 18: Printing the statement “Program completed”.

Code Output

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

Comparison of CASE and SEARCHED CASE

Both forms choose one branch, but they differ in how the branch is selected, as summarised below.

FAQs