The Loop loop control statement for Oracle PL/SQL

You can use the Loop statement to loop through the data in PL/SQL, which allows you to loop through the specified sequence of statements. The common Loop Loop statement consists of 3 types: basic loop, while ... Loop and for ... LOOP.

The basic syntax structure of the loop statement is as follows:

[<<label_name>>] LOOP statement ... END LOOP [Label_name] "Syntax description" tab of the <<LABEL_NAME>>:LOOP structure, is optional. Loop:loop loop start flag. A sequence of statements in a Statement:loop statement that is executed in a loop. End Loop:loop Loop End flag to add a label for the loop structure.

1. Basic Loop statement
Example: Requires declaring a variable, each cycle needs to increment the number 1 for the variable, and output the result. Exits the loop operation when the value of the variable is greater than 3 o'clock.

A. Using exit ... When the loop operation is ended.

Sql> set serveroutput on; Sql>--exit whensql> 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;       dbms_output.put_line (' Loop loop is over! ‘); One  end;   v_rlt = -3V_RLT = -2V_RLT = -1V_RLT = 0V_RLT = 1V_RLT = 2V_RLT = 3LOOP the loop is over! PL/SQL procedure successfully completed

B. Use if ... Exit Statement End Loop

Sql>--if exitsql> 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); Ten        exit Fst_loop; end       if; end     loop fst_loop;       dbms_output.put_line (' Loop is over! ‘);  v_rlt = -3V_RLT = -2V_RLT = -1V_RLT = 0V_RLT = 1V_RLT = 2V_RLT = 3 The value of the variable is already greater than 3, the current value is 4LOOP the loop has ended! PL/SQL procedure successfully completed

2, while ... Loop structure
While ... The loop structure, unlike the basic loop statement, can end the loop loop itself. A Boolean expression follows the While keyword, and when the Boolean expression after the while is true, the loop weight statement sequence is executed 1 times, and then the expression after the while is re-determined to be true, only if the Boolean expression after the while is false. To end the entire loop loop.

The syntax for the statement structure is as follows:

[<<label_name>>] While boolean_expressionloopstatement ... END LOOP [label_name]; "Syntax description" Boolean_expression: Boolean expression. Statement: A sequence of statements that, when Boolean_expression is true, can have execution rights.

C, while ... Loop structure

Sql>--while...loopsql> 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;       dbms_output.put_line (' While loop is over! ‘); One  end;  v_rlt = -3V_RLT = -2V_RLT = -1V_RLT = 0V_RLT = 1V_RLT = 2V_RLT = 3WHILE the loop is over! PL/SQL procedure successfully completed

3.FOR ... Loop structure
For ... The Loop statement can traverse an integer of a range that is enclosed by the For and Loop keywords. When the loop is first entered, the loop range is determined and will not be recalculated again. Once per cycle, the cycle index will automatically increase by 1.

For ... The syntax structure of the loop statement is as follows:

[<<label_name>>] For index_name in[REVERSE]lower_bound. Upper_boundloopstatement ... END loop [label_name]; "Syntax description" Index_name: Loop counter, which is a variable that can get the current loop exponent. It is important to note that you cannot assign a value to it manually. REVERSE: Optional, specifies the loop mode. The default loop mode is the subscript (lower_bound) to superscript (upper_bound). This option is used from superscript to subscript boundaries. Lower_bound: The subscript bounds of the cyclic range. Upper_bound: The superscript bounds of the cycle range. ".." Between subscript and superscript cannot be omitted.

D, for ... Loop structure

Sql>--for. Loopsql> 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 Loop has ended! ');  6  end;  7  /v_rlt = -3V_RLT = -2V_RLT = -1V_RLT = 0V_RLT = 1V_RLT = 2V_RLT = 3FOR the loop is over! PL/SQL procedure successfully completed

The Loop loop control statement for Oracle PL/SQL