The jOOQ User Manual SQL building Procedural statements Variables

previous : next

Variables

Applies to ❌ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

In imperative languages, local variables are an essential way of temporarily storing data for further processing. All procedural languages have a way to declare, assign, and reference such local variables.

Declaration

In jOOQ, local variable expressions can be created using DSL.var() (not to be confused with DSL.val(T), which creates bind values!)

Variable<Integer> i = var("i", INTEGER);

This variable doesn't do anything on its own yet. But like many things in jOOQ, it has to be declared first, outside of an actual jOOQ expression, in order to be usable in jOOQ expressions.

We can now reference the variable in a declaration statement as follows:

-- MySQL syntax
DECLARE i INTEGER;

// All dialects
declare(i)

Notice that there are many different ways to declare a local variable in different dialects. There is

The Oracle PL/SQL, PostgreSQL pgplsql style

In these languages, the DECLARE statement is actually not an independent statement that can be used anywhere. It is part of a procedural block, prepended to BEGIN .. END:

-- PL/SQL syntax
DECLARE
  i INT;
BEGIN
  NULL;
END;

When using jOOQ, you can safely ignore this fact, and prepend that there is a DECLARE statement also in these dialects. jOOQ will add additional BEGIN .. END blocks to your surrounding block, to make sure the whole block becomes syntactically and semantically correct.

The T-SQL, MySQL style

In these languages, the DECLARE statement is really an independent statement that can be used anywhere. Just like in the Java language, variables can be declared at any position and used only "further down", lexically. Ignoring T-SQL's JavaScript-esque understanding of scope for a moment.

-- T-SQL syntax
DECLARE @i INTEGER;

Notice that you can safely ignore the @ sign that is required in some dialects, such as T-SQL. jOOQ will generate it for you.

Assignment

A local variable needs a way to have a value assigned to it. Assignments are possible both on org.jooq.Variable, or on org.jooq.Declaration, directly. For example

-- T-SQL syntax
DECLARE @i INTEGER = 1;

// All dialects
declare(i).set(1)

Alternatively, you can split declaration and assignment, or re-assign new values to variables:

-- T-SQL syntax
DECLARE @i INTEGER;
SET @i = 1;
SET @i = 2;

// All dialects
declare(i),
i.set(1),
i.set(2)

Some dialects also support using subqueries in assignment expressions, and other expresions in their procedural languages. For example:

-- T-SQL syntax
SET @i = (SELECT MAX(col) FROM t);

// All dialects
i.set(select(max(T.COL)).from(T))

In PL/SQL, the above can be emulated as follows:

-- PL/SQL syntax
SELECT MAX(col)
INTO i
FROM t;

Referencing

Obviously, once we've assigned a value to a local variable, we want to reference it as well in arbitrary expressions, and queries.

For this purpose, org.jooq.Variable extends org.jooq.Field, and as such, can be used in arbitrary places where any other column expression can be used. Within the procedural language, a simple example would be to increment a local variable:

-- PL/SQL syntax
i := i + 1;

// All dialects
i.set(i.plus(1))

Or in a more complete example, use it in a SQL statement:

-- PL/SQL syntax
DECLARE
  i INT;
BEGIN
  i := 1;
  INSERT INTO t (col) VALUES (i);
END;

// All dialects
Variable<Integer> i = var("i", INTEGER);
create.begin(
  declare(i),
  i.set(1),
  insertInto(T).columns(T.COL).values(i)
).execute();

Dialect support

This example using jOOQ:

begin(declare(i), i.set(1))

Translates to the following dialect specific expressions:

Aurora Postgres

DO $$
DECLARE
  DECLARE i int;
BEGIN
  SET i = 1;
END;
$$

BigQuery

BEGIN
  DECLARE i int64;
  SET i = 1;
END;

DB2

BEGIN
  DECLARE i integer;
  SET i = 1;
END

Exasol

BEGIN
  i int;
  i := 1;
END;

Firebird

EXECUTE BLOCK AS
  DECLARE i integer;
BEGIN
  :i = 1;
END

H2

CREATE ALIAS block_1732025857617_2919744 AS $$
  void x(Connection c) throws SQLException {
    Integer i = null;
    i = 1;
  }
$$;
CALL block_1732025857617_2919744();
DROP ALIAS block_1732025857617_2919744;

Hana

DO BEGIN
  DECLARE i integer;
  i = 1;
END;

HSQLDB

BEGIN ATOMIC
  DECLARE i int;
  SET i = 1;
END;

Informix

BEGIN
  DEFINE i integer;
  LET i = 1;
END;

MariaDB

BEGIN NOT ATOMIC
  DECLARE i int;
  SET i = 1;
END;

MySQL

CREATE PROCEDURE block_1732025863141_9223689()
MODIFIES SQL DATA
BEGIN
  DECLARE i int;
  SET i = 1;
END;
CALL block_1732025863141_9223689();
DROP PROCEDURE block_1732025863141_9223689;

Oracle

DECLARE
  i number(10);
BEGIN
  i := 1;
END;

Postgres, YugabyteDB

DO $$
DECLARE
  i int;
BEGIN
  i := 1;
END;
$$

SQLDataWarehouse, SQLServer

BEGIN
  DECLARE @i int;
  SET @i = 1;
END;

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

/* UNSUPPORTED */

The jOOQ User Manual SQL building Procedural statements Variables

previous : next