LOOP statement
Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Many procedural languages support a condition-less loop, which in its pure form, just loops forever. In order to create an infinite number of records in a table, one might write the following:
-- PL/SQL syntax LOOP INSERT INTO t (col) VALUES (1); END LOOP;
// All dialects loop( insertInto(T).columns(T.COL).values(1) )
An "infinite" loop is usually exited using an EXIT statement.
Dialect support
This example using jOOQ:
loop(update(BOOK_TO_BOOK_STORE).set(BOOK_TO_BOOK_STORE.STOCK, BOOK_TO_BOOK_STORE.STOCK.plus(1)))
Translates to the following dialect specific expressions:
Aurora Postgres, Postgres, YugabyteDB
LOOP
UPDATE BOOK_TO_BOOK_STORE
SET
STOCK = (BOOK_TO_BOOK_STORE.STOCK + 1);
END LOOP
BigQuery
LOOP
UPDATE BOOK_TO_BOOK_STORE
SET
BOOK_TO_BOOK_STORE.STOCK = (BOOK_TO_BOOK_STORE.STOCK + 1)
WHERE TRUE;
END LOOP
DB2, Exasol, HSQLDB, Informix, MariaDB, MySQL, Oracle
LOOP
UPDATE BOOK_TO_BOOK_STORE
SET
BOOK_TO_BOOK_STORE.STOCK = (BOOK_TO_BOOK_STORE.STOCK + 1);
END LOOP
Firebird
WHILE (1 = 1) DO BEGIN
UPDATE BOOK_TO_BOOK_STORE
SET
BOOK_TO_BOOK_STORE.STOCK = (BOOK_TO_BOOK_STORE.STOCK + 1);
END
H2
for (;;) {
try (PreparedStatement s = c.prepareStatement(
"UPDATE BOOK_TO_BOOK_STORE\n" +
"SET\n" +
" BOOK_TO_BOOK_STORE.STOCK = (BOOK_TO_BOOK_STORE.STOCK + 1)"
)) {
s.execute();
}
}
Hana
WHILE 1 = 1 DO
UPDATE BOOK_TO_BOOK_STORE
FROM BOOK_TO_BOOK_STORE
SET
BOOK_TO_BOOK_STORE.STOCK = (BOOK_TO_BOOK_STORE.STOCK + 1);
END WHILE
SQLDataWarehouse, SQLServer
WHILE 1 = 1 BEGIN
UPDATE BOOK_TO_BOOK_STORE
SET
BOOK_TO_BOOK_STORE.STOCK = (BOOK_TO_BOOK_STORE.STOCK + 1);
END
ASE, Access, Aurora MySQL, ClickHouse, CockroachDB, Derby, DuckDB, MemSQL, Redshift, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
References to this page
Feedback
Do you have any feedback about this page? We'd love to hear it!