Plsql - Loops

There may be a situation when you need to execute a block of code several number of times. In general, statements are executed sequentially: The first statement in a function is executed first, followed by the second, and so on.

Programming languages provide various control structures that allow for more complicated execution paths.

A loop statement allows us to execute a statement or group of statements multiple times and following is the general form of a loop statement in most of the programming languages:

PL/SQL provides the following types of loop to handle the looping requirements. Click the following links to check their detail.

Loop Type	Description
PL/SQL Basic LOOP	In this loop structure, sequence of statements is enclosed between the LOOP and END LOOP statements. At each iteration, the sequence of statements is executed and then control resumes at the top of the loop.
PL/SQL WHILE LOOP	Repeats a statement or group of statements until a given condition is true. It tests the condition before executing the loop body.
PL/SQL FOR LOOP	Execute a sequence of statements multiple times and abbreviates the code that manages the loop variable.
Nested loops in PL/SQL	You can use one or more loop inside any another basic loop, while or for loop.

PL/SQL Basic LOOP

Basic loop structure encloses sequence of statements in between the LOOP and END LOOP statements. With each iteration, the sequence of statements is executed and then control resumes at the top of the loop.

Syntax:

The syntax of a basic loop in PL/SQL programming language is:


Here, sequence of statement(s) may be a single statement or a block of statements. An EXIT statement or an EXIT WHEN statement is required to break the loop.

Example:


When the above code is executed at SQL prompt, it produces the following result:


You can use the EXIT WHEN statement instead of the EXIT statement:


When the above code is executed at SQL prompt, it produces the following result:


PL/SQL WHILE LOOP

A WHILE LOOP statement in PL/SQL programming language repeatedly executes a target statement as long as a given condition is true.

Syntax:


Example:


When the above code is executed at SQL prompt, it produces the following result:


PL/SQL FOR LOOP

A FOR LOOP is a repetition control structure that allows you to efficiently write a loop that needs to execute a specific number of times.

Syntax:


Here is the flow of control in a for loop:

• The initial step is executed first, and only once. This step allows you to declare and initialize any loop control variables.
• Next, the condition ,i.e., initial_value .. final_value is evaluated. If it is TRUE, the body of the loop is executed. If it is FALSE, the body of the loop does not execute and flow of control jumps to the next statement just after the for loop.
• After the body of the for loop executes, the value of the counter variable is increased or decreased.
• The condition is now evaluated again. If it is TRUE, the loop executes and the process repeats itself (body of loop, then increment step, and then again condition). After the condition becomes FALSE, the FOR-LOOP terminates. Following are some special characteristics of PL/SQL for loop:
• The initial_value and final_value of the loop variable or counter can be literals, variables, or expressions but must evaluate to numbers. Otherwise, PL/SQL raises the predefined exception VALUE_ERROR.
• The initial_value need not to be 1; however, the loop counter increment (or decrement) must be 1.
• PL/SQL allows determine the loop range dynamically at run time.

Example:


When the above code is executed at SQL prompt, it produces the following result:


Reverse FOR LOOP Statement

By default, iteration proceeds from the initial value to the final value, generally upward from the lower bound to the higher bound. You can reverse this order by using the REVERSE keyword. In such case, iteration proceeds the other way. After each iteration, the loop counter is decremented.

However, you must write the range bounds in ascending (not descending) order. The following program illustrates this:


When the above code is executed at SQL prompt, it produces the following result:


Nested loops in PL/SQL

PL/SQL allows using one loop inside another loop. Following section shows few examples to illustrate the concept.

The syntax for a nested basic LOOP statement in PL/SQL is as follows:


The syntax for a nested FOR LOOP statement in PL/SQL is as follows:


The syntax for a nested WHILE LOOP statement in Pascal is as follows:


Example:

The following program uses a nested basic loop to find the prime numbers from 2 to 100:


When the above code is executed at SQL prompt, it produces the following result:


Labeling a PL/SQL Loop

PL/SQL loops can be labeled. The label should be enclosed by double angle brackets (<< and >>) and appear at the beginning of the LOOP statement. The label name can also appear at the end of the LOOP statement. You may use the label in the EXIT statement to exit from the loop.

The following program illustrates the concept:


When the above code is executed at SQL prompt, it produces the following result:


The Loop Control Statements

Loop control statements change execution from its normal sequence. When execution leaves a scope, all automatic objects that were created in that scope are destroyed.

PL/SQL supports the following control statements. Labeling loops also helps in taking the control outside a loop. Click the following links to check their detail.

Control Statement	Description
EXIT statement	The Exit statement completes the loop and control passes to the statement immediately after END LOOP
CONTINUE statement	Causes the loop to skip the remainder of its body and immediately retest its condition prior to reiterating.
GOTO statement	Transfers control to the labeled statement. Though it is not advised to use GOTO statement in your program.

EXIT statement

The EXIT statement in PL/SQL programming language has following two usages:

• When the EXIT statement is encountered inside a loop, the loop is immediately terminated and program control resumes at the next statement following the loop.
• If you are using nested loops (i.e. one loop inside another loop), the EXIT statement will stop the execution of the innermost loop and start executing the next line of code after the block.

Syntax:

The syntax for an EXIT statement in PL/SQL is as follows:


Example:


When the above code is executed at SQL prompt, it produces the following result:


The EXIT WHEN Statement

The EXIT-WHEN statement allows the condition in the WHEN clause to be evaluated. If the condition is true, the loop completes and control passes to the statement immediately after END LOOP.

Following are two important aspects for the EXIT WHEN statement:

• Until the condition is true, the EXIT-WHEN statement acts like a NULL statement, except for evaluating the condition, and does not terminate the loop.
• A statement inside the loop must change the value of the condition.

Syntax:

The syntax for an EXIT WHEN statement in PL/SQL is as follows:


The EXIT WHEN statement replaces a conditional statement like if-then used with the EXIT statement.

Example:


When the above code is executed at SQL prompt, it produces the following result:


CONTINUE statement

The CONTINUE statement causes the loop to skip the remainder of its body and immediately retest its condition prior to reiterating. In other words, it forces the next iteration of the loop to take place, skipping any code in between.

Syntax:

The syntax for a CONTINUE statement is as follows:


Example:


When the above code is executed at SQL prompt, it produces the following result:


GOTO statement

A GOTO statement in PL/SQL programming language provides an unconditional jump from the GOTO to a labeled statement in the same subprogram.

NOTE: Use of GOTO statement is highly discouraged in any programming language because it makes difficult to trace the control flow of a program, making the program hard to understand and hard to modify. Any program that uses a GOTO can be rewritten so that it doesn't need the GOTO.

Syntax:

The syntax for a GOTO statement in PL/SQL is as follows:


When the above code is executed at SQL prompt, it produces the following result:


Restrictions with GOTO Statement

GOTO Statement in PL/SQL imposes the following restrictions:

• A GOTO statement cannot branch into an IF statement, CASE statement, LOOP statement or sub-block.
• A GOTO statement cannot branch from one IF statement clause to another or from one CASE statement WHEN clause to another.
• A GOTO statement cannot branch from an outer block into a sub-block (that is, an inner BEGIN-END block).
• A GOTO statement cannot branch out of a subprogram. To end a subprogram early, either use the RETURN statement or have GOTO branch to a place right before the end of the subprogram.
• A GOTO statement cannot branch from an exception handler back into the current BEGINEND block. However, a GOTO statement can branch from an exception handler into an enclosing block.

---