The jOOQ User Manual. Multiple Pages : SQL building : Column expressions : User-defined functions | previous : next |
All versions: 3.11 | 3.10 | 3.9 | 3.8 | 3.7 | Development versions: 3.12 | Unsupported versions: 3.6 | 3.5 | 3.4 | 3.3 | 3.2 | 2.6
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