Previously years, we’ve invested plenty of effort into bettering our procedural language capabilities in jOOQ. What began with a easy inside API to assist the emulations of DDL clauses like these:
-- Some dialect that helps this
create desk if not exists t (i varchar(10));
-- Db2
start
declare proceed handler for sqlstate '42710' start finish;
execute rapid 'create desk T (I varchar(10))';
finish
-- Oracle
start
execute rapid 'create desk T (I varchar2(10))';
exception
when others then
if sqlerrm like 'ORA-00955%' then null;
else increase;
finish if;
finish;
-- SQL Server
start strive
create desk T (I varchar(10))
finish strive
start catch
if error_number() != 2714 throw;
finish catch
… advanced right into a full fledged API for all kinds of procedural logic executed in your database server.
Nameless blocks
The above examples present what most RDBMS name “nameless blocks”, much like Java’s nameless courses, i.e. components of procedural logic that shouldn’t have a reputation.
Relying on the database, these blocks are interpreted on the fly, or compiled and cached like extraordinary SQL statements. They are often very helpful for a wide range of causes:
- To create atomic ad-hoc items of code, executed in a single server spherical journey, much like the above DDL scripts with built-in exception dealing with
- To create dynamic procedural code. This can be esoteric for a lot of, however precisely the correct factor to do for others. All of jOOQ is about dynamic SQL, so why not additionally dynamic PL/SQL, T-SQL, pgplsql, and so forth?
- To work round limitations imposed by Conway’s Legislation, when there is no such thing as a manner you will get the required GRANT or different bureaucratic token to deploy your process in manufacturing at your discretion. I imply, that is nonetheless a factor in plenty of firms.
- A lesser case of Conway’s Legislation could also be whenever you’re a product vendor, and also you don’t know if you happen to can create procedures in your shoppers’ manufacturing system. Simply don’t. Run your procedural logic as an nameless block if you happen to can’t, or as a process if you happen to can. Similar jOOQ code.
- In case your procedural code adjustments very ceaselessly (even dynamically), storing it would trigger tough points. For those who’ve ever labored with Oracle and bumped into the dreaded latch free occasion, you realize what I imply.
I’m under no circumstances advocating it is best to use nameless blocks over saved procedures normally. For those who can, retailer your code within the database for higher efficiency and re-use. However typically you may’t, and typically you shouldn’t.
So, jOOQ helps – as at all times – a mixture of numerous procedural logic components, together with:
- Blocks with variable declarations
IF
assertion- Loops together with
LOOP
,WHILE
,REPEAT
,FOR
loops EXIT
(orLEAVE
) andCONTINUE
(orITERATE
) for loop management circulateRETURN
to return from procedures or featuresGOTO
(ghasp!)SIGNAL
orRAISE
- Labels
CALL
assertion to name different saved proceduresEXECUTE
assertion (for working dynamic SQL from inside procedural logic. Which stage of inception is that?)
And we’re including extra assist on a regular basis. The Java code would possibly look one thing like this:
Variable<Integer> i = var(title("i"), INTEGER);
ctx.start(
for_(i).in(1, 10).loop(
insertInto(T).columns(T.COL).values(i)
)
).execute();
Assuming you can not run a bulk insert assertion for some purpose, this could be the best way to go. It interprets to varied dialects as follows.
Db2 and MySQL (which doesn’t assist nameless blocks, however assertion batches)
start
declare I bigint;
set I = 1;
whereas I <= 10 do
insert into T (COL) values (I);
set I = (I + 1);
finish whereas;
finish;
PostgreSQL
do $$
start
for I in 1 .. 10 loop
insert into T (COL) values (I);
finish loop;
finish;
$$
Oracle
start
for I in 1 .. 10 loop
insert into T (COL) values (I);
finish loop;
finish;
SQL Server
start
declare @I bigint = 1;
whereas @I <= 10 start
insert into T (COL) values (I);
set @I = (@I + 1);
finish;
finish;
As at all times with jOOQ, you don’t have to start out out with writing jOOQ API based mostly code. Whereas that is the really helpful method when your procedural (or SQL) logic is dynamic, jOOQ can even parse and translate static SQL in string type. The babelfish of SQL. Mess around with it right here to study extra: https://www.jooq.org/translate/
Storing the code as a process
For those who don’t have any of the above use-cases, it would be best to retailer this code as a process (or perform):
- For larger re-use
- For higher efficiency
In that case, ranging from jOOQ 3.15, you need to use our CREATE PROCEDURE
, CREATE FUNCTION
, and even CREATE TRIGGER
assist.
Be aware:
CREATE PACKAGE
is excessive on our want checklist, however may not make it into 3.15 anymore. If packages are used for namespacing solely, they could be emulated utilizing schemas in different dialects. Different bundle stage options, akin to bundle state could also be tougher to translate.
The earlier nameless block might be simply wrapped in a DSLContext.createProcedure()
name
Variable<Integer> i = var("i", INTEGER);
Parameter<Integer> i1 = in("i1", INTEGER);
Parameter<Integer> i2 = in("i2", INTEGER);
ctx.createProcedure("insert_into_t")
.parameters(i1, i2)
// Chances are you'll or could not wrap your block in BEGIN .. END.
// jOOQ will determine it out per dialect...
.as(for_(i).in(i1, i2).loop(
insertInto(T).columns(T.COL).values(i)
))
.execute();
Which might produce the next procedures:
Db2 and MySQL
create process INSERT_INTO_T(
I1 integer,
I2 integer
)
start
declare I bigint;
set I = I1;
whereas I <= I2 do
insert into T (COL) values (I);
set I = (I + 1);
finish whereas;
finish;
MariaDB
create process INSERT_INTO_T(
I1 int,
I2 int
)
start
for I in I1 .. I2 do
insert into T (COL) values (I);
finish for;
finish;
Oracle
create process INSERT_INTO_T(
I1 quantity,
I2 quantity
)
as
start
for I in I1 .. I2 loop
insert into T (COL) values (I);
finish loop;
finish;
PostgreSQL
create process INSERT_INTO_T(
I1 int,
I2 int
)
language plpgsql
as
$$
start
for I in I1 .. I2 loop
insert into T (COL) values (I);
finish loop;
finish;
$$
SQL Server
create process INSERT_INTO_T
@I1 int,
@I2 int
as
start
declare @I bigint = @I1;
whereas @I <= @I2 start
insert into T (COL) values (@I);
set @I = (@I + 1);
finish;
finish;
Mess around with it right here to study extra: https://www.jooq.org/translate/. We’re additionally wanting ahead to your bug reviews and/or function requests right here: https://github.com/jOOQ/jOOQ/points/new/select.
Tough transformations
Procedural languages are standardised through the ISO/IEC 9075-4 commonplace, and a few RBDMS surprisingly comply with a big extent on the usual, together with:
Others do much less so, however all procedural languages agree on the truth that they’re quite simple languages, with none such “fancy” issues like subtype or parametric polymorphism (OK, PL/SQL has some subtype polymorphism, however not a really refined one. We gained’t assist it for now), lambda expressions, dynamic dispatch, algebraic information varieties, and so forth. and so forth.
What they do have in widespread is a decent integration with the SQL language, which is the place they shine.
However there are delicate variations, nonetheless. For instance, they differ in the place you may declare variables. Some have block scope, others don’t. And a few adhere to the usual, the place LEAVE
requires a label, others don’t.
Think about you write this “fantasy” jOOQ code
Identify t = unquotedName("t");
Identify a = unquotedName("a");
Variable<Integer> i = var(unquotedName("i"), INTEGER);
ctx.start(
insertInto(t).columns(a).values(1),
declare(i).set(2),
loop(
insertInto(t).columns(a).values(i),
i.set(i.plus(1)),
if_(i.gt(10)).then(loop(exit()), exit())
)
)
.execute();
That is only a extra difficult model of the unique loop, which inserts values 1-10 right into a desk. There’s no purpose apart from to indicate off the transformation capabilities for the nesting of loop(exit())
, in addition to the infinite LOOP
with EXIT
utilization, reasonably than the listed FOR
loop.
There are some things that don’t at all times work precisely like this in some dialects!
Let’s take a look at what Db2 does with this.
start
-- Variable declarations must be "pulled up" to the start
-- of the block, i.e. earlier than the INSERT assertion
declare i integer;
insert into t (a) values (1);
-- Whereas irrelevant to this instance, the init worth for the
-- variable declaration should stay on the unique location
set i = 2;
-- We'd like a label to have the ability to go away this loop
alias_1:
loop
insert into t (a) values (i);
set i = (i + 1);
if i > 10 then
-- Similar right here, a label is required
alias_2:
loop
go away alias_2;
finish loop;
go away alias_1;
finish if;
finish loop;
finish
If we don’t use EXIT
on a loop, then there gained’t be a label. Or, you may clearly label your loops explicitly, which is at all times really helpful. However typically, you don’t have that in your unique supply code.
What does Oracle do with this?
Oracle has a barely completely different syntax right here:
declare
i quantity(10);
start
insert into t (a) values (1);
i := 2;
loop
insert into t (a) values (i);
i := (i + 1);
if i > 10 then
loop
exit;
finish loop;
exit;
finish if;
finish loop;
finish;
The principle distinction being that the declaration can be pulled up, however a separate DECLARE
block is required to declare variables outdoors of BEGIN .. END
. Label-less EXIT
is supported natively, so nothing must be reworked right here.
For those who’re considering how this transformations work, learn this submit.
Conclusion
Whether or not you’re migrating off one dialect onto one other, or whether or not you’re supporting a number of dialects without delay, otherwise you’re writing dynamic SQL and dynamic procedural logic, otherwise you similar to writing issues in Java reasonably than native SQL, otherwise you endure from Conway’s Legislation and can’t retailer your procedural code simply, jOOQ can assist you with these endeavours.
For some time now, jOOQ has supported procedural statements as nameless blocks for the most well-liked dialects. Ranging from jOOQ 3.15, we’ll additionally assist storing this logic within the database in a dialect agnostic manner, in addition to parsing / translating procedural code on our web site, or as a library / CLI or JDBC proxy to switch your SQL / procedural code ad-hoc in a legacy JDBC software.
Keep tuned for extra on this thrilling space of jOOQ growth!