Oracle PL/SQL IF THEN ELSE Statement: ELSIF, NESTED-IF

Details

What are Decision-Making Statements?

Decision making statements are those who will decide the flow-control of SQL statements based on the conditions. It gives the programmer a better control of preventing a particular code from executing (diagram 1) or choosing a desired code based on the condition (diagram 2). Below is the pictorial representation of the "Decision Making Statement".

Decision Making Statement Diagram

Decision Making Statement Diagram

Types of Decision Making Statements:

Oracle provides the following types of decision making statements.

In this tutorial, you will learn-

IF-THEN Statement

The IF-THEN statement is mainly used to execute a particular section of codes only when the condition is satisfied.

The condition should yield Boolean (True/False). It is a basic conditional statement which will allow the ORACLE to execute/skip a particular piece of code based on the pre-defined conditions.

Syntax for IF THEN Statements: 

IF <condition: returns Boolean>
THEN
 -executed only if the condition returns TRUE 
 <action_block>
END if;

Note: Whenever condition evaluated to 'NULL', then SQL will treat 'NULL' as 'FALSE'.

Example 1: In this example, we are going to print a message when the number is greater than 100. For that, we will execute the following code

To print a message when a number has value more than 100, we execute the following code. 

DECLARE 
a NUMBER :=10;
BEGIN
dbms_output.put_line(‘Program started.' );
IF( a > 100 ) THEN
dbms_output.put_line('a is greater than 100'); 
END IF;
dbms_output.put_line(‘Program completed.');
END;
/

Code Explanation:

Code Output: 

Program started.
Program completed.

Example 2: In this example, we are going to print a message if a given alphabet is present in English vowels (A, E, I, O, U).

To print a message when the given character is Vowel, we execute the following code. 

DECLARE 
a CHAR(1) :=’u’;
BEGIN
IF UPPER(a) in ('A’,'E','I','0','U' ) THEN 
dbms_output.put_line(‘The character is in English Vowels');
END IF;
END;
/

Code Explanation:

Code Output: 

The character is in English Vowels

IF-THEN-ELSE Statement

Syntax for IF-THEN-ELSE Statements: 

IF <condition: returns Boolean>
THEN
	-executed only if the condition returns TRUE
	<action_blockl>
ELSE
	-execute if the condition failed (returns FALSE)
	<action_block2>
END if;

Note: Whenever condition evaluates to 'NULL', then SQL will treat 'NULL' as 'FALSE'.

Example 1: In this example, we are going to print message whether the given number is odd or even. 

DECLARE 
a NUMBER:=11;
BEGIN
dbms_output.put_line (‘Program started');
IF( mod(a,2)=0) THEN 
dbms_output.put_line('a is even number' ); 
ELSE
dbms_output.put_line('a is odd number1); 
END IF;
dbms_output.put_line (‘Program completed.’);
END;
/

Code Explanation:

Code Output: 

Program started.
a is odd number
Program completed.

IF-THEN-ELSIF Statement

Note: ELSE block is optional in this conditional statement. If there is no ELSE block, and none of the condition satisfied, then the controller will skip all the action block and start executing the remaining part of the code.

Syntax for IF-THEN-ELSIF Statements: 

IF <conditionl: returns Boolean>
THEN
-executed only if the condition returns TRUE <
action_blockl>
ELSIF <condition2 returns Boolean> <
action_block2>
ELSIF <condition3:returns Boolean> <
action_block3>
ELSE —optional 
<action_block_else>
END if;

Note: Whenever condition evaluates to 'NULL', then SQL will treat 'NULL' as 'FALSE'.

Example 1: Without ELSE block

In this example, we are going to print the grade based on the given marks without else condition (mark >= 70 Grade A, mark >=40 and mark<70 Grade B, mark >=35 and mark<40 Grade C). 

DECLARE
mark NUMBER :=55;
BEGIN
dbms_output.put_line(‘Program started.’ );
IF( mark >= 70) THEN 
dbms_output.put_line(‘Grade A’);
ELSIF(mark >= 40 AND mark < 70) THEN
dbms_output.put_line(‘Grade B'); 
ELSIF(mark >=35 AND mark < 40) THEN
dbms_output.put_line(‘Grade C’);
END IF;
dbms_output.put_line(‘Program completed.’); 
END;
/

Code Explanation:

Code Output: 

Program started.
Grade B
Program completed.

Example 2: With ELSE block

In this example, we are going to print the grade based on the given marks with else condition (mark >= 70 Grade A, mark >=40 and mark<70 Grade B, mark >=35 and mark<40 Grade C, else 'No Grade'). 

DECLARE
mark NUMBER :=25;
BEGIN
dbms_output.put_line(‘Program started.’ );
IF( mark >= 70) THEN 
dbms_output.put_line(‘Grade A’); 
ELSIF(mark >= 40 AND mark < 70) THEN 
dbms_output.put_line(‘Grade B'); 
ELSIF(mark >=35 AND mark < 40) THEN 
dbms_output.put_line(‘Grade C);
ELSE
dbms_output.put_line(‘No Grade’);
END IF;
dbms_output.put_line(‘Program completed.' ); 
END;
/

Code Explanation:

Code Output: 

Program started.
No Grade 
Program completed.

NESTED-IF Statement

PL/SQL Decision Making Statements

PL/SQL Decision Making Statements 

IF <conditionl: returns Boolean>
THEN
	—executed only if the condition returns TRUE
	<action block1 starts>
	IF <condition2: returns Boolean>
	THEN
	<action_block2>
	END IF; —END IF corresponds to condition2
<action_blockl ends>
END IF; —END IF corresponds to condition1
Syntax Explanation:

Here we are going to see an example of Nested If –

Example of Nested- If Statement: Greatest of three number

In this example, we are going to print the greatest of three numbers by using Nested-If statement. The numbers will be assigned in the declare part, as you can see in the code below, i.e Number= 10,15 and 20 and the maximum number will be fetched using nested-if statements. 

DECLARE 
a NUMBER :=10; 
b NUMBER :=15; 
c NUMBER :=20;
BEGIN
dbms_output.put_line(‘Program started.' );
IF( a > b)THEN
/*Nested-if l */
	dbms_output.put_line(’Checking Nested-IF 1'); 
	IF( a > c ) THEN
	dbms_output.put_line(‘A is greatest’); 
	ELSE
	dbms_output.put_line(‘C is greatest’); 
	END IF;
ELSE
/*Nested-if2 */
	dbms_output.put_line('Checking Nested-IF 2' ); 
	IF( b > c ) THEN
	dbms_output.put_line(’B is greatest' ); 
	ELSE
	dbms_output.put_line(’C is greatest' ); 
	END IF;
END IF;
dbms_output.put_line(‘Program completed.’ );
END;
/

Code Explanation:

Output of code: 

Program started.
Checking Nested-IF 2
C is greatest
Program completed.

Summary

In this chapter, we have learned the different decision-making statements and their syntax and examples. Below table gives the summary of various conditional statements that we have discussed.

TYPE

DESCRIPTION

USAGE

IF-THEN

Checks for a Boolean condition, if TRUE code in 'THEN' block will be executed.

To skip,/execute a particular code based on the condition.

IF-THEN-ELSE

Checks for a Boolean condition, if TRUE code in 'THEN' block will be executed, if false code in 'ELSE' block is executed.

Most appropriate in 'THIS-OR-THAT' condition.

IF-THEN-ELSIF

Checks for a Boolean condition in sequential order. The first block in the sequence which returns TRUE condition will be executed. If none of the conditions in the sequence is TRUE, then code in 'ELSE' block is executed.

Used to choose from more than two alternatives mostly.

NESTED-IF

Allows one or more IF-THEN or IF-THEN-ELSIF statement inside another IF-THEN or IF-THEN-ELSIF statement(s).

Mainly used in nested condition situation.

 

YOU MIGHT LIKE: