Oracle Loop statements

Oracle Loop statements

When you write a loop control structure, you can use three types of loop statements, such as the basic loop, while loop, and for loop, which describe the methods for using these three loop statements.

1. Basic cycle

LOOP

Statement1;

......

EXIT [when condition];

END LOOP;

When a basic loop is used, the statement is executed at least once, regardless of whether the condition is met, and when condition is true, the loop exits and the corresponding action after the end loop is executed. When writing a basic loop, be sure to include the exit statement, or you will get into a dead loop. You should also define a loop control variable and modify the value of the loop control variable in the loop body. Example:

Sql> Declare
2 I int:=1;
3 begin
4 loop
5 INSERT into Testloop values (i);
6 exit when i=10;
7 i:=i+1;
8 End Loop;
9 End;
10/

2.WHILE Cycle

The basic loop executes at least one statement in the body of the loop, whereas for a while loop, the statement in the loop body executes only if the condition is true. While loop with while ... Loop starts and ends with end loop.

While condition LOOP

Statement1;

Statement2;

.....

END LOOP;

When condition is true, the statement in the loop body is executed, and when condition is false or null, the loop exits and the statement after the end loop is executed. When using the while loop, you should define a loop control variable and change the value of the loop control variable in the loop body. Example:

Sql> Declare
2 I int:=1;
3 begin
4 while i<=10 loop
5 INSERT into Testloop values (i);
6 i:=i+1;
7 End Loop;
8 End;
9/

3.FOR Cycle

When using a basic loop or while loop, you need to define a loop control variable, and the loop control variable can use not only the number type, but also other data types. When using a For loop, Oracle implicitly defines the loop control variable.

For counter in [REVERSE] lower_bound. . Upper_bound LOOP

Statement1;

Statement2;

.......

END LOOP;

Counter is a loop control variable, and the variable is implicitly defined by Oracle and does not need to be explicitly defined. Lower_bound and Upper_bound correspond to the lower and upper bounds of the loop control variable, and by default, the loop control variable automatically increases by 1 per loop when the for loop is used. If you specify the reverse option, the loop control variable is automatically reduced by 1 per loop. Example:

Sql> begin
2 for I in reverse 1..10 loop
3 INSERT into Testloop values (i);
4 End Loop;
5 end;

4. Nesting Loops and labels

Nested loops are embedded in a circular statement, while the label (label) is used to mark nested blocks or nested loops, by using labels in nested loops, you can distinguish between inner and outer loops, and can exit the outer loop directly in the inner loop, and can be written with << Label_name>> defines the label. Example:

Sql> Declare
2 result int;
3 begin
4 <<outer>>
5 for I in 1..100 loop
6 <<inter>>
7 for J in 1..100 Loop
8 result:=i*j;
9 Exit outer when result=1000;
Ten exit when result=500;
one end loop Inter;
Dbms_output.put_line (result);
End loop outer;
Dbms_output.put_line (result);
The end;
16/

When the above PL/SQL block is executed, result=1000 exits the outer loop directly, while result=500 exits the inner loop only.

Oracle Loop statements