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:

LOOP

  [Sequence of statements]

EXIT [condition]

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

Count up by hundreds until we get an error

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 the lower_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 the condition 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;