The Loop loop control statement for Oracle PL/SQL

In PL / SQL, you can use the LOOP statement to cycle through the data, and use this statement to cycle through the specified sequence of statements. The commonly used LOOP loop statement contains 3 forms: basic LOOP, WHILE ... LOOP and FOR ... LOOP.

    The basic grammatical structure of the LOOP statement is as follows:

[<< label_name >>]
LOOP
 statement ...
END LOOP [label_name]

【Grammar】
<< label_name >>: The label of the LOOP structure is optional.
LOOP: LOOP cycle start sign.
statement: A sequence of statements executed cyclically in a LOOP statement.
END LOOP: LOOP cycle end mark, you can add a LOOP structure label.
1. Basic LOOP statement
    Example: It is required to declare a variable. Each cycle needs to add 1 to the variable and output the result. When the variable value is greater than 3, the loop operation is exited.

    a. Use EXIT ... WHEN to end the loop operation.

SQL> set serveroutput on;
SQL> --exit when
SQL> declare
  2 v_rlt number (8): =-3;
  3 begin
  4 << fst_loop >>
  5 loop
  6 dbms_output.put_line (‘v_rlt =‘ || v_rlt);
  7 v_rlt: = v_rlt + 1;
  8 exit fst_loop when v_rlt> 3;
  9 end loop;
 10 dbms_output.put_line (‘LOOP cycle has ended!’);
 11 end;
 12 /
 
v_rlt = -3
v_rlt = -2
v_rlt = -1
v_rlt = 0
v_rlt = 1
v_rlt = 2
v_rlt = 3
The LOOP cycle is over!
 
PL / SQL procedure successfully completed
    b. End the loop using the IF ... EXIT statement

SQL> --if exit
SQL> declare
  2 v_rlt number (8): =-3;
  3 begin
  4 << fst_loop >>
  5 loop
  6 dbms_output.put_line (‘v_rlt =‘ || v_rlt);
  7 v_rlt: = v_rlt + 1;
  8 if v_rlt> 3 then
  9 dbms_output.put_line (‘The value of the variable is greater than 3, the current value is’ || v_rlt);
 10 exit fst_loop;
 11 end if;
 12 end loop fst_loop;
 13 dbms_output.put_line (‘LOOP cycle has ended!’);
 14 end;
 15 /
 
v_rlt = -3
v_rlt = -2
v_rlt = -1
v_rlt = 0
v_rlt = 1
v_rlt = 2
v_rlt = 3
The value of the variable is already greater than 3, the current value is 4
The LOOP cycle is over!
 
PL / SQL procedure successfully completed
2. WHILE ... LOOP structure
    WHILE ... LOOP structure is different from the basic LOOP statement, it can end the LOOP cycle itself. A boolean expression is required after the WHILE keyword. When the boolean expression after WHILE is TRUE, the statement sequence of loop weight is executed once, and then the expression after WHILE is re-judged whether it is TRUE. When the Boolean expression is FALSE, the entire LOOP cycle ends.

    The relevant syntax of the sentence structure is as follows:

[<< label_name >>]
WHILE boolean_expression
LOOP
statement ...
END LOOP [label_name];
【Grammar】
boolean_expression: Boolean expression.
statement: Statement sequence. When boolean_expression is TRUE, the statement sequence can obtain the execution right.
    c, WHILE ... LOOP structure

SQL> --while ... loop
SQL> declare
  2 v_rlt number (8): =-3;
  3 begin
  4 << while_loop >>
  5 while (v_rlt <4)
  6 loop
  7 dbms_output.put_line (‘v_rlt =‘ || v_rlt);
  8 v_rlt: = v_rlt + 1;
  9 end loop while_loop;
 10 dbms_output.put_line (‘WHILE cycle has ended!’)
 11 end;
 12
 13 /
 
v_rlt = -3
v_rlt = -2
v_rlt = -1
v_rlt = 0
v_rlt = 1
v_rlt = 2
v_rlt = 3
The WHILE cycle is over!
 
PL / SQL procedure successfully completed
3. FOR ... LOOP structure
    The FOR ... LOOP statement can traverse a range of integers, which is closed by the FOR and LOOP keywords. When entering the loop for the first time, the loop range will be determined and will not be calculated again in the future. Each cycle, the cycle index will automatically increase by 1.

    The syntax structure of the FOR ... LOOP statement is as follows:

[<< label_name >>]
FOR index_name IN
[REVERSE]
lower_bound .. upper_bound
LOOP
statement ...
END LOOP [label_name];
【Grammar】
index_name: loop counter, is a variable, it can get the current loop index. It should be noted that it cannot be assigned manually.
REVERSE: Optional, specify the cycle mode. The default looping mode is from subscript (lower_bound) to superscript (upper_bound). Use this option from the upper demarcation to the lower demarcation.
lower_bound: the lower bound of the loop range.
upper_bound: The upper bound of the loop range.
".." between subscript and superscript cannot be omitted.
    d. FOR ... LOOP structure

SQL> --for..loop
SQL> begin
  2 for v_rlt in -3..3 loop
  3 dbms_output.put_line (‘v_rlt =‘ || v_rlt);
  4 end loop;
  5 dbms_output.put_line (‘FOR cycle has ended!’);
  6 end;
  7 /
 
v_rlt = -3
v_rlt = -2
v_rlt = -1
v_rlt = 0
v_rlt = 1
v_rlt = 2
v_rlt = 3
The FOR loop is over!
Oracle PL / SQL LOOP loop control statement