Shulou(Shulou.com)06/01 Report--

Loops refer to the repeated execution of one or more statements in a program. There are three main types of loops in PL/SQL:

1.Basic Loop

2. FORLoop

3.WHILE Loop

Let's introduce the usage of these three loops one by one.

1. BasicLoops

The format of the basic loop is as follows:

LOOP

Statement1

...

EXIT [WHENcondition]

END LOOP

The EXIT here is used to exit the loop, and if there is no EXIT, it will become an endless loop.

Let's look at a small example:

SQL > select location_id, city, country_id fromlocations where country_id = 'CA'

LOCATION_ID CITY CO--1800 Toronto CA 1900 Whitehorse CA-- currently has two records

SQL > edit

DECLARE v_countryid locations.country_id%TYPE: = 'CA'; v_loc_id locations.location_id%TYPE; v_counter NUMBER (2): = 1; v_new_city locations.city%TYPE: =' Montreal';BEGIN SELECT MAX (location_id) INTO v_loc_id FROM locations WHERE country_id = v_countryid LOOP INSERT INTOlocations (location_id, city, country_id) VALUES ((v_loc_id + v_counter), v_new_city, v_countryid); v_counter: = vcountermeasures + 1; EXIT WHEN v_counter > 3;-- EXIT can be followed by a tag to specify which layer of loop END LOOP; COMMIT;END;/ to exit

SQL > /

PL/SQL procedure successfully completed.

SQL > select location_id, city, country_id from locations where country_id = 'CA'

LOCATION_ID CITY CO--1800 Toronto CA 1900 Whitehorse CA 1901 Montreal CA 1902 Montreal CA 1903 Montreal CA-after executing the loop statement Three more records

2. WHILE cycle

The format of the WHILE loop is as follows:

WHILE condition LOOP

Statement1

Statement2

...

END LOOP

A WHILE loop means that the loop body is executed when the result of the condition is TRUE. Its loop body can be executed 0 or more times, which is more suitable for cases where the number of cycles is uncertain. When the condition is not met, it automatically exits the loop.

Now use the WHILE loop to rewrite the previous example:

SQL > edit

DECLARE v_countryid locations.country_id%TYPE: = 'CA'; v_loc_id locations.location_id%TYPE; v_counter NUMBER (2): = 1; v_new_city locations.city%TYPE: =' Montreal';BEGIN SELECT MAX (location_id) INTO v_loc_id FROM locations WHERE country_id = vested roomyid; WHILE v_counter /

PL/SQL procedure successfully completed.

SQL > select location_id, city, country_id from locations where country_id = 'CA'

LOCATION_ID CITY CO

-

1800 Toronto CA

1900 Whitehorse CA

1901 Montreal CA

1902 Montreal CA

1903 Montreal CA

1904 Montreal CA

1905 Montreal CA

1906 Montreal CA

8 rows selected.

-- three more records

If the condition is not met once, the WHILE loop will not be executed once.

III. FOR cycle

The basic format of the FOR loop is:

FORcounter IN [REVERSE]

-- REVERSE is a keyword that indicates a reverse loop, such as a loop from 10 to 1

Lower_bound..upper_bound LOOP

-- the lower boundary and the upper boundary, respectively. The variable counter and the upper and lower boundaries must be numerical.

Statement1

Statement2

...

ENDLOOP

FOR loops are often used when the number of loops is known, and FOR loops do not need to specifically declare a variable as a counter. Its counter can not be used after the FOR loop, if you must use it, it is recommended to declare a counter. Unlike FOR loops in other languages, users cannot customize the step size, and if you want to customize the step size, you can use a basic loop or a WHILE loop instead.

Let's rewrite the previous example with a FOR loop:

SQL > edit

DECLARE v_countryid locations.country_id%TYPE: = 'CA'; v_loc_id locations.location_id%TYPE; v_new_city locations.city%TYPE: =' Montreal';BEGIN SELECT MAX (location_id) INTO v_loc_id FROM locations WHERE country_id = v_countryid FOR i IN 1.. 3-the I here does not need to be specifically declared in the DECLARE section, 1 and 3 are the lower and upper boundaries of the loop, respectively-the step size of the FOR loop is fixed, and you cannot define LOOP INSERT INTOlocations (location_id, city, country_id) VALUES ((v_loc_id + I), v_new_city, v_countryid). END LOOP;-- DBMS_OUTPUT.PUT_LINE ('The counter is' | | I);-- Open the comment for the first time to verify what happens after the counter goes out of the loop. COMMIT;END;/

SQL > /

* ERROR at line 16:ORA-06550: line 16, column 44:PLS-00201: identifier'i 'must be declaredORA-06550: line 16, column 2:PL/SQL: Statement ignored-- reported an error because the counter I is not declared, so it cannot be used without the FOR loop.

SQL > /

PL/SQL procedure successfully completed.

SQL > select location_id, city, country_id from locations where country_id = 'CA'

LOCATION_ID CITY CO--1800 Toronto CA 1900 Whitehorse CA 1901 Montreal CA 1902 Montreal CA 1903 Montreal CA 1904 Montreal CA 1905 Montreal CA 1906 Montreal CA 1907 Montreal CA 1908 Montreal CA 1909 Montreal CA 11 rows selected.-- has three more records

The FOR loop is a popular loop because its number of loops can be controlled, but there are some basic rules to pay attention to when using a FOR loop:

a. Counter counter can only be referenced inside a loop, it does not need to be defined outside the loop

b. Do not assign a value to the counter counter, which is assigned automatically, but you can assign the value of the counter to other variables

c. Do not use null as the lower and upper bound of the number of cycles

d. The lower and upper bounds can be non-integers, but non-integers are automatically calculated as integers.

e. The addition of the keyword REVERSE indicates that the number of cycles is in reverse order, that is, it is executed from the upper bound to the lower bound, but even if REVERSE is used, the value of the lower bound should still be smaller than that of the upper bound. Here is an example to demonstrate the use of REVERSE:

SQL > edit

BEGIN DBMS_OUTPUT.PUT_LINE ('- Normal-'); FORi IN 1.. 3-normal order LOOP DBMS_OUTPUT.PUT_LINE ('Outputis' | | I); ENDLOOP; DBMS_OUTPUT.PUT_LINE ('- Reverse-') FORi IN REVERSE 1.. 3-if the keyword REVERSE is added, LOOP DBMS_OUTPUT.PUT_LINE ('Outputis' | | I) is executed in reverse order; END LOOP; DBMS_OUTPUT.PUT_LINE ('- Upperand Lower-') FORi IN REVERSE 3.. 1-although the REVERSE keyword is used, the lower boundary is larger than the upper boundary, resulting in uncontrollable phenomena LOOP DBMS_OUTPUT.PUT_LINE ('Outputis' | | I); END LOOP; END; /

SQL > /

-Normal-Output is 1Output is 2Output is 3-Reverse-Output is 3Output is 2Output is 1-Upper andLower- is not executed because the lower bound is greater than the upper bound, so the result is considered to be FALSE, so the loop body is not entered. PL/SQL proceduresuccessfully completed.

IV. Selection of cycle types

The choice of cycle types should be based on actual needs, and only some basic suggestions are provided here:

1. The body of the loop must be executed at least once. Basic LOOP is recommended.

two。 The condition is judged first to determine that the loop body executes 0 or more times, especially when the number of cycles is uncertain, it is recommended to use WHILE loop.

3. The number of loops is known, so it is recommended to use FOR loops.

5. Nesting of loops

Loops can be nested, but be careful not to nest too many times, preferably no more than 3 layers. It is a good programming practice to use tags to distinguish between code blocks and loop bodies. EXIT is used in conjunction with tags to specify which layer of loops the current loop exits. Let's take a look at a small example:

VI. CONTITUE keyword

CONTITUE is a concept introduced in ORACLE11g, and CONTINUE cannot be used if the version of the database is prior to 11g.

The usage of CONTITUE is:

1. Skip the current statement, but do not exit the loop and go straight to the next loop

two。 It has the same syntax as EXIT, and can be used

A. CONTINUE

-- No conditions attached

B.CONTINUE WHEN condition

-- additional conditions

C.CONTINUE label

-Jump to a tag

Before 11g, if you want to achieve the function of CONTINUE, you need to write more complex programs, and introduce CONTINUE to simplify the program and improve performance. Let's look at an example of CONTINUE:

SQL > edit

DECLARE v_total SIMPLE_INTEGER: = 0 v_total begin FOR i IN 1.. 10 LOOP v_total: = v_total + I; DBMS_OUTPUT.PUT_LINE ('Total is:' | v_total); CONTINUE WHEN i > 5;-- when I is greater than 5, jump out of the current cycle and enter the next cycle v_total: = v_total + I DBMS_OUTPUT.PUT_LINE ('Out of Loop Total is' | | v_total); END LOOP;END;/

SQL > /

When Total is: 1Out of Loop Total is2Total is: 4Out of Loop Total is6Total is: 9Out of Loop Total is12Total is: 16Out of Loop Total is20Total is: 25Out of Loop Total is30-- I is greater than 5, jump out of the current cycle Total is: 36Total is: 43Total is: 51Total is: 60Total is: 70 PL/SQL procedure successfully completed.

Let's take a look at an example of the combination of CONTINUE and tags:

SQL > edit

DECLARE v_total NUMBER: = 0 BeforeTopLoop begin FOR i IN 1.. 10 LOOP v_total: = vantage total1; DBMS_OUTPUT.PUT_LINE ('--BeforeTopLoop---Total is:'| | v_total); FOR j IN 1.. 10 LOOP CONTINUE BeforeTopLoop WHEN i + j > 5;-- Jump to tag BeforeTopLoop at v_total: = vroomtotal1 DBMS_OUTPUT.PUT_LINE ('--AfterTopLoop---Total is:'| | v_total); END LOOP;END LOOP;END

SQL > /

-BeforeTopLoop---Total is: 1---AfterTopLoop---Total is: 2---AfterTopLoop---Total is: 3---AfterTopLoop---Total is: 4---AfterTopLoop---Total is: 5---BeforeTopLoop---Total is: 6---AfterTopLoop---Total is: 7---AfterTopLoop---Total is: 8---AfterTopLoop---Total is: 9---BeforeTopLoop---Total is: 10---AfterTopLoop---Total is: 11---AfterTopLoop---Total is: 12---BeforeTopLoop---Total is: 13---AfterTopLoop---Total is: 14---BeforeTopLoop---Total is: 15---BeforeTopLoop---Total is: 16---BeforeTopLoop---Total is: 17---BeforeTopLoop---Total is: 18---BeforeTopLoop---Total is: 19---BeforeTopLoop---Total is: 20 PL/SQL procedure successfully completed.

Let's look at an example of using CONTINUE to jump from an inner loop to an outer loop:

SQL > edit

BEGIN FOR i IN 1.. 5 LOOP DBMS_OUTPUT.PUT_LINE ('Outer index =' | | TO_CHAR (I)); FOR j IN 1.. 5 LOOP DBMS_OUTPUT.PUT_LINE ('- > Inner index ='| | TO_CHAR (j)) This statement will not be executed five times because it will jump out of this layer of loop CONTINUE outer;END LOOP inner; END LOOP outer;END after each execution.

SQL > /

Outer index = 1Murray-> Inner index = 1Outer index = 2murmure-> Inner index = 1Outer index = 3Murray-> Inner index = 1Outer index = 4Murray-> Inner index = 1Outer index = 5Murray-> Inner index = 1 PL/SQL procedure successfully completed.

VI. GOTO sentence

CONTINUE is used with tags, which is similar to GOTO statements. The GOTO statement can jump unconditionally to another code block, but that code block must be in the same executable section (such as the BEGIN or EXCEPTION section) as the code block before the jump. GOTO is not recommended in practical programming because it does not require conditional control and is easy to destroy the readability of the program.

Let's look at an example of a GOTO statement:

SQL > edit

BEGIN GOTO second_output;-jumps directly to the location of the tag, and the following statement never executes DBMS_OUTPUT.PUT_LINE ('This linewill never execute.'); DBMS_OUTPUT.PUT_LINE (' We area hereafter'); END;/

SQL > /

We are here!PL/SQL procedure successfully completed.

When using the GOTO statement, it is best to add the IF statement to set the condition of the jump.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.