Block statement
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The most basic building block in procedural languages is the block statement, which allows for creating scope (except for T-SQL, which has no block scope), and for logically grouping related statement together. Just like in Java, where any set of statements can be grouped using curly braces: { statment1; statement2; }
, in procedural languages, usually, the keywords BEGIN
and END
are used to delimit a block. For example:
BEGIN INSERT INTO t (col) VALUES (1); INSERT INTO t (col) VALUES (2); END;
create.begin( insertInto(T).columns(T.COL).values(1), insertInto(T).columns(T.COL).values(2) ).execute();
Notice how jOOQ's DSLContext.begin(Statement...)
takes an ordinary varargs array (or collection) of org.jooq.Statement
as an argument. As such, the statements are comma separated, not semi colon separated. Also, it is important that statements passed to the procedural API do not call the Query.execute()
method, as that would execute a statement in the client, rather than embedding a statement expression in a block.
Just like in SQL, such blocks can be nested with any depth, e.g.
BEGIN BEGIN INSERT INTO t (col) VALUES (1); INSERT INTO t (col) VALUES (2); END; BEGIN INSERT INTO t (col) VALUES (3); INSERT INTO t (col) VALUES (4); END; END;
create.begin( begin( insertInto(T).columns(T.COL).values(1), insertInto(T).columns(T.COL).values(2) ), begin( insertInto(T).columns(T.COL).values(3), insertInto(T).columns(T.COL).values(4) ) ).execute();
Client side "blocks"
In some cases, it may be desireable to group several statements in a "block" in the client only, without producing the BEGIN
and END
keywords on the server, in case it is not needed. This can be done using DSLContext.statements(Statement...)
.
INSERT INTO t (col) VALUES (1); INSERT INTO t (col) VALUES (2);
statements( insertInto(T).columns(T.COL).values(1), insertInto(T).columns(T.COL).values(2))
This API is useful whenever you want to group several statements into one logical org.jooq.Statement
and let jOOQ figure out if BEGIN .. END
block syntax is required or not. If it is required, then they are added - e.g. when the block is executed on the top level, or nested inside an IF statement, in case the IF
statement doesn't already have its own THEN
keyword to delimit multi-statement content.
Block execution
org.jooq.Block
extends org.jooq.Query
, which in turn extends org.jooq.Statement
. A Query
is a statement that can be executed on its own, as a standalone executable.
All other org.jooq.Statement
types (as explained in the following sections) cannot be executed on their own. For example, it makes no sense to execute a GOTO statement outside of a statement block.
Dialect support
This example using jOOQ:
begin(deleteFrom(BOOK), deleteFrom(AUTHOR))
Translates to the following dialect specific expressions:
Aurora Postgres, Postgres, YugabyteDB
DO $$ BEGIN DELETE FROM BOOK; DELETE FROM AUTHOR; END; $$
BigQuery
BEGIN DELETE FROM BOOK WHERE TRUE; DELETE FROM AUTHOR WHERE TRUE; END;
DB2
BEGIN DELETE FROM BOOK; DELETE FROM AUTHOR; END
Exasol, Informix, Oracle, SQLDataWarehouse, SQLServer, Teradata, Vertica
BEGIN DELETE FROM BOOK; DELETE FROM AUTHOR; END;
Firebird
EXECUTE BLOCK AS BEGIN DELETE FROM BOOK; DELETE FROM AUTHOR; END
H2
CREATE ALIAS block_1732884011998_5956672 AS $$ void x(Connection c) throws SQLException { try (PreparedStatement s = c.prepareStatement( "DELETE FROM BOOK" )) { s.execute(); } try (PreparedStatement s = c.prepareStatement( "DELETE FROM AUTHOR" )) { s.execute(); } } $$; CALL block_1732884011998_5956672(); DROP ALIAS block_1732884011998_5956672;
Hana
DO BEGIN DELETE FROM BOOK; DELETE FROM AUTHOR; END;
HSQLDB
BEGIN ATOMIC DELETE FROM BOOK; DELETE FROM AUTHOR; END;
MariaDB
BEGIN NOT ATOMIC DELETE FROM BOOK; DELETE FROM AUTHOR; END;
MySQL
CREATE PROCEDURE block_1732884019048_8118877() MODIFIES SQL DATA BEGIN DELETE FROM BOOK; DELETE FROM AUTHOR; END; CALL block_1732884019048_8118877(); DROP PROCEDURE block_1732884019048_8118877;
ASE, Access, Aurora MySQL, ClickHouse, CockroachDB, Derby, DuckDB, MemSQL, Redshift, SQLite, Snowflake, Sybase, Trino
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
References to this page
- Dollar quoted string token
- SQL Statements (DDL)
- CREATE PROCEDURE
- DROP DATABASE IF EXISTS
- DROP DOMAIN IF EXISTS
- DROP FUNCTION IF EXISTS
- DROP INDEX IF EXISTS
- DROP PROCEDURE IF EXISTS
- DROP SCHEMA IF EXISTS
- DROP SEQUENCE IF EXISTS
- DROP TABLE IF EXISTS
- DROP TRIGGER IF EXISTS
- DROP TYPE IF EXISTS
- DROP VIEW IF EXISTS
- Transactional statements
- Procedural statements
- CONTINUE statement
- EXIT statement
- IF statement
- Variables
Feedback
Do you have any feedback about this page? We'd love to hear it!