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
-- ACCESS, ASE, AURORA_MYSQL, COCKROACHDB, DERBY, EXASOL, 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
Feedback
Do you have any feedback about this page? We'd love to hear it!