Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12
FOR statement
Applies to ❌ 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_().
Dialect support
This example using jOOQ:
for_(i).in(1, 10).loop(insertInto(BOOK).columns(BOOK.ID).values(i))
Translates to the following dialect specific expressions:
Aurora Postgres, CockroachDB, Exasol, Oracle, Postgres, YugabyteDB
FOR i IN 1 .. 10 LOOP INSERT INTO BOOK (ID) VALUES (i); END LOOP
BigQuery
BEGIN
DECLARE i int64 DEFAULT 1;
WHILE i <= 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
SET i = (i + 1);
END WHILE;
END;
DB2
BEGIN
DECLARE i integer;
SET i = 1;
WHILE i <= 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
SET i = (i + 1);
END WHILE;
END;
Firebird
DECLARE i integer DEFAULT 1; WHILE (:i <= 10) DO BEGIN INSERT INTO BOOK (ID) VALUES (:i); :i = (:i + 1); END
H2
for (Integer i = 1; i <= 10; i++) {
try (PreparedStatement s = c.prepareStatement(
"INSERT INTO BOOK (ID)\n" +
"VALUES (?)"
)) {
s.setObject(1, i);
s.execute();
}
}
Hana
BEGIN
DECLARE i integer;
FOR i IN 1 .. 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
END FOR;
END;
HSQLDB
BEGIN ATOMIC
DECLARE i int;
SET i = 1;
WHILE i <= 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
SET i = (i + 1);
END WHILE;
END;
Informix
BEGIN
DEFINE i integer;
LET i = 1;
FOR i IN (1 TO 10) LOOP
INSERT INTO BOOK (ID)
VALUES (i);
END LOOP;
END;
MariaDB
FOR i IN 1 .. 10 DO INSERT INTO BOOK (ID) VALUES (i); END FOR
MySQL
BEGIN
DECLARE i int;
SET i = 1;
WHILE i <= 10 DO
INSERT INTO BOOK (ID)
VALUES (i);
SET i = (i + 1);
END WHILE;
END;
Snowflake
FOR i IN 1 TO 10 DO INSERT INTO BOOK (ID) SELECT :i; END FOR
SQLDataWarehouse
BEGIN
DECLARE @i int DEFAULT 1;
WHILE @i <= 10 BEGIN
INSERT INTO BOOK (ID)
SELECT @i;
SET @i = (@i + 1);
END;
END;
SQLServer
BEGIN
DECLARE @i int = 1;
WHILE @i <= 10 BEGIN
INSERT INTO BOOK (ID)
VALUES (@i);
SET @i = (@i + 1);
END;
END;
ASE, Access, Aurora MySQL, ClickHouse, Databricks, Derby, DuckDB, MemSQL, Redshift, SQLite, Sybase, Teradata, Trino, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!