Trending September 2023 # Case Statement In Oracle Pl/Sql With Examples # Suggested October 2023 # Top 12 Popular

You are reading the article Case Statement In Oracle Pl/Sql With Examples updated in September 2023 on the website Nhunghuounewzealand.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Case Statement In Oracle Pl/Sql 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) 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.).

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) ); 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 ELSE action_block_default; END CASE;

The WHEN clause for which the expression returns TRUE will be executed.

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.

DECLARE a NUMBER :=55; b NUMBER :=5; arth_operation VARCHAR2(20) :='DIVIDE'; BEGIN dbms_output.put_line(‘Program started.' ); CASE WHEN arth_operation = 'ADD' WHEN arth_operation = ‘SUBTRACT' WHEN arth_operation = ‘MULTIPLY’ WHEN arth_operation = ’DIVIDE' 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.

You're reading Case Statement In Oracle Pl/Sql With Examples

Update the detailed information about Case Statement In Oracle Pl/Sql With Examples on the Nhunghuounewzealand.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!