The classic PL/SQL FOR loop counts from one number to another by one. Modern PL/SQL lets a single FOR loop step by any amount, cover several ranges, iterate over the values or indexes of a collection, and filter iterations, which removes most of the counters and IF statements that loops used to need.
Code for This Guide
The main examples are in the examples/plsql-basics folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
for i in [reverse] low .. high [by step] [, low .. high ...] [when condition] loop ... end loop; for value in values of collection loop ... end loop; for i in indices of collection loop ... end loop; for i, value in pairs of collection loop ... end loop;
Steps, Ranges, Values, and Pairs
Example:
declare
type t_codes is table of varchar2(3);
v_codes t_codes := t_codes('DXB', 'LHR', 'SIN');
begin
for i in 1 .. 10 by 3 loop
dbms_output.put_line('stepped: ' || i);
end loop;
for i in 1 .. 2, 8 .. 9 loop
dbms_output.put_line('ranges: ' || i);
end loop;
for code in values of v_codes loop
dbms_output.put_line('value: ' || code);
end loop;
for i, code in pairs of v_codes loop
dbms_output.put_line('pair: ' || i || ' = ' || code);
end loop;
end;
/Output:
stepped: 1 stepped: 4 stepped: 7 stepped: 10 ranges: 1 ranges: 2 ranges: 8 ranges: 9 value: DXB value: LHR value: SIN pair: 1 = DXB pair: 2 = LHR pair: 3 = SIN PL/SQL procedure successfully completed.
| Control | Iterates over |
|---|---|
| 1 .. 10 by 3 | 1, 4, 7, 10 |
| 1 .. 2, 8 .. 9 | Several ranges in turn |
| values of v_codes | The elements of the collection |
| pairs of v_codes | Each index with its element |
REVERSE, WHEN, and INDICES OF
REVERSE counts down, WHEN skips iterations whose condition is false, and INDICES OF walks the indexes of a sparse collection, here 10, 20, and 30.
Example:
declare
type t_codes is table of varchar2(3) index by pls_integer;
v_codes t_codes;
begin
v_codes(10) := 'DXB'; v_codes(20) := 'LHR'; v_codes(30) := 'SIN';
-- WHEN filters the iterations; REVERSE counts down
for i in reverse 1 .. 10 when mod(i, 3) = 0 loop
dbms_output.put_line('multiple of 3: ' || i);
end loop;
-- INDICES OF walks the indexes of a sparse collection
for i in indices of v_codes loop
dbms_output.put_line('index ' || i || ' = ' || v_codes(i));
end loop;
end;
/Output:
multiple of 3: 9 multiple of 3: 6 multiple of 3: 3 index 10 = DXB index 20 = LHR index 30 = SIN PL/SQL procedure successfully completed.
Without INDICES OF, looping from 1 to 30 over this collection would fail on the missing indexes.
Things to Know
- The loop variable is declared by the loop and is read-only inside it.
- Iteration controls can be combined in one loop, such as a range followed by the values of a collection.
- Cursor FOR loops, for r in (select ...) loop, iterate over query rows.
Related Guides
Conclusion
PL/SQL FOR loops accept steps, multiple ranges, REVERSE, WHEN filters, and the values, indexes, or pairs of a collection. Use them to iterate exactly over what you need without manual counters or IF statements.
