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;