• Home
  • Testing
  • SAP
  • Web
  • Must Learn!
  • Big Data
  • Live Projects
  • Blog

Decision making statements are important to write any complex programs. These statements will decide the control flow at run time based on pre-defined conditions.








In this tutorial, you will learn-

Introduction to Decision Making Statements

Decision making statements are those which will decide the flow-control of SQL statements based on the conditions. It gives the programmer a better control like 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".

PL/SQL Decision Making Statements

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

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.

PL/SQL Decision Making Statements Syntax Explanation:

  • 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 evaluate 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.

PL/SQL Decision Making Statements

PL/SQL Decision Making Statements

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.

PL/SQL Decision Making Statements

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 as 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.

PL/SQL Decision Making Statements Syntax Explanation:

  • 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 block will be executed.

Note: Whenever condition evaluate 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.

PL/SQL Decision Making Statements

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 condition to be satisfied.
  • The first condition that returns <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 condition 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.

PL/SQL Decision Making Statements Syntax Explanation:

  • 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 condition 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).

PL/SQL Decision Making Statements

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').

PL/SQL Decision Making Statements

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 allows 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.

PL/SQL Decision Making Statements

PL/SQL Decision Making Statements 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.

PL/SQL Decision Making Statements

PL/SQL Decision Making Statements

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.

CASE Statement

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.

PL/SQL Decision Making Statements Syntax Explanation:

  • In the above syntax, the expression will return a value that could be of any type (variable, number, etc.).
  • Each 'WHEN' clause is treated as an alternatives which have <value> and <action_block>.
  • The 'WHEN' clause which matches the value as that of the expression will be selected, and the corresponding <action_block> will be executed.
  • 'ELSE' block is optional which hold the <action_block_default> that needs to be executed when none of the alternatives match the expression value.
  • The 'END' marks the end of CASE statement and it is a mandatory part of CASE.

Example 1: Arithmetic Calculation using Case

In this example, we are going to do arithmetic calculation between two numbers 55 and 5.

PL/SQL Decision Making Statements

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 CASE statement.
  • Code line14: Printing the statement "Program completed".

Code Output:

Program started.

Multiplication of the numbers are: 275

Program completed.

SEARCHED CASE Statement

SEARCHED CASE statement is similar to 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.

PL/SQL Decision Making Statements Syntax Explanation:

  • In the above syntax, each WHEN clause has the separate <expression> and <action_block>.
  • The WHEN clause for which the expression returns TRUE will be executed.
  • 'ELSE' block is optional which hold the <action_block_default> that needs to be executed when none of the alternatives satisfies.
  • The 'END' marks the end of CASE statement and it is a mandatory part of CASE.

Example 1: Arithmatic Calculation using Searched Case

In this example, we are going to do arithmetic calculation between two numbers 55 and 5.

PL/SQL Decision Making Statements

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

In this chapter, we have learnt 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 a sequential order. The first block in the sequence which returns TRUE condition will be executed. If none of the conditions in sequence is TRUE, then code in 'ELSE' block is executed

Used to choose from more than 2 alternative 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.

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 2 alternative mostly.

 

YOU MIGHT LIKE:
PL-SQL

Packages in PL/SQL

PL/SQL package is nothing but a logical grouping of a related subprogram (procedure/function) into a...