Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
User-defined functions
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Some databases support user-defined functions, which can be embedded in any SQL statement, if you're using jOOQ's code generator. Let's say you have the following simple function in Oracle SQL:
CREATE OR REPLACE FUNCTION echo (INPUT NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN INPUT;
END echo;
The above function will be made available from a generated Routines class. You can use it like any other column expression:
SELECT echo(1) FROM DUAL WHERE echo(2) = 2
create.select(echo(1)).where(echo(2).eq(2)).fetch();
Note that user-defined functions returning CURSOR or ARRAY data types can also be used wherever table expressions can be used, if they are unnested
Feedback
Do you have any feedback about this page? We'd love to hear it!