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

In this chapter, we are going to see the loop concept in PL/SQL and flow of control in loops. You will learn-









Introduction to Loops Concept

Loops concept basically allows a certain part of the code in a program to get executed for the desired number of types.

Loops concept provides the following advantage in coding

  • Reusability of code
  • Reduced code size
  • Easy flow of control
  • Reduced Complexity

The below diagram shows the looping concept in a pictorial manner

Loops in PL/SQL

In the above diagram, the loop condition will be checked, and as long as the loop condition is satisfied, the execution block will be executed.

In each iteration, the loop counter variable that actually decides the loop condition should modify to make the control exit from the loop. In some cases, this loop counter variable is increment/decrement operator for a predefined count and in some case it is a search condition that keep on executing the block till it satisfies it.

Loop Control Statements

Before learning the loops concept, it is mandatory to learn about loop control statements. Loop control statements are those that actually controls the flow of execution inside the loop. Below is the detailed description about the loop control statements.

CONTINUE

This keyword sends an instruction to the PL/SQL engine that whenever PL/SQL engine encounters this keyword inside the loop, then it will skip the remaining code in the execution block of the code, and next iteration will start immediately. This will be mainly used if the code inside the loop wants to be skipped for certain iteration values.

EXIT / EXIT WHEN

This keyword sends an instruction to the PL/SQL engine that whenever PL/SQL engine encounters this keyword, then it will immediately exit from the current loop. If the PL/SQL engine encounters the EXIT in nested loops, then it will come out of the loop in which it has been defined, i.e. in a nested loops, giving EXIT in the inner loop will only exit the control from inner loop but not from the outer loop. 'EXIT WHEN' is followed by an expression which gives Boolean result. If the result is TRUE, then the control will EXIT.

GOTO

This statement will transfer the control to the labeled statement ("GOTO <label> ;"). This has the following restrictions

  • Transfer of control can be done only within the subprograms.
  • Transfer of control cannot be done from exception handling part to the execution part

Usage of this statement is not recommended unless there are no other alternatives, as the code-control traceability will be very difficult in the program due to the transfer of control from one part to another part.

Types of Loop in PL/SQL

PL/SQL provides following three types of loops

  • Basic loop statement
  • For loop statement
  • While loop statement

Basic Loop Statement

This loop statement is the simplest loop structure in PL/SQL. The execution block starts with keyword 'LOOP' and ends with the keyword 'END LOOP'.

The exit condition should be given inside this execution block so that control exit from the loop.

It needs EXIT keyword to be given explicitly in the execution part to exit from the loop.

Loops in PL/SQL Syntax Explanation:

  • In the above syntax, key word 'LOOP' marks beginning of the loop and 'END LOOP' marks the end of the loop.
  • The execution block contains all the code that needs to be executed including the EXIT condition.
  • The execution part can contain any execution statement.

Note: Basic loop statement with no EXIT keyword will be an INFINITE-LOOP that will never stop.

Example 1: In this example, we are going to print number from 1 to 5 using basic loop statement. For that, we will execute the following code

Loops in PL/SQL

Code Explanation:

  • Code line 2: Declaring the variable 'a' as 'NUMBER' data type and initializing it with value '1'.
  • Code line 4: Printing the statement "Program started".
  • Code line 5: Keyword 'LOOP' marks the beginning of the loop.
  • Code line 6: Prints the value of 'a'.
  • Code line 7: Increments the value of 'a' by +1.
  • Code line 8: Checks whether the value of 'a' is greater than 5.
  • Code line 9: Keyword 'END LOOP' marks the end of execution block.
  • The code from line 6 to line 8 will continue to execute till 'a' reaches the value 6, as the condition will return TRUE, and the control will EXIT from the loop.
  • Code line 10: Printing the statement "Program completed"

WHILE Loop Statement

WHILE loop statement works similar to the Basic loop statement except the EXIT condition is at the very beginning of the loop.

It works like entry-check loop in which execution block will not even be executed once if the condition is not satisfied, as the exit condition is checking before execution part. It does not require keyword 'EXIT' explicitly to exit from the loop since it is validating the condition implicitly each time of the loop.

Loops in PL/SQL Syntax Explanation:

  • In the above syntax, keyword 'WHILE' marks beginning of the loop and 'END LOOP' marks the end of the loop.
  • EXIT condition is evaluated each time before the execution part is starts executing.
  • The execution block contains all the code that needs to be executed.
  • The execution part can contain any execution statement.

Example 1: In this example, we are going to print number from 1 to 5 using WHILE loop statement. For that, we will execute the following code

Loops in PL/SQL

Code Explanation:

  • Code line 2: Declaring the variable 'a' as 'NUMBER' data type and initializing it with value '1'.
  • Code line 4: Printing the statement "Program started".
  • Code line 5: Keyword 'WHILE' marks the beginning of the loop, and it also checks whether value of 'a' is greater than 5
  • Code line 7: Prints the value of 'a'.
  • Code line 8: Increments the value of 'a' by +1.
  • Code line 9: Keyword 'END LOOP' marks the end of execution block.
  • The code from line 7 and line 8 will continue to execute till 'a' reaches the value 6, as the condition will returns TRUE, and the control will EXIT from the loop.
  • Code line 10: Printing the statement "Program completed"

FOR Loop Statement

"FOR LOOP" statement is best suitable when you want to execute a code for known number of times rather than based on some other conditions.

In this loop, the lower limit and the higher limit will be specified and as long as the loop variable is in between these range the loop will be executed.

The loop variable is self-incremental, so no explicit increment operation is needed in this loop. The loop variable need not to be declared, as it is declared implicitly.

Loops in PL/SQL Syntax Explanation:

  • In the above syntax, keyword 'FOR' marks beginning of the loop and 'END LOOP' marks the end of the loop.
  • Loop variable is evaluated every time before executing the execution part.
  • The execution block contains all the code that needs to be executed. The execution part can contain any execution statement.
  • The loop_variable is declared implicitly during the execution of the entire loop, and the scope of this loop_variable will be only inside this loop.
  • If the loop variable came out of the range, then control will exit from the loop.
  • The loop can be made to work in the reverse order by adding the keyword 'REVERSE' before lower_limit.

Example 1: In this example, we are going to print number from 1 to 5 using FOR loop statement. For that, we will execute the following code

Loops in PL/SQL

Code Explanation:

  • Code line 2: Printing the statement "Program started".
  • Code line 3: Keyword 'FOR' marks the beginning of the loop and loop_variable 'a' is declared. It now will have the value starting from 1 to 5
  • Code line 5: Prints the value of 'a'.
  • Code line 6: Keyword 'END LOOP' marks the end of execution block.
  • The code from line 5 will continue to execute till 'a' reaches the value 6, as the condition will fail, and the control will EXIT from the loop.
  • Code line 7: Printing the statement "Program completed"

Nested Loops

The loop statements can also be nested. The outer and inner loop can be of different types. In nested loop, for every one iteration value of the outer loop, the inner loop will be executed fully.

Loops in PL/SQL Syntax Explanation:

  • In the above syntax, the outer loop has one more loop inside it.
  • The loops can be of any types and execution functionality part is same.

Example 1: In this example, we are going to print number from 1 to 3 using FOR loop statement. Each number will be printed as many times as its value. For that, we will execute the following code

Loops in PL/SQLLoops in PL/SQL

Code Explanation:

  • Code line 2: Declaring the variable 'b' as 'NUMBER' data type.
  • Code line 4: Printing the statement "Program started".
  • Code line 5: Keyword 'FOR' marks the beginning of the loop and loop_variable 'a' is declared. It now will have the value starting from 1 to 3
  • Code line 7: Resetting the value of 'b' to '1' each time.
  • Code line 8: Inner while loop checks for the condition a>=b.
  • Code line 10: Prints the value of 'a' as long as the above condition is satisfied.
  • Code line 14: Printing the statement "Program completed"

Labelling of Loops

In PL/SQL, the loops can be labeled. The label should be enclosed between "<<" and ">>". The labeling of loops particularly in nested loop codes will give more readability. The label can be given in EXIT command to exit from that particular loop. Using label, the control can be made to directly exit the outer loop of the nested loops from anyplace inside the loops, by giving the exit command followed by outer loop label.

Loops in PL/SQL

Syntax Explanation:
  • In the above syntax, the out loop has one more loop inside it.
  • The '<<OUTER_LOOP>>' and '<<INNER_LOOP>>' are the labels of these loops.

Example 1: In this example, we are going to print number starting from 1 using Basic loop statement. Each number will be printed as many times as its value. The upper limit of the series is fixed at the program declaration part. Let us learn how we can use the label concept to achieve this. For that, we will execute the following code

Loops in PL/SQLLoops in PL/SQL

Code Explanation:

  • Code line 2-3: Declaring the variable 'a' and 'b' as 'NUMBER' data type.
  • Code line 4: Declaring the variable 'upper_limit' as 'NUMBER' data type with value '4'
  • Code line 6: Printing the statement "Program started".
  • Code line 7: The outer loop has been labeled as "outer_loop"
  • Code line 9: The value of 'a' is incremented by 1.
  • Code line 11: Inner loop has been labeled as "inner_loop".
  • Code line 13: EXIT condition that check whether the value 'a' is higher than 'upper_limit' value. If not then it will go further, else it exits outer loop directly.
  • Code line 14: Printing the value of 'b'.
  • Code line 15: Increments the value of 'b' by +1.
  • Code line 16: EXIT condition that checks whether the value of 'b' is higher than 'a'. If so, then it will exit the control from the inner loop.
  • Code line 14: Printing the statement "Program completed"

Summary

In this chapter, we have learnt the loops concept, different types of loops, nested loops and labeling of loops. Following loops summarize the types of loops and their usage area.

Loop

EXIT Criteria

Usage

Basic Loop

Exit when encounters the keyword 'EXIT' in the execution part

Good to use when exit is not based on any particular condition.

WHILE Loop

Exit when the check condition returns false

Good to use when the loop count is unknown, and exit is based on some other condition.

FOR Loop

Exit when the counter reaches the limit

Good to use when loop count to be executed is known.

 

YOU MIGHT LIKE:
PL-SQL

PL/SQL Data Types

In oracle, each value or constant is assigned with a data type. Any data type is associated with the...

PL-SQL

Packages in PL/SQL

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