In this part, we will examine Loops in PL/SQL. There might be a circumstance when you need to execute a square of code a few number of times. When all is said in done, explanations are executed consecutively: The main assertion in a capacity is executed first, trailed continuously, etc.
Programming dialects give different control structures that consider more confounded execution ways.
A circle proclamation permits us to execute an assertion or gathering of explanations on numerous occasions and following is the overall type of a circle articulation in a large portion of the programming dialects −
PL/SQL gives the accompanying kinds of circle to deal with the circling prerequisites. Snap the accompanying connects to check their detail.
S.No | Loop Type & Description |
---|---|
1 |
PL/SQL Basic LOOP In this loop structure, sequence of statements is enclosed between the LOOP and the END LOOP statements. At each iteration, the sequence of statements is executed and then control resumes at the top of the loop. |
2 |
PL/SQL WHILE LOOP Repeats a statement or group of statements while a given condition is true. It tests the condition before executing the loop body. |
3 |
PL/SQL FOR LOOP Execute a sequence of statements multiple times and abbreviates the code that manages the loop variable. |
4 |
Nested loops in PL/SQL You can use one or more loop inside any another basic loop, while, or for loop. |
Labeling a PL/SQL Loop
PL/SQL circles can be marked. The name ought to be encased by twofold point sections (<< and >>) and show up toward the start of the LOOP proclamation. The mark name can likewise show up toward the finish of the LOOP articulation. You may utilize the mark in the EXIT articulation to exit from the circle.
The accompanying system outlines the idea −
DECLARE
i number(1);
j number(1);
BEGIN
<< outer_loop >>
FOR i IN 1..3 LOOP
<< inner_loop >>
FOR j IN 1..3 LOOP
dbms_output.put_line('i is: '|| i || ' and j is: ' || j);
END loop inner_loop;
END loop outer_loop;
END;
/
At the point when the above code is executed at the SQL brief, it delivers the accompanying outcome −
i is: 1 and j is: 1
i is: 1 and j is: 2
i is: 1 and j is: 3
i is: 2 and j is: 1
i is: 2 and j is: 2
i is: 2 and j is: 3
i is: 3 and j is: 1
i is: 3 and j is: 2
i is: 3 and j is: 3
PL/SQL procedure successfully completed.
The Loop Control Statements
Circle control proclamations change execution from its typical arrangement. At the point when execution leaves an extension, all programmed objects that were made in that degree are obliterated.
PL/SQL upholds the accompanying control articulations. Marking circles likewise help in taking the control outside a circle. Snap the accompanying connects to check their subtleties.
S.No | Control Statement & Description |
---|---|
1 |
EXIT statement The Exit statement completes the loop and control passes to the statement immediately after the END LOOP. |
2 |
CONTINUE statement Causes the loop to skip the remainder of its body and immediately retest its condition prior to reiterating. |
3 |
GOTO statement Transfers control to the labeled statement. Though it is not advised to use the GOTO statement in your program. |