One of the strengths of modern RDBMS is the capability to mix the powerful SQL language with procedural code.
SQL is a 4th generation programming language (4GL), and as such, extremely well suited for querying and bulk data manipulation. Its functional-declarative nature allows for it to be optimised in highly efficient ways using cost-based optimisation, but also statically as we’ve blogged before.
Sometimes, however, an imperative 3GL is better suited for a given task. That’s where stored procedures shine, or more specifically, procedural languages of RDBMS.
Among the ones that jOOQ supports, at least these ones support procedures:
- BigQuery
- Db2
- Exasol
- Firebird
- HANA
- HSQLDB
- Informix
- MariaDB
- MySQL
- Oracle
- PostgreSQL
- SQL Server
- Vertica
Others may do, as well, but jOOQ isn’t supporting their dialects yet.
Many have implemented their own procedural languages, some according to the ISO/IEC 9075-4 Persistent stored modules (SQL/PSM) standard, others have their own.
jOOQ support for procedural logic
Since jOOQ 3.12, our commercial distributions have supported anonymous blocks and the procedural statements they contain, such as the IF statement, LOOP statements, etc. Starting with jOOQ 3.15, we also support 3 types of statements to manage storing procedural logic in the catalog:
Using these statements via jOOQ may not be your every day use-case. You may prefer managing that logic via the native syntax, which is still more powerful than what jOOQ 3.15 supports (especially when you’re using Oracle’s PL/SQL), in case of which you’ll u se jOOQ purely to call your procedure from Java in the usual type safe manner.
But maybe, you have one of these use-cases?
- You’re a product vendor, and you profit from procedural logic being vendor agnostic in order to support multiple of your clients’ RDBMS
- Your procedural logic is dynamic, just like your SQL logic (and what other than jOOQ to use for that?)
- You don’t have the necessary privileges to create procedures, functions, or triggers in your schema
In all of those cases, jOOQ is here to help you.
How does it work?
The first building block is the anonymous block, which isn’t supported by all of the above dialects, regrettably. jOOQ can emulate it on MySQL as discussed here, but not currently in other dialects.
Here’s a simple, empty anonymous block:
-- Db2
BEGIN
END
-- Firebird
EXECUTE BLOCK AS
BEGIN
END
-- MariaDB
BEGIN NOT ATOMIC
END;
-- Oracle
BEGIN
NULL;
END;
-- PostgreSQL
DO $$
BEGIN
NULL;
END;
$$
It doesn’t really do much, but you can try executing it as follows, with jOOQ:
ctx.begin().execute();
Now, let’s do something more interesting, such as:
// Assuming the usual static imports:
import static org.jooq.impl.DSL.*;
import static org.jooq.impl.SQLDataType.*;
// Then write
Variable<Integer> i = variable(unquotedName("i"), INTEGER);
Table<?> t = table(unquotedName("t"));
Field<Integer> col = field(unquotedName("col"), INTEGER);
ctx.begin(
declare(i).set(1),
while_(i.le(10)).loop(
insertInto(t).columns(c).values(i),
i.set(i.plus(1))
)
).execute();
The above block executes:
-- Db2
BEGIN
DECLARE i integer;
SET i = 1;
WHILE i <= 10 DO
INSERT INTO t (c)
VALUES (i);
SET i = (i + 1);
END WHILE;
END
-- FIREBIRD
EXECUTE BLOCK AS
DECLARE i integer;
BEGIN
:i = 1;
WHILE (:i <= 10) DO BEGIN
INSERT INTO t (c)
VALUES (:i);
:i = (:i + 1);
END
END
-- MariaDB
BEGIN NOT ATOMIC
DECLARE i int;
SET i = 1;
WHILE i <= 10 DO
INSERT INTO t (c)
VALUES (i);
SET i = (i + 1);
END WHILE;
END;
-- Oracle
DECLARE
i number(10);
BEGIN
i := 1;
WHILE i <= 10 LOOP
INSERT INTO t (c)
VALUES (i);
i := (i + 1);
END LOOP;
END;
-- PostgreSQL
DO $$
DECLARE
i int;
BEGIN
i := 1;
WHILE i <= 10 LOOP
INSERT INTO t (c)
VALUES (i);
i := (i + 1);
END LOOP;
END;
$$
-- SQL Server
BEGIN
DECLARE @i int;
SET @i = 1;
WHILE @i <= 10 BEGIN
INSERT INTO t (c)
VALUES (@i);
SET @i = (@i + 1);
END;
END;
Easy as pie. Perhaps you prefer a FOR loop, instead? Try this:
ctx.begin(
for_(i).in(1, 10).loop(
insertInto(t).columns(c).values(i)
)
).execute();
It produces the necessary emulations, if required, because regrettably, not all dialects support FOR:
-- Db2
BEGIN
DECLARE i integer;
SET i = 1;
WHILE i <= 10 DO
INSERT INTO t (c)
VALUES (i);
SET i = (i + 1);
END WHILE;
END
-- Firebird
EXECUTE BLOCK AS
DECLARE i integer;
BEGIN
:i = 1;
WHILE (:i <= 10) DO BEGIN
INSERT INTO t (c)
VALUES (:i);
:i = (:i + 1);
END
END
-- MariaDB
BEGIN NOT ATOMIC
FOR i IN 1 .. 10 DO
INSERT INTO t (c)
VALUES (i);
END FOR;
END;
-- Oracle
BEGIN
FOR i IN 1 .. 10 LOOP
INSERT INTO t (c)
VALUES (i);
END LOOP;
END;
-- PostgreSQL
DO $$
BEGIN
FOR i IN 1 .. 10 LOOP
INSERT INTO t (c)
VALUES (i);
END LOOP;
END;
$$
-- SQL Server
BEGIN
DECLARE @i int;
BEGIN
SET @i = 1;
WHILE @i <= 10 BEGIN
INSERT INTO t (c)
VALUES (@i);
SET @i = (@i + 1);
END;
END;
END;
SQL vs procedures
Of course, this particular SQL statement would be better implemented using a single bulk insertion statement, purely with SQL, not with procedural logic
ctx.insertInto(t, c)
.select(selectFrom(generateSeries(1, 10)))
.execute();
Which translates to:
-- Db2
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM (
WITH
generate_series(generate_series) AS (
SELECT 1
FROM SYSIBM.DUAL
UNION ALL
SELECT (generate_series + 1)
FROM generate_series
WHERE generate_series < 10
)
SELECT generate_series
FROM generate_series
) generate_series;
-- Firebird
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM (
WITH RECURSIVE
generate_series(generate_series) AS (
SELECT 1
FROM RDB$DATABASE
UNION ALL
SELECT (generate_series + 1)
FROM generate_series
WHERE generate_series < 10
)
SELECT generate_series
FROM generate_series
) generate_series;
-- MariaDB
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM (
WITH RECURSIVE
generate_series(generate_series) AS (
SELECT 1
UNION ALL
SELECT (generate_series + 1)
FROM generate_series
WHERE generate_series < 10
)
SELECT generate_series
FROM generate_series
) AS generate_series;
-- Oracle
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM (
SELECT (level + (1 - 1)) generate_series
FROM DUAL
CONNECT BY level <= ((10 + 1) - 1)
) generate_series;
-- PostgreSQL
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM generate_series(1, 10);
-- SQL Server
WITH
generate_series(generate_series) AS (
SELECT 1
UNION ALL
SELECT (generate_series + 1)
FROM generate_series
WHERE generate_series < 10
)
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM (
SELECT generate_series
FROM generate_series
) generate_series
… but you get the point.
Storing the procedural logic
If you have the necessary privileges, and your procedural logic isn’t super dynamic, you may choose to store your logic in a procedure or function directly in your database. In some databases, this means a compiler will be able to eagerly translate the logic to something very efficient (e.g. machine code), instead of interpreting the logic on the fly.
Take the above WHILE loop, for example. You may want to store that as a procedure P:
Name p = unquotedName("p");
ctx.createProcedure(p)
.modifiesSQLData()
.as(
declare(i).set(1),
while_(i.le(10)).loop(
insertInto(t).columns(c).values(i),
i.set(i.plus(1))
)
)
.execute();
This produces the following statements:
-- Db2
CREATE PROCEDURE p()
MODIFIES SQL DATA
BEGIN
DECLARE i integer;
SET i = 1;
WHILE i <= 10 DO
INSERT INTO t (c)
VALUES (i);
SET i = (i + 1);
END WHILE;
END;
-- Firebird
CREATE PROCEDURE p()
AS
DECLARE i integer;
BEGIN
:i = 1;
WHILE (:i <= 10) DO BEGIN
INSERT INTO t (c)
VALUES (:i);
:i = (:i + 1);
END
END
-- MariaDB
CREATE PROCEDURE p()
MODIFIES SQL DATA
BEGIN
DECLARE i int;
SET i = 1;
WHILE i <= 10 DO
INSERT INTO t (c)
VALUES (i);
SET i = (i + 1);
END WHILE;
END;
-- Oracle
CREATE PROCEDURE p
AS
i number(10);
BEGIN
i := 1;
WHILE i <= 10 LOOP
INSERT INTO t (c)
VALUES (i);
i := (i + 1);
END LOOP;
END;
-- PostgreSQL
CREATE PROCEDURE p()
LANGUAGE plpgsql
AS
$$
DECLARE
i int;
BEGIN
i := 1;
WHILE i <= 10 LOOP
INSERT INTO t (c)
VALUES (i);
i := (i + 1);
END LOOP;
END;
$$
-- SQL Server
CREATE PROCEDURE p
AS
BEGIN
DECLARE @i int;
SET @i = 1;
WHILE @i <= 10 BEGIN
INSERT INTO t (c)
VALUES (@i);
SET @i = (@i + 1);
END;
END;
And now, what better way to call this procedure than, again, an anonymous block?
ctx.begin(call(unquotedName("p"))).execute();
Producing:
-- Db2
BEGIN
CALL p();
END
-- Firebird
EXECUTE BLOCK AS
BEGIN
EXECUTE PROCEDURE p;
END
-- MariaDB
BEGIN NOT ATOMIC
CALL p();
END;
-- Oracle
BEGIN
p();
END;
-- PostgreSQL
DO $$
BEGIN
CALL p();
END;
$$
-- SQL Server
BEGIN
EXEC p ;
END;
If you’re using jOOQ in Flyway or Liquibase to generate procedures during your database migrations, you can obviously generate jOOQ procedure stubs to call in a more type safe manner, instead of the above dynamic procedure call.
Parsing procedural logic
This jOOQ feature is not really exceptional. You can play around with our parser / translator here: https://www.jooq.org/translate. It can definitely help you translate your (simpler) stored procedures between dialects, such as PL/SQL, T-SQL, PL/pgSQL, etc.
Conclusion
As a rule of thumb, if you can do it with SQL (the 4GL), do it with SQL alone. But sometimes, you can’t. A 3GL is a better choice for an algorithm. When using jOOQ, you’ll naturally think of using Java to implement that 3GL algorithm. But wait, you could move the logic to the server for (drastically) increased performance!
Thanks to jOOQ, you can generate procedural logic that is:
- Dynamic
- Vendor agnostic
- Anonymous or stored
Just like you’re used to, from jOOQ, for SQL