|The jOOQ User Manual. Multiple Pages : SQL building : Procedural statements : Variables||previous : next|
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.
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
-- 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.
-- 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;
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();