New versions: Dev (3.16) | Latest (3.15) | 3.14 | 3.13 | 3.12

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))

The above is equivalent to this:

-- PL/SQL syntax
SELECT MAX(col) INTO i FROM t;
// All dialects
select(max(T.COL)).into(i).from(T)

Row assignment

Some dialects support row assignment of variables, which other languages call "destructuring". This is particularly useful when assigning multiple values from a query:

-- HSQLDB syntax
SET (i, j) = (SELECT MIN(col), MAX(col) FROM t);
// All dialects
row(i, j).set(select(min(T.COL), max(T.COL)).from(T))

The above is equivalent to this:

-- PL/SQL syntax
SELECT MIN(col), MAX(col) INTO i, j FROM t;
// All dialects
select(min(T.COL), max(T.COL)).into(i, j).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_1631889888348_7451329 AS $$
  void x(Connection c) throws SQLException {
    Integer i = null;
    i = 1;
  }
$$;
CALL block_1631889888348_7451329();
DROP ALIAS block_1631889888348_7451329;

-- 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_1631889888349_4440714()
MODIFIES SQL DATA
BEGIN
  DECLARE i int;
  SET i = 1;
END;
CALL block_1631889888349_4440714();
DROP PROCEDURE block_1631889888349_4440714;

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

-- POSTGRES, YUGABYTE
DO $$
DECLARE
  i int;
BEGIN
  i := 1;
END;
$$

-- SQLDATAWAREHOUSE, SQLSERVER
BEGIN
  DECLARE @i int;
  SET @i = 1;
END;

-- ACCESS, ASE, AURORA_MYSQL, COCKROACHDB, DERBY, IGNITE, MEMSQL, REDSHIFT, SNOWFLAKE, SQLITE, SYBASE, TERADATA, VERTICA
/* UNSUPPORTED */

(These are currently generated with jOOQ 3.16, see #10141), or translate your own on our website

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo