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
  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, MEMSQL, REDSHIFT, SNOWFLAKE, SQLITE, SYBASE, TERADATA, VERTICA
/* UNSUPPORTED */

(These are currently generated with jOOQ 3.17, see #10141), or translate your own on our website

The jOOQ User Manual : SQL building : Procedural statements : LOOP statement

previous : next