All versions: 3.12 | Development versions: 3.13

Variables

Available in ❌ 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", SQLDataType.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();
The jOOQ Logo