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_1732026443879_6895148 AS $$
void x(Connection c) throws SQLException {
Integer i = null;
i = 1;
}
$$;
CALL block_1732026443879_6895148();
DROP ALIAS block_1732026443879_6895148;
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_1732026450649_7458796() MODIFIES SQL DATA BEGIN DECLARE i int; SET i = 1; END; CALL block_1732026450649_7458796(); DROP PROCEDURE block_1732026450649_7458796;
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 */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!