PL/SQL LOOP Construct

Oracle提供三种样式的Loop结构

1. The Basic Loop

The BASIC loop repeats until a condition is met. Because the condition is tested at the end of the loop, the BASIC loop will always execute at least once.

A simple loop runs until you explicitly end the loop. The syntax for a simple loop is as follows:

<pre class="code">LOOP [Sequence of statements] EXIT [<em>condition</em>] END LOOP;

To end the loop, you use either an EXIT or EXIT WHEN statement.

The EXIT statement ends a loop immediately.

EXIT WHEN statement ends a loop when a specified condition occurs.

Sample

<strong>Count up by hundreds until we get an error</strong> SET ECHO ON SET SERVEROUTPUT ON DECLARE hundreds_counter NUMBER(1,-2); BEGIN hundreds_counter := 100; LOOP DBMS_OUTPUT.PUT_LINE(hundreds_counter); hundreds_counter := hundreds_counter + 100; END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Done.'); END; /
DECLARE i PLS_INTEGER := 1; BEGIN LOOP i := i + 1; INSERT INTO loop_test VALUES (i); IF i > 99 THEN EXIT; END IF; END LOOP; COMMIT; END; / SELECT * FROM loop_test;

2. The FOR Loop

The FOR loop repeats, incrementing or decrementing its internal counter until the counter reaches its pre-programmed limit, set by thelower_bound and higher_bound parameters.

-- incrementing FOR <variable> IN <start_number> .. <end_number> LOOP <code here> END LOOP; -- decrementing FOR <variable> IN REVERSE <start_number> .. <end_number> LOOP <code here> END LOOP;

Sample

BEGIN FOR i IN 2000 .. 2100 LOOP INSERT INTO loop_test VALUES (i); END LOOP; COMMIT; END; / SELECT * FROM loop_test; BEGIN FOR i IN REVERSE 3000 .. 3100 LOOP INSERT INTO loop_test VALUES (i); END LOOP; COMMIT; END; / SELECT * FROM loop_test;

3. The WHILE Loop

The WHILE loop repeats until a given condition is met. If the condition is not met it will repeat forever. If thecondition is met or satisfied before the loop begins, it will not execute at all.

WHILE condition LOOP

  statement_1;

  statement_2;

  . . .

  statement_n;

END LOOP;

Example

DECLARE i PLS_INTEGER := 999; BEGIN WHILE i < 1100 LOOP i := i + 1; INSERT INTO loop_test VALUES (i); END LOOP; COMMIT; END; / SELECT * FROM loop_test;