Oracle FOR LOOP statement.

Preamble

In Oracle/PLSQL, the FOR LOOP cycle allows you to execute code again for a fixed number of times.

FOR LOOP statement syntax

FOR loop_counter IN [REVERSE] lowest_number...highest_number
LOOP
{...}
END LOOP;

Statement parameters and arguments

• loop_counter – a variable of the cycle counter in the FOR LOOP cycle.
• REVERSE is optional. If REVERSE is specified, the loop counter will be counted in reverse order.
• Low_number – initial value for loop_counter.
• highest_number – final value for loop_counter.
• statement – It is the code executed every time passing through a loop.

Note:
You can use the FOR LOOP loop when you want to execute the loop body a fixed number of times.
If the REVERSE parameter is specified, highest_number and lowest_number will have the start and end values for loop_counter respectively.

Example:

FOR Lcntr IN 1...20
LOOP
LCalc := Lcntr * 31;
END LOOP;

This example loop will run 20 times. The counter called LCntr will start with 1 and end with 20.

declare
LCalc number;
begin
FOR Lcntr IN 1...20
LOOP
LCalc := Lcntr * 31;
dbms_output.put_line(LCalc);
END LOOP;
end;

--Result

31
62
93
124
155
186
217
248
279
310
341
372
403
434
465
496
527
558
589
620

You can use the REVERSE modifier to run the loop in reverse order. For example:

FOR Lcntr IN REVERSE 1...15
LOOP
LCalc := Lcntr * 31;
END LOOP;

This example of the FOR LOOP loop will run 15 times. However, since REVERSE is specified, the counter is called LCntr, starts at 15 and ends at 1 (in fact, the loop is in the opposite direction).

Script text for example

declare
LCalc number;
begin
FOR Lcntr IN REVERSE 1...15
LOOP
LCalc := Lcntr * 31;
dbms_output.put_line(LCalc);
END LOOP;
end;

--Result:

465
434
403
372
341
310
279
248
217
186
155
124
93
62

PL/SQL tutorial: FOR Loop In Oracle Database

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.