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

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

Types of Decision Making Statements:

Oracle provides the following types of decision making statements.

• IF-THEN
• IF-THEN-ELSE
• IF-THEN-ELSIF
• NESTED-IF
• CASE
• SEARCHED CASE

In this tutorial, you will learn-

• Introduction to Decision Making Statements
• IF-THEN Statement
• IF-THEN-ELSE Statement
• IF-THEN-ELSIF Statement
• NESTED-IF Statement

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;

• In the above syntax, keyword 'IF' will be followed by a condition which evaluates to 'TRUE'/'FALSE'.
• The control will execute the <action_block> only if the condition returns <TRUE>.
• In the case of condition evaluates to <FALSE> then, SQL will skip the <action_block>, and it will start executing the code next to 'END IF' block.

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 line 2: Declaring the variable 'a' as 'NUMBER' data type and initializing it with value '10'.
• Code line 4: Printing the statement "Program started".
• Code line 5: Checking the condition, whether variable 'a' is greater than '100.'
• Code line 6: If 'a' is greater than '100', then "a is greater than 100" will be printed. If 'a' is lesser than or equal to 100, then condition fails, so the above printing statement ignored.
• Code line 8: Printing the statement "Program completed".

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 line 2: Declaring the variable 'a' as 'CHAR' of size '1' data type and initializing it with value 'u'.
• Code line 4: Checking the condition, whether variable 'a' is present in the list ('A','E','I','O','U').
• Value of 'a' has been converted to uppercase before comparing to make the comparison is case-insensitive.
• Code line 5: If 'a' is present in the list, then the statement "The character is in English Vowels" will be printed. If condition failed, then this program will not give any output, as outside the IF-THEN block we have not issued any printing statement.

Code Output:

The character is in English Vowels

IF-THEN-ELSE Statement

• The IF-THEN-ELSE statement is mainly used to select between two alternatives based on the condition.
• Below is the syntax representation of 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;

• In the above syntax, keyword 'IF' will be followed by a condition which evaluates to 'TRUE'/'FALSE'.
• The control will execute the <action_block1> only if the condition returns <TRUE>.
• In case of condition evaluates to <FALSE> then, SQL will execute <action_block2>.
• In any case, one of the two action blocks will be executed.

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 line 2: Declaring the variable 'a' as 'NUMBER' data type and initializing it with value '11'.
• Code line 4: Printing the statement "Program started".
• Code line 5: Checking the condition, whether modulus of variable 'a' by '2' is 0.
• Code line 6: If '0', then "a is even number" will be printed.
• Code line 7: If the modulus value is not equal to '0', then the condition returns <FALSE>, so the message "a is odd number" will be printed.
• Code line10: Printing the statement "Program completed"

Code Output:

Program started.
a is odd number
Program completed.   

IF-THEN-ELSIF Statement

• The IF-THEN-ELSIF statement is mainly used where one alternative should be chosen from a set of alternatives, where each alternative has its own conditions to be satisfied.
• The first conditions that return <TRUE> will be executed, and the remaining conditions will be skipped.
• The IF-THEN-ELSIF statement may contain 'ELSE' block in it. This 'ELSE' block will be executed if none of the conditions is satisfied.

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;

• In the above syntax, the control will execute the <action_block1> only if the condition1 returns <TRUE>.
• If condition1 is not satisfied, then the controller will check for condition2.
• The controller will exit from the IF-statement in the following two cases.
  • When the controller found any condition that returns <TRUE>. In this case, the corresponding action_block will be executed and the controller will exit this IF-statement block and will start executing the remaining code.
  • When none of the conditions satisfied, the then controller will execute ELSE block if present, then will exit from the IF-statement.

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 line 2: Declaring the variable 'mark' as 'NUMBER' data type and initializing it with value '55'.
• Code line 4: Printing the statement "Program started".
• Code line 5: Checking the condition1, whether 'mark' is greater or equal 70.
• Code line 7: Since condition1 failed, then the condition2 '70>mark>=40' is checked.
• Code line 8: The condtition2 returns <TRUE>, hence the message 'Grade B' will be printed.
• Code line12: Printing the statement "Program completed".
• In this case, the condition3 'mark < 35' will be skipped, as the controller found one condition which returns <TRUE> before condition3.

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 line 2: Declaring the variable 'mark' as 'NUMBER' data type and initializing it with value '25'.
• Code line 4: Printing the statement "Program started".
• Code line 5: Checking the condition 1, whether 'mark' is greater or equal 70.
• Code line 7: Since condition1 failed, then the condition2 '70>mark>=40' is checked.
• Code line 8: Since condition2 failed, then the condition3 '40>mark>=35' is checked.
• Code line 11: Since all the conditions are failed, control will now check for the presence of ELSE block, and it will print the message 'No Grade' from ELSE block.
• Code line14: Printing the statement "Program completed".

Code Output:

Program started.
No Grade 
Program completed.

NESTED-IF Statement

• The NESTED-IF statement is basically allowed programmers to place one or more 'IF' condition inside another 'IF' condition's <action_block> other than normal statements.
• Each 'IF' condition should have a separate 'END IF' statement which marks the end-of-scope of that particular <action_block>.
• The 'IF' statement will consider the nearest 'END IF' statement as an endpoint for that particular condition.
• The pictorial representation for NESTED-IF is shown below diagram.

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:

• In the above syntax, the outer IF contains one more IF statement in its action block.
• The condition1 returns <TRUE>, then control will be executing <action_block1> and checks the condition2.
• If condition2 also returns <TRUE>, then <action_block2> will also be executed.
• In case of condition2 evaluates to <FALSE> then, SQL will skip the <action_block2>.

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:

• Code line 2: Declaring the variable 'a' as 'NUMBER' data type and initializing it with value '10'.
• Code line 3: Declaring the variable 'b' as 'NUMBER' data type and initializing it with value '15'.
• Code line 4: Declaring the variable 'c' as 'NUMBER' data type and initializing it with value '20'.
• Code line 6: Printing the statement "Program started" (line 6).
• Code line 7: Checking the condition1, whether 'a' is greater than 'b' (line 7).
• Code line 10: If 'a' is greater than 'b, then condition in 'nested-if 1' will check if 'a' is greater than 'c'(line 10).
• Code line 13: If still 'a' is greater, then message 'A is greatest' will be printed (line 11). Else if condition2 fails, then 'C is greatest' will be printed (line 13).
• Code line 18: In case condition1 returns false, then condition in 'nested-if 2' will check if 'b' is greater than 'c'(line 18).
• Code line 21: If 'b' is greater than 'c' , then message 'B is greatest' will be printed (line 19), else if condition2 fails, then 'C is greatest' will be printed (line 21).
• Code line 24: Printing the statement "Program completed" (line 24).

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.