PL/SQL Loop loop detailed

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.