All versions: 3.12 | Development versions: 3.13

FOR statement

Available in ❌ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

When iterating over a sequence of numeric values, the FOR loop provides useful syntax sugar over the previous types of loops, including the WHILE loop, despite being functional equivalent.

An example:

-- PL/SQL

FOR i IN 1 .. 10 LOOP
  INSERT INTO t (col) VALUES (i);
END LOOP;
// All dialects
Variable<Integer> i = var("i", INTEGER);
for_(i).in(1, 10).loop(
  insertInto(T).columns(T.COL).values(i)
)

In addition to simplifying the most common case, there are also options of traversing the arguments in a reversed way, and using an additional optional step variable, for example:

-- pgplsql

FOR i IN REVERSE 10 .. 1 BY 2 LOOP
  INSERT INTO t (col) VALUES (i);
END LOOP;
// All dialects
Variable<Integer> i = var("i", INTEGER);
for_(i).inReverse(10, 1).by(2).loop(
  insertInto(T).columns(T.COL).values(i)
)

Not all dialects support the entirety of this syntax, but luckily it is easy for jOOQ to emulate in all dialects using WHILE:

-- PL/SQL
WHILE i >= 1 LOOP
  INSERT INTO t (col) VALUES (i);
  i := i - 2;
END LOOP;

Notice that for is a reserved keyword in the Java language, so the jOOQ API cannot use it as a method name. We've suffixed such conflicts with an underscore: for_().

The jOOQ Logo