The jOOQ User Manual SQL building Procedural statements Block statement

previous : next

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_1734679626395_453874 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_1734679626395_453874();
DROP ALIAS block_1734679626395_453874;

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_1734679631461_6844502()
MODIFIES SQL DATA
BEGIN
  DELETE FROM BOOK;
  DELETE FROM AUTHOR;
END;
CALL block_1734679631461_6844502();
DROP PROCEDURE block_1734679631461_6844502;

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

/* UNSUPPORTED */

The jOOQ User Manual SQL building Procedural statements Block statement

previous : next