CONTINUE statement

Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition

A safer way to jump to labels than via GOTO is to use EXIT (jumping out of a LOOP, or block, or other statement) or CONTINUE (skipping a LOOP iteration).

Just like Java's continue, the CONTINUE statement skips the rest of the current iteration in a loop and continues the next iteration. Some dialects use the ITERATE statement for this.

Without a label

-- PL/SQL
LOOP
  i := i + 1;
  CONTINUE WHEN i <= 10;
END LOOP;

// All dialects
loop(
  i.set(i.plus(1)),
  continueWhen(i.gt(10))
)

With a label

-- PL/SQL
<<label>>
LOOP
  i := i + 1;
  CONTINUE label WHEN i <= 10;
END LOOP;

// All dialects
Label label = label("label");
label.label(loop(
  i.set(i.plus(1)),
  continue(label).when(i.le(10))
))

Notice that continue 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: continue_().

Dialect support

This example using jOOQ:

loop(i.set(i.plus(1)), continueWhen(i.gt(10)))

Translates to the following dialect specific expressions:

-- AURORA_POSTGRES
LOOP
  SET i = (i + 1);
  CONTINUE WHEN i > 10;
END LOOP

-- BIGQUERY
LOOP
  SET i = (i + 1);
  IF i > 10 THEN
    CONTINUE;
  END IF;
END LOOP

-- DB2, HSQLDB, MARIADB, MYSQL
alias_1:
LOOP
  SET i = (i + 1);
  IF i > 10 THEN
    ITERATE alias_1;
  END IF;
END LOOP

-- H2
for (;;) {
  i = (i + 1);
  if (i > 10) {
    continue;
  }
}

-- HANA
WHILE 1 = 1 DO
  i = (i + 1);
  IF i > 10 THEN
    CONTINUE;
  END IF;
END WHILE

-- INFORMIX
LOOP
  LET i = (i + 1);
  IF i > 10 THEN
    CONTINUE;
  END IF;
END LOOP

-- ORACLE, POSTGRES, YUGABYTEDB
LOOP
  i := (i + 1);
  CONTINUE WHEN i > 10;
END LOOP

-- SQLDATAWAREHOUSE, SQLSERVER
WHILE 1 = 1 BEGIN
  SET @i = (@i + 1);
  IF @i > 10
    CONTINUE;
END

-- ACCESS, ASE, AURORA_MYSQL, COCKROACHDB, DERBY, EXASOL, FIREBIRD, 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 : CONTINUE statement

previous : next