PL/SQL Loops

PL/SQL Loops

Basic loop

A loop that executes an unlimited number of times. It encloses a sequence of statements between the keywords LOOP and END LOOP. With each iteration, the sequence of statements is executed, then control resumes at the top of the loop. An EXIT, GOTO, or RAISE statement branches out of the loop. A raised exception also ends the loop. [Via]

Syntax of PL/SQL Basic loop

loop
  /* statements */
end loop;

Example of PL/SQL Basic loop

declare
num number := 1;
begin
 loop
  dbms_output.put_line ('The Number is '||num);
  num := num +1;
   if num > 10
   Then
    dbms_output.put_line('Existing the Basic loop.');
   exit;
   end if;
 end loop;
end;
/

The Number is 1
The Number is 2
The Number is 3
The Number is 4
The Number is 5
The Number is 6
The Number is 7
The Number is 8
The Number is 9
The Number is 10
Exiting the Basic loop.


While loop

The WHILE-LOOP statement associates a Boolean expression with a sequence of statements enclosed by the keywords LOOP and END LOOP. Before each iteration of the loop, the expression is evaluated. If the expression returns TRUE, the sequence of statements is executed, then control resumes at the top of the loop. If the expression returns FALSE or NULL, the loop is bypassed and control passes to the next statement. [Via]

Syntax of PL/SQL While loop

while A > B loop
  /* statements */
end loop

Example of PL/SQL While loop

declare
v_test varchar2(8) := 'RUN';
n_numb number := 2;
begin
while v_test <> 'STOP' loop
if n_numb > 5
then v_test := 'STOP';
end if;
dbms_output.put_line (v_test||': '||n_numb);
n_numb := n_numb + 1;
end loop;

v_test := 'DOWN';
while n_numb > 1 AND v_test = 'DOWN' loop
dbms_output.put_line (v_test||': '||n_numb);
n_numb := n_numb - 1;
end loop;

while 7 = 4 loop
NULL; --never get here
end lop;
end;
/

RUN: 2
RUN: 3
RUN: 4
RUN: 5
STOP: 6
DOWN: 7
DOWN: 6
DOWN: 5
DOWN: 4
DOWN: 3
DOWN: 2

The last loop will never execute because the condition will never be true. The middle loop uses multiple condition tests, using the AND key word. The first loop runs while v_Test does not equal 'STOP'.

[Via: Easy Oracle PL/SQL Programming: Get Started Fast with Working PL/SQL Code By John Garmany]

For loop

Numeric FOR_LOOP loops iterate over a specified range of integers. The range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP.

The range is evaluated when the FOR loop is first entered and is never re-evaluated. The loop body is executed once for each integer in the range defined by lower_bound..upper_bound. After each iteration, the loop index is incremented.[Via]

Syntax of PL/SQL For Loop

FOR LOOP_COUNTER IN LOWER_LIMIT..UPPER_LIMIT LOOP

STATEMENTS...;

END LOOP;

Example of PL/SQL For Loop

Below PL/SQL For Loop calculates Factorial of 10.

SET SERVEROUTPUT ON
DECLARE
  fact_num NUMBER := 1;
BEGIN
  FOR v_counter IN 1..10 LOOP
   fact_num := fact_num * v_counter;
  END LOOP;

DBMS_OUTPUT.PUT_LINE
('Factorial of 10 is: '||fact_num);
END;

Example of PL/SQL Reverse For Loop

Begin
 For num in Reverse 1..3 Loop
  DBMS_OUTPUT.PUT_LINE('num = '|| num);
 END LOOP;
END;

num = 3
num = 2
num = 1