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:

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 . In the case of condition evaluates to 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. 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:
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. 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:

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:

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 . In case of condition evaluates to 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. 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 , so the message “a is odd number” will be printed. Code line10: Printing the statement “Program completed”

Code Output:

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

In the above syntax, the control will execute the <action_block1> only if the condition1 returns . 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 . 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). 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 , 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 before condition3.

Code Output:
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’). 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:

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.

Syntax Explanation:

In the above syntax, the outer IF contains one more IF statement in its action block. The condition1 returns , then control will be executing <action_block1> and checks the condition2. If condition2 also returns , then <action_block2> will also be executed. In case of condition2 evaluates to 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.

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:

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.