When writing a loop control structure, you can use three types of loop statements: Basic loop, WHILE loop, and FOR loop. The following describes how to use these three types of loop statements. 1. Basic loop LOOPstatement1; ...... EXIT [WHENcondition]; ENDLOOP; when using a basic loop, the statement will be executed at least whether or not the conditions are met
When writing a loop control structure, you can use three types of loop statements: Basic loop, WHILE loop, and FOR loop. The following describes how to use these three types of loop statements. 1. Basic LOOP statement1; ...... EXIT [WHEN condition]; end loop; WHEN using a basic LOOP, the statement will be executed at least whether or not the conditions are met
When writing a loop control structure, you can use three types of loop statements: Basic loop, WHILE loop, and FOR loop. The following describes how to use these three types of 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 whether or not the condition is met. When the condition is TRUE, the LOOP is exited and the corresponding operation after the end loop is executed. When writing a basic loop, you must include the EXIT statement. Otherwise, it will be in an endless loop. In addition, you should define the cyclic control variable and modify the value of the cyclic 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 Loop
The statement in the loop body must be executed at least once in a basic loop. For a WHILE loop, the statement in the loop body is executed only when the condition is TRUE. The while loop starts with the WHILE... LOOP and ends with the end loop.
WHILE condition LOOP
Statement1;
Statement2;
.....
End loop;
If condition is TRUE, the statement in the LOOP body is executed. If condition is FALSE or NULL, the LOOP is exited and the statement after the end loop is executed. When a WHILE loop is used, define the 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 Loop
When using a basic loop or WHILE loop, you need to define a loop control variable, and the loop control variable can not only use the NUMBER type, but also use other data types. When a FOR loop is used, ORACLE implicitly defines the loop control variable.
FOR counter in [REVERSE] lower_bound... upper_bound LOOP
Statement1;
Statement2;
.......
End loop;
Counter is a cyclic control variable that is implicitly defined by oracle and does not need to be explicitly defined. Lower_bound and upper_bound correspond to the lower bound value and upper bound value of the loop control variable. By default, when a FOR loop is used, the variable automatically increases by 1 during each loop. if the REVERSE option is specified, the loop control variable is automatically reduced by 1 for each loop. Example:
SQL> begin
2 for I in reverse 1 .. 10 loop
3 insert into testloop values (I );
4 end loop;
5 end;
4. nested loops and labels
Nested loop refers to embedding another loop statement in one loop statement, while label is used to mark nested blocks or nested loops by using labels in nested loops, the inner and outer loops can be distinguished, and the outer loops can be directly exited in the inner loop. < > Define a label. Example:
SQL> declare
2 result int;
3 begin
4 < >
5 for I in 1 .. 100 loop
6 < >
7 for j in 1 .. 100 loop
8 result: = I * j;
9 exit outer when result = 1000;
10 exit when result = 500;
11 end loop inter;
12 dbms_output.put_line (result );
13 end loop outer;
14 dbms_output.put_line (result );
15 end;
16/
When the preceding PL/SQL block is executed, if result = 1000, the outer loop is exited directly, and if result = 500, the inner loop is exited only.