Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Most RDBMS support some sort of procedural language that allows for running imperative code inside of the database. The syntax of these languages is often similar, and hence it is supported and standardised by jOOQ as well.
In some databases, the procedural language can exist in the form of anonymous blocks, i.e. ad-hoc programs that are interpreted (or compiled) on the fly. In most RDBMS, however, the main approach to using the procedural languages is to store the procedural logic in stored procedures or functions, such that they can be pre-compiled and translated to native code for performance reasons.
The jOOQ API currently supports anonymous blocks only. In some dialects where anonymous blocks are not supported, a procedure is stored, called, and dropped again as an emulation.
An example of an anonymous block that inserts values 1 - 10 into a table:
-- PL/SQL syntax BEGIN FOR i IN 1 .. 10 LOOP INSERT INTO t (col) VALUES (i); END LOOP; END;
Variable<Integer> i = var(name("i"), INTEGER); create.begin( for_(i).in(1, 10).loop( insertInto(T).columns(T.COL).values(i) ) ).execute();
The entire loop is executed on the server, which may greatly help reduce client server round trips.
Apart from the block statement, this feature set is available only in our commercial distributions.
- Block statement
- CALL statement (new)
- CONTINUE statement
- EXECUTE statement
- EXIT statement
- FOR statement
- GOTO statement
- IF statement
- LOOP statement
- REPEAT statement
- SIGNAL (new)
- WHILE statement
|The jOOQ User Manual : SQL building : Procedural statements||previous : next|