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:

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 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.

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:

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:

In the above syntax, each WHEN clause has the separate 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 Case In this example, we are going to do arithmetic calculation between two numbers 55 and 5.

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:

Summary