Oracle PL/SQL Tutorial - PL/SQL LOOP

• « Previous
• Next »

The LOOP statement encloses a sequence of statements between the keywords LOOP and END LOOP, as follows:

 LOOP 
   sequence_of_statements 
 END LOOP; 
 

The sequence of statements is executed, then control resumes at the top of the loop.

We can use CONTINUE and CONTINUE-WHEN statements in a basic loop.

To prevent an infinite loop, you must use an EXIT or EXIT-WHEN statement.

EXIT Statement

When an EXIT statement is encountered, the loop exits immediately and control passes to the statement immediately after END LOOP.

DECLARE -- from   w ww. j  a v a 2s .  c  o m
  x NUMBER := 0; 
BEGIN 
  LOOP 
    DBMS_OUTPUT.PUT_LINE('Inside loop:  x = ' || TO_CHAR(x)); 
    x := x + 1;  
    IF x > 10 THEN 
      EXIT; 
    END IF; 
  END LOOP; 
  DBMS_OUTPUT.PUT_LINE (' After loop:  x = ' || TO_CHAR(x)); 
END; 
/ 

The code above generates the following result.

EXIT-WHEN Statement

When an EXIT-WHEN statement is encountered, the condition in the WHEN clause is evaluated.

If the condition is true, the loop completes and control passes to the statement immediately after END LOOP.

DECLARE -- from w ww.  ja v  a2 s.  c  o  m
  x NUMBER := 0; 
BEGIN 
  LOOP 
    DBMS_OUTPUT.PUT_LINE ('Inside loop:  x = ' || TO_CHAR(x)); 
    x := x + 1; 
    EXIT WHEN x > 10; 
  END LOOP; 
  DBMS_OUTPUT.PUT_LINE ('After loop:  x = ' || TO_CHAR(x)); 
END; 
/ 

The code above generates the following result.

Labeling a PL/SQL Loop

Loops can be labeled.

The optional label, an undeclared identifier enclosed by double angle brackets, must appear at the beginning of the LOOP statement.

The label name can also appear at the end of the LOOP statement.

With either form of EXIT statement, you can exit not only the current loop, but any enclosing loop.

   
DECLARE -- ww w. j av  a 2  s. co  m
  s  PLS_INTEGER := 0; 
  i  PLS_INTEGER := 0; 
  j  PLS_INTEGER; 
BEGIN 
  <<outer_loop>> 
  LOOP 
    i := i + 1; 
    j := 0; 
    <<inner_loop>> 
    LOOP 
      j := j + 1; 
      s := s + i * j;
      EXIT inner_loop WHEN (j > 10); 
      EXIT outer_loop WHEN ((i * j) > 25); 
    END LOOP inner_loop; 
  END LOOP outer_loop; 
  DBMS_OUTPUT.PUT_LINE('The sum: ' || TO_CHAR(s)); 
END; 
/ 

The code above generates the following result.

• Next »
• « Previous