Stored procedures and functions

This version of the manual is outdated. For the latest version, follow this link: http://www.jooq.org/doc/3.0/manual.

# Interaction with stored procedures

The full power of your database's vendor-specific extensions can hardly be obtained outside of the database itself. Most modern RDBMS support their own procedural language. With jOOQ, stored procedures are integrated easily

The main way to interact with your RDBMS's stored procedures and functions is by using the generated artefacts. See the manual's section about generating procedures and packages for more details about the source code generation for stored procedures and functions.

# Stored functions

When it comes to DSL, stored functions can be very handy in SQL statements as well. Every stored function (this also applies to FUNCTIONS in Oracle PACKAGES) can generate a Field representing a call to that function. Typically, if you have this type of function in your database:

CREATE OR REPLACE FUNCTION f_author_exists (author_name VARCHAR2)
RETURN NUMBER;

Then convenience methods like these are generated:

// Create a field representing a function with another field as parameter
public static Field<BigDecimal> fAuthorExists(Field<String> authorName) { // [...]

// Create a field representing a function with a constant parameter
public static Field<BigDecimal> fAuthorExists(String authorName) { // [...]

Let's say, you have a T_PERSON table with persons' names in it, and you want to know whether there exists an author with precisely that name, you can reuse the above stored function in a SQL query:

SELECT T_PERSON.NAME, F_AUTHOR_EXISTS(T_PERSON.NAME)
  FROM T_PERSON

-- OR:

SELECT T_PERSON.NAME
  FROM T_PERSON
 WHERE F_AUTHOR_EXISTS(T_PERSON.NAME) = 1

create.select(T_PERSON.NAME, Functions.fAuthorExists(T_PERSON.NAME))
      .from(T_PERSON);

// OR: Note, the static import of Functions.*
create.select(T_PERSON.NAME)
      .from(T_PERSON)
      .where(fAuthorExists(T_PERSON.NAME));

# Stored procedures

The notion of a stored procedure is implemented in most RDBMS by the fact, that the procedure has no RETURN VALUE (like void in Java), but it may well have OUT parameters. Since there is not a standard way how to embed stored procedures in SQL, they cannot be integrated in jOOQ's DSL either.