#

| Chapter 1 Oracle PL/SQL Language Pocket Reference |

## 1.7 Loops

The LOOP construct allows you to repeatedly execute a sequence of statements. There are three kind of loops: simple, WHILE, and FOR.

Use the EXIT statement to break out of LOOP and pass control to the statement following the END LOOP.

### 1.7.1 The Simple Loop

The syntax for a simple loop is:

>     LOOP
>        executable_statement(s)
>     END LOOP;

The simple loop should contain an EXIT or EXIT WHEN statement so as not to execute infinitely. Use the simple loop when you want the body of the loop to execute at least once.

For example:

>     LOOP
>        FETCH company_cur INTO company_rec;
>        EXIT WHEN company_cur%ROWCOUNT > 5 OR
>           company_cur%NOTFOUND;
>        process_company(company_cur);
>     END LOOP;

### 1.7.2 The Numeric FOR Loop

The syntax for a numeric FOR loop is:

>     FOR loop_index IN [REVERSE] lowest_number..
>        highest_number
>     LOOP
>        executable_statement(s)
>     END LOOP;

The PL/SQL runtime engine automatically declares the loop index a PLS_INTEGER variable; never declare a variable with that name yourself. The lowest_number and highest_number ranges can be variables, but are evaluated only once -- on initial entry into the loop. The REVERSE keyword causes PL/SQL to start with the highest_number and decrement down to the lowest_number. For example:

>     BEGIN
>        DBMS_OUTPUT.PUT_LINE('Beginning Forward');
>        FOR counter IN 1 .. 4
>        LOOP
>           DBMS_OUTPUT.PUT_LINE('counter='||counter);
>        END LOOP;

>        DBMS_OUTPUT.PUT_LINE('Beginning REVERSE');
>        FOR counter IN REVERSE 1 .. 4
>        LOOP
>           DBMS_OUTPUT.PUT_LINE('counter='||counter);
>        END LOOP;
>     END;

### 1.7.3 The Cursor FOR Loop

The syntax for a cursor FOR loop is:

>     FOR record_index IN [cursor_name | (SELECT statement)]
>     LOOP
>        executable_statement(s)
>     END LOOP;

The PL/SQL runtime engine automatically declares the loop index a record of cursor_name%ROWTYPE; never declare a variable with that name yourself.

The cursor FOR loop automatically opens the cursor, fetches all rows identified by the cursor, and then closes the cursor. You can embed the SELECT statement directly in the cursor FOR loop. For example:

>     FOR emp_rec IN emp_cur
>     LOOP
>        IF emp_rec.title = 'Oracle Programmer'
>        THEN
>           give_raise(emp_rec.emp_id,30)
>        END IF;
>     END LOOP;

### 1.7.4 The WHILE Loop

The syntax for a WHILE loop is:

>     WHILE condition
>     LOOP
>        executable_statement(s)
>     END LOOP;

Use the WHILE loop when, depending on the entry condition, you don't want the loop body to execute even once:

>     WHILE NOT end_of_analysis
>     LOOP
>        perform_analysis;
>        get_next_record;
>        IF analysis_cursor%NOTFOUND AND next_step IS NULL
>        THEN
>           end_of_analysis := TRUE;
>        END IF;
>     END LOOP;

### 1.7.5 The REPEAT UNTIL Loop Emulation

PL/SQL does not directly support a REPEAT UNTIL construct, but a modified simple loop can emulate one. The syntax for this emulated REPEAT UNTIL loop is:

>     LOOP
>        executable_statement(s)
>        EXIT WHEN Boolean_condition;
>     END LOOP;

Use the emulated REPEAT UNTIL loop when executing iterations indefinitely before conditionally terminating the loop.

### 1.7.6 The EXIT Statement

The syntax for the EXIT statement is:

>     EXIT [WHEN Boolean_condition];

If you do not include a WHEN clause in the EXIT statement, it will terminate the loop unconditionally. Otherwise, the loop terminates only if Boolean_condition evaluates to TRUE. The EXIT statement is optional and can appear anywhere in the loop.

### 1.7.7 Loop Labels

Loops can be optionally labeled to improve readability and execution control. The label must appear immediately in front of the statement that initiates the loop.

The following example demonstrates the use of loop labels to qualify variables within a loop and also to terminate nested and outer loops:

>     <<year_loop>>
>     FOR yearind IN 1 .. 20
>     LOOP
>        <<month_loop>>
>        LOOP
>           ...
>           IF year_loop.yearind > 10
>           THEN
>              EXIT year_loop;
>           END IF;
>        END LOOP month_loop;
>     END LOOP year_loop;

1.6 Conditional and Sequential Control |  | 1.8 Database Interaction and Cursors

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.