Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12

EXIT 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).

For example, in the absence of more sophisticated LOOP syntaxes, you may choose to exit a loop using EXIT (which translates to LEAVE or BREAK in some dialects, and works the same way as Java's break):

Without a label

-- PL/SQL
LOOP
  i := i + 1;
  EXIT WHEN i > 10;
END LOOP;
// All dialects
loop(
  i.set(i.plus(1)),
  exitWhen(i.gt(10))
)

With a label

-- PL/SQL
<<label>>
LOOP
  i := i + 1;
  EXIT label WHEN i > 10;
END LOOP;
// All dialects
Label label = label("label");
label.label(loop(
  i.set(i.plus(1)),
  exit(label).when(i.gt(10))
))

Dialect support

This example using jOOQ:

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

Translates to the following dialect specific expressions:

Aurora Postgres

LOOP
  SET i = (i + 1);
  EXIT WHEN i > 10;
END LOOP

BigQuery

LOOP
  SET i = (i + 1);
  IF i > 10 THEN
    BREAK;
  END IF;
END LOOP

DB2, HSQLDB, MariaDB, MySQL

alias_1:
LOOP
  SET i = (i + 1);
  IF i > 10 THEN
    LEAVE alias_1;
  END IF;
END LOOP

Firebird

WHILE (1 = 1) DO BEGIN
  :i = (:i + 1);
  IF (:i > 10) THEN
    LEAVE;
END

H2

for (;;) {
  i = (i + 1);
  if (i > 10) {
    break;
  }
}

Hana

WHILE 1 = 1 DO
  i = (i + 1);
  IF i > 10 THEN
    BREAK;
  END IF;
END WHILE

Informix

LOOP
  LET i = (i + 1);
  EXIT WHEN i > 10;
END LOOP

Oracle, Postgres, YugabyteDB

LOOP
  i := (i + 1);
  EXIT WHEN i > 10;
END LOOP

SQLDataWarehouse, SQLServer

WHILE 1 = 1 BEGIN
  SET @i = (@i + 1);
  IF @i > 10
    BREAK;
END

ASE, Access, Aurora MySQL, ClickHouse, CockroachDB, Derby, DuckDB, Exasol, MemSQL, Redshift, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica

/* UNSUPPORTED */

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

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo