Basic Loops Database Management System

Basic Loops Database Management System

BASIC LOOPS

Syntax:

BASIC LOOPS Database Management System

 

Basic Loops

BASIC LOOPS Database Management System : The simplest form of LOOP statement is the basic (or infinite) loop, which encloses a sequence of statements between the keywords LOOP and END LOOP. Each time the flow of execution reaches the END LOOP statement, control is returned to the corresponding LOOP statement above it. A basic loop allows execution of its statement at least once, even if the condition is already met upon entering the loop. Without the EXIT statement, the loop would be infinite.

The EXIT Statement

You can use the EXIT statement to terminate a loop. Control passes to the next Statement after the END LOOP statement. You can issue EXIT either as an action within an IF statement or as a standalone statement within the loop. The EXIT statement must be placed inside a loop. In the latter case, you can attach a WHEN clause to allow Conditional termination of the loop. When the EXIT statement is encountered, the Condition in the WHEN clause is evaluated. If the condition yields TRUE, the loop ends and control passes to the next Statement after the loop. A basic loop can contain multiple EXIT control passes to the next statement after the loop. A basic loop can contain multiple EXIT.

Basic Loops

BASIC LOOPS Database Management System

 

BASIC LOOPS Database Management System

Use the WHILE loop to repeat statements while a condition is TRUE.

WHILE Loops

BASIC LOOPS Database Management System

FOR Loops

FOR counter IN (REVERSE)

lower_bound…upper bound LOOP

statement1;

statement 2;

END LOOP;

FOR Loops

FOR loops have the same general structure as the basic loop. In addition, they have a Control statement before the LOOP keyword to determine the number of iterations that PL/SQL performs. In the Syntax: Counter is an implicitly declared integer whose value automatically increases or decreases (decreases if the REVERSE keyword is used) by 1 on each iteration of the loop until the upper or lower bound is reached. REVERSE causes the counter to decrement with each iteration from the upper bound to the lower bound. (Note that the lower bound is still referenced first).

lower_bound Specifies the lower bound for the range of Counter values.

upper_bound specifies the upper bound for the range of counter values.

Do not declare the counter; it is declared implicitly as an integer.

Note: The Sequence of statements is executed each time the counter is incremented, as determined by the two bounds. The lower bound and upper bound of the loop range can be literals, variables, or expressions, but must evaluate to integers. The lower bound and upper bound are inclusive in the loop range. If the lower bound of the loop range evaluates to a larger integer than the upper bound, the sequence of statements will not be executed, provided REVERSE has not been used. For example the following, statement is executed only once:

FOR IN 3.3 LOOP statement1; END LOOP;

Nested Loops and Labels

  • Nest loops to multiple levels.
  • Use labels to distinguish between blocks and loops.
  • Exit the outer loop with the EXIT statement that references the label.

Nested Loops and Labels

You can nest loops to multiple levels. You can nest FOR, WHILE, and basic loops within one another. The termination of a nested loop does not terminate the enclosing loop unless an exception was raised. However, you can label loops and exit the outer loop with the EXIT statement.

Label names follow the same rules as other identifiers. A label is placed before a statement, either on the same line or on a separate line. Label loops by placing the label before the word LOOP within label

delimiters <<label>>.

If the loop is labelled, the label name can optionally be included after the END LOOP statement for

clarity.

 

Nested loops and labels

BEGIN

<<Outer loop>>

LOOP

var_counter := var Counter+1;

 EXIT WHEN var_counter-10;

<<Inner_loop–>

LOOP

EXIT Outer_loop WHEN total_done = ‘YES’;

 -Leave both loops

EXIT WHEN inner_done = ‘YES’;

 – Leave inner loop only

END LOOP inner_loop;

END LOOP Outer_loop;

END;