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

What are Decision-Making Statements?

Decision-making statements decide the flow-control of SQL statements based on conditions. They give the programmer better control over preventing a particular block from executing, or choosing a desired block based on a condition. Below is a pictorial representation of the decision-making statement.

Types of decision-making statements: Oracle provides the following types.

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

This tutorial covers the IF variants. The CASE and searched CASE forms are covered in the PL/SQL CASE statement guide.

IF-THEN Statement

The IF-THEN statement is used to execute a particular section of code only when a condition is satisfied. The condition should yield a Boolean (True/False) value. It is a basic conditional statement that lets Oracle execute or skip a piece of code based on the pre-defined condition.

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, the keyword ‘IF’ is followed by a condition that evaluates to ‘TRUE’ or ‘FALSE’.
• The control executes the <action_block> only if the condition returns <TRUE>.
• If the condition evaluates to <FALSE>, SQL skips the <action_block> and starts executing the code after the ‘END IF’ block.

Note: Whenever a condition evaluates to ‘NULL’, SQL treats ‘NULL’ as ‘FALSE’.

Example 1: In this example, we print a message when the number is greater than 100.

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” is printed. If ‘a’ is less than or equal to 100, the condition fails and the printing statement is ignored.
• Code line 8: Printing the statement “Program completed”.

Code Output:

Program started.
Program completed.

Example 2: In this example, we print a message if a given letter is an English vowel (A, E, I, O, U).

DECLARE
a CHAR(1) := 'u';
BEGIN
IF UPPER(a) in ('A','E','I','O','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’ and initializing it with value ‘u’.
• Code line 4: Checking whether variable ‘a’ is present in the list (‘A’,’E’,’I’,’O’,’U’). The value of ‘a’ is converted to uppercase before comparing, to make the comparison case-insensitive.
• Code line 5: If ‘a’ is present in the list, “The character is in English Vowels” is printed. If the condition fails, this program gives no output, as there is no printing statement outside the IF-THEN block.

Code Output:

The character is in English Vowels

IF-THEN-ELSE Statement

The IF-THEN-ELSE statement is used to select between two alternatives based on a condition. Below is the syntax.

Syntax for IF-THEN-ELSE Statements:

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

• The keyword ‘IF’ is followed by a condition that evaluates to ‘TRUE’ or ‘FALSE’.
• The control executes <action_block1> only if the condition returns <TRUE>.
• If the condition evaluates to <FALSE>, SQL executes <action_block2>.
• In any case, one of the two action blocks is executed.

Note: Whenever a condition evaluates to ‘NULL’, SQL treats ‘NULL’ as ‘FALSE’.

Example 1: In this example, we print 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 number');
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 whether the modulus of variable ‘a’ by ‘2’ is 0.
• Code line 6: If ‘0’, then “a is even number” is printed.
• Code line 7: If the modulus value is not equal to ‘0’, the condition returns <FALSE>, so “a is odd number” is printed.
• Code line 10: 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 used where one alternative should be chosen from a set of alternatives, where each alternative has its own condition.
• The first condition that returns <TRUE> is executed, and the remaining conditions are skipped.
• The IF-THEN-ELSIF statement may contain an ‘ELSE’ block, executed if none of the conditions is satisfied.

Note: The ELSE block is optional. If there is no ELSE block and none of the conditions is satisfied, the controller skips all the action blocks and executes the remaining code.

Syntax for IF-THEN-ELSIF Statements:

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

• The control executes <action_block1> only if condition1 returns <TRUE>.
• If condition1 is not satisfied, the controller checks condition2, and so on.
• The controller exits the IF statement when it finds a condition that returns <TRUE> and runs the matching action block, or when none is satisfied and the ELSE block (if present) runs.

Note: Whenever a condition evaluates to ‘NULL’, SQL treats ‘NULL’ as ‘FALSE’.

Example 1: Without an ELSE block. In this example, we print the grade based on the given marks without an 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 condition1, whether ‘mark’ is greater than or equal to 70.
• Code line 7: Since condition1 failed, condition2 ’70 > mark >= 40′ is checked.
• Code line 8: Condition2 returns <TRUE>, so the message ‘Grade B’ is printed.
• Code line 12: Printing the statement “Program completed”. Condition3 ‘mark < 35’ is skipped, as a TRUE condition was already found.

Code Output:

Program started.
Grade B
Program completed.

Example 2: With an ELSE block. In this example, we print the grade based on the given marks with an 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 condition1, whether ‘mark’ is greater than or equal to 70.
• Code line 7: Since condition1 failed, condition2 ’70 > mark >= 40′ is checked.
• Code line 9: Since condition2 failed, condition3 ’40 > mark >= 35′ is checked.
• Code line 11: Since all conditions failed, control checks for the ELSE block and prints ‘No Grade’.
• Code line 14: Printing the statement “Program completed”.

Code Output:

Program started.
No Grade
Program completed.

NESTED-IF Statement

• The NESTED-IF statement allows programmers to place one or more ‘IF’ conditions inside another ‘IF’ condition’s <action_block>.
• Each ‘IF’ condition should have a separate ‘END IF’ statement, which marks the end of scope of that particular <action_block>.
• The ‘IF’ statement considers the nearest ‘END IF’ as the endpoint for that condition.
• The pictorial representation for NESTED-IF is shown below.

IF <condition1: 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_block1 ends>
END IF; --END IF corresponds to condition1

Syntax Explanation:

• The outer IF contains one more IF statement in its action block.
• If condition1 returns <TRUE>, control executes <action_block1> and checks condition2.
• If condition2 also returns <TRUE>, then <action_block2> is also executed.
• If condition2 evaluates to <FALSE>, SQL skips <action_block2>.

Example of Nested-IF Statement: Greatest of three numbers. In this example, we print the greatest of three numbers using a nested-if statement. The numbers are assigned in the declare part (Number = 10, 15, and 20), and the maximum is 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 1 */
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-4: Declaring the variables ‘a’, ‘b’, and ‘c’ as ‘NUMBER’ and initializing them with 10, 15, and 20.
• Code line 6: Printing the statement “Program started”.
• Code line 7: Checking condition1, whether ‘a’ is greater than ‘b’.
• Code line 10: If ‘a’ is greater than ‘b’, the nested-if 1 checks if ‘a’ is greater than ‘c’.
• Code line 11-13: If ‘a’ is still greater, ‘A is greatest’ is printed; otherwise ‘C is greatest’ is printed.
• Code line 18: If condition1 returns false, nested-if 2 checks if ‘b’ is greater than ‘c’.
• Code line 19-21: If ‘b’ is greater than ‘c’, ‘B is greatest’ is printed; otherwise ‘C is greatest’ is printed.
• Code line 24: Printing the statement “Program completed”.

Output of code:

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

Comparison of Decision-Making Statements

The table below summarises the various conditional statements discussed, so the right one can be chosen for a given situation.

FAQs