Oracle statement Loop

Source: Internet
Author: User
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.

Related Article

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.