Available in versions: Dev (3.21) | Latest (3.20) | 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, Oracle, Postgres, YugabyteDB
LOOP 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
Snowflake
LOOP
i := (:i + 1);
IF (:i > 10) THEN
BREAK;
END IF;
END LOOP
SQLDataWarehouse, SQLServer
WHILE 1 = 1 BEGIN
SET @i = (@i + 1);
IF @i > 10
BREAK;
END
ASE, Access, Aurora MySQL, ClickHouse, CockroachDB, Databricks, Derby, DuckDB, Exasol, MemSQL, Redshift, SQLite, Sybase, Teradata, Trino, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!