Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
Stored procedures and functions
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Many RDBMS support the concept of "routines", usually calling them procedures and/or functions. These concepts have been around in programming languages for a while, also outside of databases. Famous languages distinguishing procedures from functions are:
- Ada
- BASIC
- Pascal
- etc...
The general distinction between (stored) procedures and (stored) functions can be summarised like this:
Procedures
- Are called using JDBC CallableStatement
- Have no return value
- Usually support OUT parameters
Functions
- Can be used in SQL statements
- Have a return value
- Usually don't support OUT parameters
Exceptions to these rules
- DB2, H2, and HSQLDB don't allow for JDBC escape syntax when calling functions. Functions must be used in a SELECT statement
- H2 only knows functions (without OUT parameters)
- Oracle functions may have OUT parameters
- Oracle knows functions that must not be used in SQL statements for transactional reasons
- Postgres only knows functions (with all features combined). OUT parameters can also be interpreted as return values, which is quite elegant/surprising, depending on your taste
- The Sybase jconn3 JDBC driver doesn't handle null values correctly when using the JDBC escape syntax on functions
In general, it can be said that the field of routines (procedures / functions) is far from being standardised in modern RDBMS even if the SQL:2008 standard specifies things quite well. Every database has its ways and JDBC only provides little abstraction over the great variety of procedures / functions implementations, especially when advanced data types such as cursors / UDT's / arrays are involved.
To simplify things a little bit, jOOQ handles both procedures and functions the same way, using a more general org.jooq.Routine type.
Using jOOQ for standalone calls to stored procedures and functions
If you're using jOOQ's code generator, it will generate org.jooq.Routine objects for you. Let's consider the following example:
-- Check whether there is an author in AUTHOR by that name and get his ID CREATE OR REPLACE PROCEDURE author_exists (author_name VARCHAR2, result OUT NUMBER, id OUT NUMBER);
The generated artefacts can then be used as follows:
// Make an explicit call to the generated procedure object:
AuthorExists procedure = new AuthorExists();
// All IN and IN OUT parameters generate setters
procedure.setAuthorName("Paulo");
procedure.execute(configuration);
// All OUT and IN OUT parameters generate getters
assertEquals(new BigDecimal("1"), procedure.getResult());
assertEquals(new BigDecimal("2"), procedure.getId();
But you can also call the procedure using a generated convenience method in a global Routines class:
// The generated Routines class contains static methods for every procedure.
// Results are also returned in a generated object, holding getters for every OUT or IN OUT parameter.
AuthorExists procedure = Routines.authorExists(configuration, "Paulo");
// All OUT and IN OUT parameters generate getters
assertEquals(new BigDecimal("1"), procedure.getResult());
assertEquals(new BigDecimal("2"), procedure.getId();
For more details about code generation for procedures, see the manual's section about procedures and code generation.
Inlining stored function references in SQL
Unlike procedures, functions can be inlined in SQL statements to generate column expressions or table expressions, if you're using unnesting operators. Assume you have a function like this:
-- Check whether there is an author in AUTHOR by that name and get his ID CREATE OR REPLACE FUNCTION author_exists (author_name VARCHAR2) RETURN NUMBER;
The generated artefacts can then be used as follows:
-- This is the rendered SQL
SELECT AUTHOR_EXISTS('Paulo') FROM DUAL
// Use the static-imported method from Routines:
boolean exists =
create.select(authorExists("Paulo")).fetchOne(0, boolean.class);
For more info about inlining stored function references in SQL statements, please refer to the manual's section about user-defined functions.
References to this page
Feedback
Do you have any feedback about this page? We'd love to hear it!