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:
1 [<<label_name>>] 2LOOP 3 statement ... 4 END LOOP [Label_name] 5 6 "Syntax description" 7 <<label_name>>: The tab of the loop structure is optional. 8loop:loop cycle start flag. 9A sequence of statements in a Statement:loop statement that is executed in a loop. the ten end Loop:loop Loop End flag allows you 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.
1Sql>set serveroutput on;2Sql>--Exit when3Sql>Declare42 V_RLT Number (8): =-3;53begin64 <<fst_loop>>75Loop86 Dbms_output.put_line (' V_RLT = ' | |v_rlt);97 v_rlt:=v_rlt+1;Ten8 Exit Fst_loop when V_RLT > 3; One9end Loop; ATen Dbms_output.put_line (' Loop loop is over! ‘); -11end; -12/ the -V_RLT =-3 -V_RLT =-2 -V_RLT =-1 +V_RLT = 0 -V_RLT = 1 +V_RLT = 2 AV_RLT = 3 at Loop loop is over! - -PL/SQL procedure successfully completed
B. Use if ... Exit Statement End Loop
1Sql>--ifExit2Sql>Declare32 V_RLT Number (8): =-3;43begin54 <<fst_loop>>65Loop76 Dbms_output.put_line (' V_RLT = ' | |v_rlt);87 v_rlt:=v_rlt+1;98ifV_RLT > 3 ThenTen9 Dbms_output.put_line (' The value of the variable is already greater than 3, the current value is ' | |v_rlt); One10exit Fst_loop; AOne endif; -12end loop Fst_loop; -Dbms_output.put_line (' Loop ' loop is over! ‘); the14end; -15/ - -V_RLT =-3 +V_RLT =-2 -V_RLT =-1 +V_RLT = 0 AV_RLT = 1 atV_RLT = 2 -V_RLT = 3 - the value of the variable is already greater than 3 and the current value is 4 - Loop loop is over! - -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:
1 [<<label_name>>]while boolean_expression2LOOP3 Statement ... 4 END LOOP [label_name]; 5 "Syntax description" 6 boolean_expression: boolean expression. 7 statement: A sequence of statements that, when Boolean_expression is true, can have execution rights.
C, while ... Loop structure
1Sql>-- while... loop2Sql>Declare32 V_RLT Number (8): =-3;43begin54 <<while_loop>>65 while(V_rlt < 4)76Loop87 Dbms_output.put_line (' V_RLT = ' | |v_rlt);98 v_rlt:=v_rlt+1;Ten9end loop While_loop; OneTen Dbms_output.put_line (' While loop is over! ‘); A11end; -12 -13/ the -V_RLT =-3 -V_RLT =-2 -V_RLT =-1 +V_RLT = 0 -V_RLT = 1 +V_RLT = 2 AV_RLT = 3 at The while 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:
1[<<label_name>>]for index_name in[REVERSE]2 lower_bound. Upper_bound3 LOOP4 Statement ...5 END LOOP [label_name];6 "Syntax description"7 index_name: The loop counter is a variable that can get the current loop exponent. It is important to note that you cannot assign a value to it manually. 8 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. 9 Lower_bound: The subscript bounds of the cyclic range. Ten Upper_bound: The superscript bounds of the cycle range. One".." Between subscript and superscript cannot be omitted.
D, for ... Loop structure
1Sql>-- for.. Loop2Sql>begin32 forV_RLT in-3..3Loop43 Dbms_output.put_line (' V_RLT = ' | |v_rlt);54end Loop;65 Dbms_output.put_line (' For Loop has ended! ‘);76end;87/9 TenV_RLT =-3 OneV_RLT =-2 AV_RLT =-1 -V_RLT = 0 -V_RLT = 1 theV_RLT = 2 -V_RLT = 3 - The For loop is over! - +PL/SQL procedure successfully completed
PL/SQL Loop loop detailed