In the past years, we’ve invested a lot of effort into improving our procedural language capabilities in jOOQ. What started with a simple internal API to support the emulations of DDL clauses like these:
-- Some dialect that supports this create table if not exists t (i varchar(10)); -- Db2 begin declare continue handler for sqlstate '42710' begin end; execute immediate 'create table T (I varchar(10))'; end -- Oracle begin execute immediate 'create table T (I varchar2(10))'; exception when others then if sqlerrm like 'ORA-00955%' then null; else raise; end if; end; -- SQL Server begin try create table T (I varchar(10)) end try begin catch if error_number() != 2714 throw; end catch
… evolved into a full fledged API for all sorts of procedural logic executed in your database server.
The above examples show what most RDBMS call “anonymous blocks”, similar to Java’s anonymous classes, i.e. elements of procedural logic that do not have a name.
Depending on the database, these blocks are interpreted on the fly, or compiled and cached like ordinary SQL statements. They can be very useful for a variety of reasons:
- To create atomic ad-hoc units of code, executed in a single server round trip, similar to the above DDL scripts with integrated exception handling
- To create dynamic procedural code. This may be esoteric for many, but exactly the right thing to do for others. All of jOOQ is about dynamic SQL, so why not also dynamic PL/SQL, T-SQL, pgplsql, etc?
- To work around limitations imposed by Conway’s Law, when there is no way you can get the necessary GRANT or other bureaucratic token to deploy your procedure in production at your discretion. I mean, this is still a thing in a lot of companies.
- A lesser case of Conway’s Law may be when you’re a product vendor, and you don’t know if you can create procedures on your clients’ production system. Just don’t. Run your procedural logic as an anonymous block if you can’t, or as a procedure if you can. Same jOOQ code.
- If your procedural code changes very frequently (even dynamically), storing it might cause tricky issues. If you’ve ever worked with Oracle and ran into the dreaded latch free event, you know what I mean.
I’m by no means advocating you should use anonymous blocks over stored procedures in general. If you can, store your code in the database for better performance and re-use. But sometimes you can’t, and sometimes you shouldn’t.
So, jOOQ supports – as always – a mix of various procedural logic elements, including:
- Blocks with variable declarations
- Loops including
ITERATE) for loop control flow
RETURNto return from procedures or functions
CALLstatement to call other stored procedures
EXECUTEstatement (for running dynamic SQL from within procedural logic. Which level of inception is that?)
And we’re adding more support all the time. The Java code might look something like this:
Variable<Integer> i = var(name("i"), INTEGER); ctx.begin( for_(i).in(1, 10).loop( insertInto(T).columns(T.COL).values(i) ) ).execute();
Assuming you cannot run a bulk insert statement for some reason, this might be the way to go. It translates to various dialects as follows.
Db2 and MySQL (which doesn’t support anonymous blocks, but statement batches)
begin declare I bigint; set I = 1; while I <= 10 do insert into T (COL) values (I); set I = (I + 1); end while; end;
do $$ begin for I in 1 .. 10 loop insert into T (COL) values (I); end loop; end; $$
begin for I in 1 .. 10 loop insert into T (COL) values (I); end loop; end;
begin declare @I bigint = 1; while @I <= 10 begin insert into T (COL) values (I); set @I = (@I + 1); end; end;
As always with jOOQ, you don’t have to start out with writing jOOQ API based code. While this is the recommended approach when your procedural (or SQL) logic is dynamic, jOOQ can also parse and translate static SQL in string form. The babelfish of SQL. Play around with it here to learn more: https://www.jooq.org/translate/
Storing the code as a procedure
If you don’t have any of the above use-cases, you will want to store this code as a procedure (or function):
- For greater re-use
- For better performance
CREATE PACKAGEis high on our wish list, but might not make it into 3.15 anymore. If packages are used for namespacing only, they might be emulated using schemas in other dialects. Other package level features, such as package state may be more difficult to translate.
The previous anonymous block can be easily wrapped in a
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) // You may or may not wrap your block in BEGIN .. END. // jOOQ will figure it out per dialect... .as(for_(i).in(i1, i2).loop( insertInto(T).columns(T.COL).values(i) )) .execute();
Which would produce the following procedures:
Db2 and MySQL
create procedure INSERT_INTO_T( I1 integer, I2 integer ) begin declare I bigint; set I = I1; while I <= I2 do insert into T (COL) values (I); set I = (I + 1); end while; end;
create procedure INSERT_INTO_T( I1 int, I2 int ) begin for I in I1 .. I2 do insert into T (COL) values (I); end for; end;
create procedure INSERT_INTO_T( I1 number, I2 number ) as begin for I in I1 .. I2 loop insert into T (COL) values (I); end loop; end;
create procedure INSERT_INTO_T( I1 int, I2 int ) language plpgsql as $$ begin for I in I1 .. I2 loop insert into T (COL) values (I); end loop; end; $$
create procedure INSERT_INTO_T @I1 int, @I2 int as begin declare @I bigint = @I1; while @I <= @I2 begin insert into T (COL) values (@I); set @I = (@I + 1); end; end;
Procedural languages are standardised via the ISO/IEC 9075-4 standard, and some RBDMS surprisingly agree to a large extent on the standard, including:
Others do less so, but all procedural languages agree on the fact that they are very simple languages, without any such “fancy” things like subtype or parametric polymorphism (OK, PL/SQL has some subtype polymorphism, but not a very sophisticated one. We won’t support it for now), lambda expressions, dynamic dispatch, algebraic data types, etc. etc.
What they do have in common is a tight integration with the SQL language, which is where they shine.
But there are subtle differences, nonetheless. For example, they differ in where you can declare variables. Some have block scope, others don’t. And some adhere to the standard, where
LEAVE requires a label, others don’t.
Imagine you write this “fantasy” jOOQ code
Name t = unquotedName("t"); Name a = unquotedName("a"); Variable<Integer> i = var(unquotedName("i"), INTEGER); ctx.begin( 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();
This is just a more complicated version of the original loop, which inserts values 1-10 into a table. There’s no reason other than to show off the transformation capabilities for the nesting of
loop(exit()), as well as the infinite
EXIT usage, rather than the indexed
There are a few things that don’t always work exactly like this in some dialects!
Let’s look at what Db2 does with this.
begin -- Variable declarations need to be "pulled up" to the beginning -- of the block, i.e. before the INSERT statement declare i integer; insert into t (a) values (1); -- While irrelevant to this example, the init value for the -- variable declaration must remain at the original location set i = 2; -- We need a label to be able to leave this loop alias_1: loop insert into t (a) values (i); set i = (i + 1); if i > 10 then -- Same here, a label is required alias_2: loop leave alias_2; end loop; leave alias_1; end if; end loop; end
If we don’t use
EXIT on a loop, then there won’t be a label. Or, you can obviously label your loops explicitly, which is always recommended. But sometimes, you don’t have that in your original source code.
What does Oracle do with this?
Oracle has a slightly different syntax here:
declare i number(10); begin insert into t (a) values (1); i := 2; loop insert into t (a) values (i); i := (i + 1); if i > 10 then loop exit; end loop; exit; end if; end loop; end;
The main difference being that the declaration is also pulled up, but a separate
DECLARE block is required to declare variables outside of
BEGIN .. END. Label-less
EXIT is supported natively, so nothing needs to be transformed here.
Whether you’re migrating off one dialect onto another, or whether you’re supporting several dialects at once, or you’re writing dynamic SQL and dynamic procedural logic, or you just like writing things in Java rather than native SQL, or you suffer from Conway’s Law and cannot store your procedural code easily, jOOQ can help you with those endeavours.
For a while now, jOOQ has supported procedural statements as anonymous blocks for the most popular dialects. Starting from jOOQ 3.15, we’ll also support storing this logic in the database in a dialect agnostic way, as well as parsing / translating procedural code on our website, or as a library / CLI or JDBC proxy to replace your SQL / procedural code ad-hoc in a legacy JDBC application.
Stay tuned for more in this exciting area of jOOQ development!