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
-- ACCESS, ASE, AURORA_MYSQL, COCKROACHDB, DERBY, DUCKDB, MEMSQL, REDSHIFT, SNOWFLAKE, SQLITE, SYBASE, TERADATA, TRINO,
-- VERTICA
/* UNSUPPORTED */
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
References to this page
Feedback
Do you have any feedback about this page? We'd love to hear it!