Chapter 15: Calling and Creating Stored Functions and Procedures

SQL is a declarative language, but it also has procedural features such as stored functions/procedures, triggers, and cursors, which means SQL is considered to be a Fourth-Generation Programming Language (4GL). In this chapter, we will see how to call and create stored functions/procedures, or in other words, how to call and create Persistent Stored Modules (SQL/PSM) for MySQL, PostgreSQL, SQL Server, and Oracle.

Just in case you need a quick reminder about the key differences between the stored procedures and functions, check out the following head-to-head table (some of these differences are entirely or partially true depending on the database):

Figure 15.1 – Key differences between procedures and functions

Figure 15.1 – Key differences between procedures and functions

As you can infer from the previous comparison, the main difference is that procedures (may) produce a side effect, whereas functions are (generally) expected not to.

So, our agenda includes the following topics:

  • Calling stored functions/procedures from jOOQ
  • Stored procedures
  • Creating stored functions/procedures via jOOQ

Right before getting started, let's have some insight from Lukas Eder who shared that: "This may come up later, but it might be worth mentioning early: there are some users who use jOOQ *only* for its stored procedure code generation capabilities. When you have a lot of stored procedures, it's almost impossible to bind to them without code generation, and jOOQ works very well out of the box, kind of like when you have a WSDL file (or something comparable), and you generate all the stubs with Axis or Metro, and so on."

OK, now let's get started!

Technical requirements

The code for this chapter can be found on GitHub at https://github.com/PacktPublishing/jOOQ-Masterclass/tree/master/Chapter15.

Calling stored functions/procedures from jOOQ

Once you start dealing with stored functions/procedures across different database vendors, you'll be hit by the lack of standardization. For instance, the vendor-specific syntax for expressing functions/procedures, the wide variety of vendor-specific functions/procedure types, and different ways of supporting and treating the output parameters are just a few non-standard aspects of stored functions/procedures.

Calling stored functions/procedures via plain JDBC code is not easy either, especially if advanced data types are involved (for instance, arrays or UDTs). But, as you already know, using the jOOQ DSL saves us from interacting directly with the JDBC API, so it saves us from making cutting-edge decisions regarding JDBC workarounds.

The jOOQ DSL represents stored functions/procedures via the org.jooq.Routine API, so there is a common API for both. Whenever the jOOQ generator detects a stored function/procedure it generates (among other things) a dedicated class in the proper package (in our case, jooq.generated.routines) that reflects its name (for instance, by default, a stored function named get_emps_in_office() results in a class named GetEmpsInOffice) and extends the jOOQ AbstractRoutine class. The generated class exposes the API needed to call this stored function/procedure via jOOQ DSL. Moreover, as you'll see soon, calling a stored function/procedure can also be done in an anonymous procedural block via DSLContext.begin() and directly via DSLContext.call(). But, enough theory, next let's tackle some different kinds of stored functions/procedures from the jOOQ perspective, and let's start with stored functions.

Stored functions

Stored functions return a result (for instance, the result of a computation). They can be called in SQL statements and, usually, they don't support output (OUT) parameters. However, in Oracle and PostgreSQL, stored functions may have output parameters that can be interpreted as returned results. Moreover, until version 11, PostgreSQL supports only stored functions that combine the features of stored functions and procedures. On the other hand, PostgreSQL 11 and beyond, Oracle, MySQL, and SQL Server distinguish between stored functions and procedures.

Next, let's see how we can call from jOOQ different kinds of stored functions expressed in one of these four dialects, and let's start by calling some scalar functions.

Scalar functions

A stored function that takes none, one, or more parameters, and returns a single value is commonly referred to as a scalar function. As a common practice, scalar functions encapsulate complex calculations that appear in many queries. Instead of expressing the calculation in every query, you can write a scalar function that encapsulates this calculation and uses it in each query. Roughly, the syntax of a scalar function is a variation of this skeleton:

CREATE FUNCTION name (parameters)
RETURNS data_type AS
BEGIN
    statements/computations
    RETURN value
END

For instance, a simple scalar function expressed in MySQL may look as follows:

DELIMITER $$
CREATE FUNCTION `sale_price`(
  `quantity` INT, `list_price` REAL, `fraction_of_price` REAL)
  RETURNS REAL
  DETERMINISTIC
  BEGIN
    RETURN (`list_price` - 
      (`list_price` * `fraction_of_price`)) * `quantity`;    
  END $$
DELIMITER ;

For this scalar function, the jOOQ Code Generator produces a dedicated class named jooq.generated.routines.SalePrice. Among its methods, this class exposes setters that allow us to provide the input parameters of the function. In our example, we will have setQuantity(Integer value), setQuantity(Field<Integer> field), setListPrice(Double value), setListPrice(Field<Double> field), setFractionOfPrice(Double value), and setFractionOfPrice(Field<Double> field). The function can be executed via jOOQ's well-known execute() methods. If the function already has a Configuration attached, then you can rely on execute() without parameters, otherwise use the execute(Configuration c) method as follows:

SalePrice sp = new SalePrice();
sp.setQuantity(25);
sp.setListPrice(15.5);
sp.setFractionOfPrice(0.75);
sp.execute(ctx.configuration());

Getting the returned scalar result can be done via the getReturnValue() method. In this case, you can use it like this:

double result = sp.getReturnValue();

As you just saw, each setter has a flavor that gets a Field as an argument. This means that we can write something like this (check the first two setters):

sp.setQuantity(field(select(PRODUCT.QUANTITY_IN_STOCK)
  .from(PRODUCT).where(PRODUCT.PRODUCT_ID.eq(1L))));
sp.setListPrice(field(select(PRODUCT.MSRP.coerce(Double.class))
  .from(PRODUCT).where(PRODUCT.PRODUCT_ID.eq(1L))));
sp.setFractionOfPrice(0.75);
sp.execute(ctx.configuration());
double result = sp.getReturnValue();

The previous examples are useful if the routine has more than 254 parameters (which isn't allowed in Java), if there are default parameters, which users don't want to set, or if the parameters need to be set dynamically. Otherwise, most probably, you'll prefer to use the static convenience API.

Writing these two examples in a more convenient/compact way can be done via the Routines.salePrice() static method. The jooq.generated.Routines class provides convenient static methods for accessing all stored functions/procedures that jOOQ has found in your database. In this case, the following two examples compact the previous examples (of course, you can shorten this example further by importing the jooq.generated.Routines.salePrice static):

double sp = Routines.salePrice(
    ctx.configuration(), 25, 15.5, 0.75);
Field<Float> sp = Routines.salePrice(
  field(select(PRODUCT.QUANTITY_IN_STOCK)
         .from(PRODUCT).where(PRODUCT.PRODUCT_ID.eq(1L))),
  field(select(PRODUCT.MSRP.coerce(Double.class))
         .from(PRODUCT).where(PRODUCT.PRODUCT_ID.eq(1L))),
  val(0.75));
double sp = ctx.fetchValue(salePrice(
 field(select(PRODUCT.QUANTITY_IN_STOCK)
  .from(PRODUCT).where(PRODUCT.PRODUCT_ID.eq(1L))),
 field(select(PRODUCT.MSRP.coerce(Double.class))
  .from(PRODUCT).where(PRODUCT.PRODUCT_ID.eq(1L))),
 val(0.75)));

Scalar functions can be used in queries as well. Here is an example via another flavor of Routines.salePrice():

ctx.select(ORDERDETAIL.ORDER_ID,
    sum(salePrice(ORDERDETAIL.QUANTITY_ORDERED,
        ORDERDETAIL.PRICE_EACH.coerce(Double.class), 
        val(0.75))).as("sum_sale_price"))
   .from(ORDERDETAIL)
   .groupBy(ORDERDETAIL.ORDER_ID)
   .orderBy(field(name("sum_sale_price")).desc())
   .fetch();

For MySQL, jOOQ renders the following plain SQL:

SELECT `classicmodels`.`orderdetail`.`order_id`,
   sum(`classicmodels`.`sale_price`(
       `classicmodels`.`orderdetail`.`quantity_ordered`, 
       `classicmodels`.`orderdetail`.`price_each`, 7.5E-1)) 
          AS `sum_sale_price`
   FROM `classicmodels`.`orderdetail`
   GROUP BY `classicmodels`.`orderdetail`.`order_id`
   ORDER BY `sum_sale_price` DESC

You can practice this example in ScalarFunction.

In this context, here is another function that was written for PostgreSQL that updates a PRODUCT.MSRP and returns it via UPDATE … RETURNING (do not confuse it with the function RETURN statement!):

CREATE OR REPLACE FUNCTION "update_msrp" (
    "id" BIGINT, "debit" INTEGER) RETURNS REAL AS $$
    UPDATE "public"."product"
        SET "msrp" = "public"."product"."msrp" - "debit"
        WHERE "public"."product"."product_id" = "id"
        RETURNING "public"."product"."msrp";
$$ LANGUAGE SQL;

jOOQ can call such a function as you saw in the previous example. For instance, here it is called in a SELECT:

ctx.select(PRODUCT.PRODUCT_ID, PRODUCT.PRODUCT_NAME, 
           PRODUCT.MSRP.as("obsolete_msrp"), 
           updateMsrp(PRODUCT.PRODUCT_ID, inline(50)))
   .from(PRODUCT).fetch();

And the rendered SQL is as follows:

SELECT "public"."product"."product_id",
       "public"."product"."product_name",
       "public"."product"."msrp" AS "obsolete_msrp",
               "public"."update_msrp"("id" := "public"
                   ."product"."product_id", "debit" := 50)
FROM "public"."product"

You can practice this example in UpdateFunction for PostgreSQL.

If your scalar functions are under Oracle, then you can take advantage of a nice Oracle feature known as scalar subquery caching. Basically, this feature renders the calls of stored functions wrapped in SQL statements as a scalar subquery. This feature avoids switching between PL/SQL and SQL contexts and this may result in better performance.

Consider the following scalar function:

CREATE OR REPLACE NONEDITIONABLE FUNCTION 
     "card_commission"("card_type" IN VARCHAR2)
RETURN NUMBER IS
 "commission" NUMBER := 0;
 BEGIN
   RETURN CASE "card_type"
     WHEN 'VisaElectron' THEN .15
     WHEN 'Mastercard' THEN .22
     ELSE .25
   END;
END;

Calling this scalar function in a query can be done here:

ctx.select(cardCommission(BANK_TRANSACTION.CARD_TYPE))
   .from(BANK_TRANSACTION)
   .fetch();
List<BigDecimal> commission = ctx.fetchValues(
  select(cardCommission(BANK_TRANSACTION.CARD_TYPE))
    .from(BANK_TRANSACTION));

But keep in mind Lukas Eder's note: "Just in case, scalar subquery caching isn't documented in Oracle, as far as I know. The context switch isn't avoided entirely, but it happens only once per scalar subquery input value, and thus per function argument value, and per query. So, instead of having 1 switch per row, we now have 1 switch per function input value."

Each time we execute this code, jOOQ renders a query that requires switching between PL/SQL and SQL contexts in order to execute the scalar function:

SELECT "CLASSICMODELS"."card_commission"(
       "CLASSICMODELS"."BANK_TRANSACTION"."CARD_TYPE")
FROM "CLASSICMODELS"."BANK_TRANSACTION"

But jOOQ has the withRenderScalarSubqueriesForStoredFunctions() flag-setting that is by default set to false. Once we set it to true, jOOQ turns on Oracle's scalar subquery caching feature. In the following example, we turn this feature on only for the current SQL:

ctx.configuration().derive(new Settings()
   .withRenderScalarSubqueriesForStoredFunctions(true))
   .dsl()
   .select(cardCommission(BANK_TRANSACTION.CARD_TYPE))
   .from(BANK_TRANSACTION)
   .fetch();

This time, the call of the cardCommission() stored function is rendered as a scalar subquery:

SELECT
  (SELECT "CLASSICMODELS"."card_commission"(
    "CLASSICMODELS"."BANK_TRANSACTION"."CARD_TYPE")
      FROM DUAL)
FROM "CLASSICMODELS"."BANK_TRANSACTION"

You can practice this example in ScalarSubqueryCaching for Oracle.

Functions returning arrays

PostgreSQL is a convenient way to write a function that returns an array. For instance, here is a function returning DEPARTMENT.TOPIC, which is declared in our schema as an array of type TEXT[]:

CREATE OR REPLACE FUNCTION "department_topic_arr" 
   (IN "id" BIGINT)
RETURNS TEXT[] 
AS $$
  SELECT "public"."department"."topic"
      FROM "public"."department" WHERE 
           "public"."department"."department_id" = "id"
$$ LANGUAGE SQL;

Calling this function in SELECT via the dedicated method, departmentTopicArr(), generated by jOOQ in Routines can be done by unnesting the returned array as in the following examples:

ctx.select().from(unnest(departmentTopicArr(2L))
   .as("t")).fetch();
ctx.fetch(unnest(departmentTopicArr(2L)).as("t"));

Next, let's take a function having an anonymous parameter (no explicit name) that builds and returns an array:

CREATE OR REPLACE FUNCTION "employee_office_arr"(VARCHAR(10))
  RETURNS BIGINT[] 
AS $$
  SELECT ARRAY(SELECT "public"."employee"."employee_number"
    FROM "public"."employee" WHERE "public"."employee"
         ."office_code" = $1)
$$ LANGUAGE sql;

This time, let's instantiate EmployeeOfficeArr, and let's pass the required parameter via a setter:

EmployeeOfficeArr eoa = new EmployeeOfficeArr();
eoa.set__1("1");
eoa.execute(ctx.configuration());
Long[] result = eoa.getReturnValue();

Since the function's parameter doesn't have a name, jOOQ has used its default implementation and generated a set__1() setter. If you had two no-name parameters, then jOOQ would generate set__1() and set__2(), and so on. In other words, jOOQ generates setters based on parameter positions starting from 1.

On the other hand, using the generated Routines.employeeOfficeArr() in a SELECT query can be done as follows:

ctx.select(field(name("t", "en")), sum(SALE.SALE_))
   .from(SALE)
   .rightJoin(unnest(employeeOfficeArr("1")).as("t", "en"))
     .on(field(name("t", "en")).eq(SALE.EMPLOYEE_NUMBER))
   .groupBy(field(name("t", "en"))).fetch();        

For PostgreSQL, jOOQ renders this SQL:

SELECT "t"."en",sum("public"."sale"."sale")
FROM "public"."sale"
RIGHT OUTER JOIN unnest("public"."employee_office_arr"('1')) 
AS "t" ("en") 
ON "t"."en" = "public"."sale"."employee_number"
GROUP BY "t"."en"

You can practice these examples in ArrayFunction for PostgreSQL.

Functions with output parameters

As we said earlier, PostgreSQL and Oracle allow output parameters in functions. Here is an example in PostgreSQL:

CREATE OR REPLACE FUNCTION "get_salary_stat"(
    OUT "min_sal" INT, OUT "max_sal" INT, OUT "avg_sal" NUMERIC) 
  LANGUAGE plpgsql
  AS $$
  BEGIN  
    SELECT MIN("public"."employee"."salary"),
           MAX("public"."employee"."salary"),
           AVG("public"."employee"."salary")::NUMERIC(7,2)
      INTO "min_sal", "max_sal", "avg_sal"
      FROM "public"."employee";
  END;
$$;

This function doesn't have a RETURN, but it has three OUT parameters that help us to obtain the results of execution. For each such parameter, jOOQ generates a getter, so we can call it via the generated jooq.generated.routines.GetSalaryStat like this:

GetSalaryStat salStat = new GetSalaryStat();
salStat.execute(ctx.configuration());
Integer minSal = salStat.getMinSal();
Integer maxSal = salStat.getMaxSal();
BigDecimal avgSal = salStat.getAvgSal();

This code (more precisely the execute() call) leads to the following SELECT (or CALL, in Oracle):

SELECT "min_sal", "max_sal", "avg_sal" 
FROM "public"."get_salary_stat"()

Here's the same result via Routines.getSalaryStat(Configuration c):

GetSalaryStat salStat = getSalaryStat(ctx.configuration());
// call the getters

Here are two more examples:

Integer minSal = ctx.fetchValue(val(getSalaryStat(
      ctx.configuration()).getMinSal()));
ctx.select(EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME,   
           EMPLOYEE.SALARY)
   .from(EMPLOYEE)
   .where(EMPLOYEE.SALARY.coerce(BigDecimal.class)
      .gt(getSalaryStat(ctx.configuration()).getAvgSal()))
   .fetch();

But pay attention that both of these examples lead to two SELECT statements (or, a CALL and a SELECT statement in Oracle) since functions with output parameters cannot be called from plain SQL. In other words, jOOQ calls the routine and fetches the OUT parameters as you can see next:

SELECT "min_sal","max_sal","avg_sal"
FROM "public"."get_salary_stat"()

And, afterward, it executes the SELECT that uses the results extracted from the output parameters. The following SELECT fits our second example (65652.17 is the average salary computed via getSalaryStat(Configuration c)):

SELECT "public"."employee"."first_name",
       "public"."employee"."last_name",
       "public"."employee"."salary"
FROM "public"."employee"
WHERE "public"."employee"."salary" > 65652.17

Lukas Eder shared: "In Oracle, functions with OUT parameters aren't "SQL callable," though... In PostgreSQL, they're just "syntax sugar" (or un-sugar, depending on taste) for a function returning a record."

You can practice these examples in InOutFunction for PostgreSQL (here, you can find an IN OUT example as well).

So, using OUT (or IN OUT) parameters in functions is not such a great idea and must be avoided. As Oracle mentioned, besides preventing a function from being used in SQL queries (more details here: https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1), the presence of output parameters in a function prevents a function from being marked as a DETERMINISTIC function or used as a result-cached function. Unlike PostgreSQL, Oracle functions having output parameters must have an explicit RETURN. Next to the jOOQ getters dedicated to output parameters, you can call getReturnValue() to obtain the result returned explicitly via the RETURN statement. When this book was written, functions with OUT parameters couldn't be called in jOOQ from plain SQL. Follow this feature request here: https://github.com/jOOQ/jOOQ/issues/3426.

You can practice an example in InOutFunction for Oracle (here, you can find an IN OUT example too).

Polymorphic functions

Some databases support the so-called polymorphic functions that accept and return polymorphic types. For instance, PostgreSQL supports the following polymorphic types: anyelement, anyarray, anynonarray, anyenum, and anyrange. Here is an example that builds an array from two passed arbitrary data type elements:

CREATE FUNCTION "make_array"(anyelement, anyelement) 
RETURNS anyarray 
AS $$
  SELECT ARRAY[$1, $2];
$$ LANGUAGE SQL;

Calling this function from jOOQ can be done as follows (notice the positional setters at work again):

MakeArray ma = new MakeArray();
ma.set__1(1);
ma.set__2(2);
ma.execute(ctx.configuration());

The returned result is org.postgresql.jdbc.PgArray:

PgArray arr = (PgArray) ma.getReturnValue();

In the bundled code you can see further processing of this array. For now, let's call make_array() from SELECT to build an array of integers and an array of strings:

ctx.select(makeArray(1, 2).as("ia"),
           makeArray("a", "b").as("ta")).fetch();

How about a function that combines polymorphic types and output parameters? Here is one:

CREATE FUNCTION "dup"(IN "f1" anyelement, 
   OUT "f2" anyelement, OUT "f3" anyarray)
AS 'select $1, array[$1,$1]' LANGUAGE sql;

Call it via jOOQ:

Dup dup = new Dup();
dup.setF1(10);
dup.execute(ctx.configuration());
// call here getF2() and/or getF3()

Or use it in a SELECT (remember from the previous section that this leads to two statements against the database):

ctx.select(val(dup(ctx.configuration(), 10).getF2())).fetch();
ctx.fetchValue(val(dup(ctx.configuration(), 10).getF2()));

You can practice these examples in PolymorphicFunction for PostgreSQL.

Functions returning explicit cursors

A function can return an explicit cursor (an explicit pointer that points to a result of a query) as well. Most probably, you're familiar with PostgreSQL REFCURSOR and Oracle SYS_REFCURSOR. Here is an example for Oracle:

CREATE OR REPLACE NONEDITIONABLE FUNCTION
   "GET_CUSTOMER" ("cl" IN NUMBER)
  RETURN SYS_REFCURSOR
       AS  "cur"  SYS_REFCURSOR;
  BEGIN
      OPEN "cur" FOR
           SELECT *
           FROM "CUSTOMER"
                       WHERE "CUSTOMER"."CREDIT_LIMIT" > "cl"
           ORDER BY "CUSTOMER"."CUSTOMER_NAME";
   
      RETURN "cur";
  END;

Calling this function via the generated jooq.generated.routines.GetCustomer can be done as follows:

GetCustomer customers = new GetCustomer();
customers.setCl(120000);
customers.execute(ctx.configuration());
Result<Record> result = customers.getReturnValue();

The result is mapped by jOOQ to Result<Record>, so a list of records is fitted entirely into memory. For better accommodation of large datasets, jOOQ has some pending feature requests to stream the cursors, so you can check the progress of issues #4503 and #4472 on the jOOQ repository. Alternatively, you can wrap the returned cursor in a table-valued function and fetch the results via a SELECT statement using jOOQ's ResultQuery.fetchLazy() method, as you saw in Chapter 8, Fetching and Mapping.

Now, going further, you can loop the Result<Record> and process each record, but to access a certain column (for instance, the customer name) of a certain row (for instance, the first row), then you can use getValue() or get() as here:

String name = (String) result.getValue(0, "CUSTOMER_NAME");
String name = result.get(0).get("CUSTOMER_NAME", String.class);

To get multiple names (or other columns) rely on getValues(), which comes in many flavors that you can find in the official documentation.

Obtaining the same Result<Record> can be done more compactly via the generated static Routines.getCustomer():

Result<Record> result = getCustomer(
   ctx.configuration(), 120000);

If you need a Table instead of this Result<Record>, then simply rely on org.jooq.impl.DSL.table as in the following two examples:

Table<?> t = table(result);
Table<CustomerRecord> t = table(result.into(CUSTOMER));

Next, you can use t in queries like any regular Table:

ctx.select(CUSTOMERDETAIL.ADDRESS_LINE_FIRST, 
           CUSTOMERDETAIL.POSTAL_CODE,
           t.field(name("CUSTOMER_NAME")))
   .from(t)
   .join(CUSTOMERDETAIL)
    .on(CUSTOMERDETAIL.CUSTOMER_NUMBER.eq(
        t.field(name("CUSTOMER_NUMBER"), Long.class)))
   .fetch();

On the other hand, another flavor of Routines.getCustomer() returns the result wrapped in a Field as Field<Result<Record>>. This allows us to use this result as a Field. For instance, here is a SELECT:

ctx.select(getCustomer(field(
     select(avg(CUSTOMER.CREDIT_LIMIT))
        .from(CUSTOMER)))).fetch();    

You can practice these examples in CursorFunction for Oracle.

How about a function that returns multiple cursors? Here is a sample that was written for PostgreSQL:

CREATE OR REPLACE FUNCTION "get_offices_multiple"() 
  RETURNS SETOF REFCURSOR 
AS $$
 DECLARE
  "ref1" REFCURSOR;           
  "ref2" REFCURSOR;           
 BEGIN
 OPEN "ref1" FOR 
  SELECT "public"."office"."city", "public"."office"."country" 
  FROM "public"."office" 
  WHERE "public"."office"."internal_budget" < 100000;  
  RETURN NEXT "ref1"; 
 OPEN "ref2" FOR 
  SELECT "public"."office"."city", "public"."office"."country"
  FROM "public"."office" 
  WHERE "public"."office"."internal_budget" > 100000;  
 RETURN NEXT "ref2";    
END;
$$ LANGUAGE plpgsql;

In this case, each cursor produces a Result<Record> wrapped in a generated Record class. Here, we have two cursors, therefore two Result<Record> wrapped in two instances of the generated GetOfficesMultipleRecord. When we call Routines.getOfficesMultiple(Configuration c) we get a Result<GetOfficesMultipleRecord> that can be expanded as follows:

Result<GetOfficesMultipleRecord> results = 
        getOfficesMultiple(ctx.configuration());
for (GetOfficesMultipleRecord result : results) {
  Result<Record> records = result.getGetOfficesMultiple();
  System.out.println("-------------------------");
  for (Record r : records) {
    System.out.println(r.get("city") + ", " + r.get("country"));
  }
}

You can practice these examples in CursorFunction for PostgreSQL.

Table-valued functions

Apart from database views, one of the underrated features of SQL is table-valued functions. This is not the first time in this book that we've discussed this feature, but this time, let's add a few more details. So, a table-valued function is a function that returns a set of data as a table data type. The returned table can be used just like a regular table.

Table-valued functions are not supported in MySQL, but they are supported in PostgreSQL, Oracle, and SQL Server. Next is a snippet of code from a PostgreSQL table-valued function (notice the RETURNS TABLE syntax, which indicates that the SELECT query from the function returns the data as a table to whatever calls the function):

CREATE OR REPLACE FUNCTION "product_of_product_line"(
  IN "p_line_in" VARCHAR)
RETURNS TABLE("p_id" BIGINT, "p_name" VARCHAR, 
              "p_line" VARCHAR) LANGUAGE plpgsql 
AS $$ 
BEGIN
  RETURN QUERY
  SELECT ...;     
  END; 
$$;

By default, the jOOQ Code Generator will generate for this function a class named ProductOfProductLine in the jooq.generated.tables package, not in the jooq.generated.routines package. The explanation is simple; jOOQ (like most databases) treats table-valued functions as ordinary tables that can be used in the FROM clause of SELECT like any other table. An exception from this practice is Oracle, where it is quite common to treat them as standalone routines – in this context, jOOQ has a flag setting that allows us to indicate whether table-valued functions should be treated as ordinary tables (generated in jooq.generated.tables) or as plain routines (generated in jooq.generated.routines). This is detailed in Chapter 6, Tackling Different Kinds of JOIN Statements.

Calling a table-valued function (with arguments) can be done via the call() method:

ProductOfProductLine popl = new ProductOfProductLine();
Table<ProductOfProductLineRecord> t = popl.call("Trains");
Result<ProductOfProductLineRecord> r = 
   ctx.fetch(popl.call("Trains"));
Result<ProductOfProductLineRecord> r = 
   ctx.selectFrom(popl.call("Trains")).fetch();

In queries, we may prefer to use the PRODUCT_OF_PRODUCT_LINE static field that was generated by the jOOQ generator in ProductOfProductLine. Both of the following examples produce the same SQL:

ctx.selectFrom(PRODUCT_OF_PRODUCT_LINE.call("Trains"))
   .fetch();
ctx.selectFrom(PRODUCT_OF_PRODUCT_LINE(val("Trains")))
   .fetch();

Here are two more examples of calling this table-valued function in the FROM clause:

ctx.selectFrom(PRODUCT_OF_PRODUCT_LINE.call("Trains"))
   .where(PRODUCT_OF_PRODUCT_LINE.P_NAME.like("1962%"))
   .fetch();
ctx.select(PRODUCT_OF_PRODUCT_LINE.P_ID, 
           PRODUCT_OF_PRODUCT_LINE.P_NAME)
   .from(PRODUCT_OF_PRODUCT_LINE.call("Classic Cars"))
   .where(PRODUCT_OF_PRODUCT_LINE.P_ID.gt(100L))
   .fetch();

Since a table-valued function returns a table, we should be able to use them in joins. But the regular JOIN feature doesn't allow us to join a table-valued function, so we need another approach. Here is where CROSS APPLY and OUTER APPLY (or LATERAL) enter into the scene. In Chapter 6, Tackling Different Kinds of JOIN Statements, you saw an example of using CROSS/OUTER APPLY to solve the popular task of joining two tables based on the results of a TOP-N query. So, CROSS/OUTER APPLY allows us to combine in a query the results returned by a table-valued function with the results of other tables or, in short, to join table-valued functions to other tables.

For instance, let's use CROSS/OUTER APPLY (you can think of it as a Stream.flatMap() in Java) to join the PRODUCTLINE table to our table-valued function. Let's say that we have added a new PRODUCTLINE without products named Helicopters and let's see how CROSS APPLY works: 

ctx.select(PRODUCTLINE.PRODUCT_LINE, PRODUCTLINE
   .TEXT_DESCRIPTION, PRODUCT_OF_PRODUCT_LINE.asterisk())
   .from(PRODUCTLINE)
   .crossApply(PRODUCT_OF_PRODUCT_LINE(
               PRODUCTLINE.PRODUCT_LINE))
   .fetch();

Since the Helicopters product line has no products, CROSS APPLY will not fetch it because CROSS APPLY acts as CROSS JOIN LATERAL. How about OUTER APPLY?

ctx.select(PRODUCTLINE.PRODUCT_LINE, PRODUCTLINE
     .TEXT_DESCRIPTION, PRODUCT_OF_PRODUCT_LINE.asterisk())
   .from(PRODUCTLINE)
   .outerApply(PRODUCT_OF_PRODUCT_LINE(
               PRODUCTLINE.PRODUCT_LINE))
   .fetch();

On the other hand, OUTER APPLY acts as LEFT OUTER JOIN LATERAL, so the Helicopters product line is returned as well.

Lukas Eder shared an opinion here: "In fact, for historic reasons, APPLY or LATERAL is optional in at least Db2, Oracle, and PostgreSQL, under some conditions. SQL Server had APPLY for a long time, but the others introduced LATERAL only relatively recently. I personally don't understand the value of making LATERAL explicit. It's always clear what an implicit LATERAL means..."

You can check out these examples in the bundled code, TableValuedFunction for PostgreSQL, Oracle, and SQL Server.

Oracle's package

Oracle allows us to group the functions/procedures that are commonly logically related into a package. A package has two parts: the first part contains the public items and is known as the package specification, while the second part, known as the package body, provides the code of the cursors or subprograms declared in the package specification. If no cursors/subprograms were declared in the package specification, then the package body can be skipped. If you are not an Oracle fan, then the following syntax should help you to digest this topic a little bit easier:

Figure 15.2 – Syntax of Oracle package

Figure 15.2 – Syntax of Oracle package

Lukas Eder shared an analogy that's useful to better understand this topic: "If it helps, package specifications are like interfaces, and bodies are like singleton instances, kind of. In a way, like this: https://twitter.com/lukaseder/status/1443855980693962755."

Packages sustain modularity, facilitate clear design, and increase code maintainability by hiding the implementation details in the package body. Moreover, packages are loaded into memory as a whole at the first invocation of a function/procedure so any subsequent invocations of functions/procedures from this package require no disk I/O. While more information on using Oracle packages is available in the official documentation, let's have an example:

CREATE OR REPLACE PACKAGE "DEPARTMENT_PKG" AS
 TYPE "BGT" IS RECORD ("LOCAL_BUDGET" FLOAT, "CASH" FLOAT);
  FUNCTION "GET_BGT"("p_profit" IN FLOAT)
    RETURN "BGT";
  FUNCTION "GET_MAX_CASH"
    RETURN FLOAT; 
END "DEPARTMENT_PKG";
/
CREATE OR REPLACE PACKAGE BODY "DEPARTMENT_PKG"
-- check bundled code for this skipped part
END"DEPARTMENT_PKG";
/

A spicy tip from Lukas Eder: "The '/' is a SQL*Plus 'spool' token (also supported by SQL Developer), and not an actual PL/SQL syntax element. For example, it doesn't work in DBeaver.

And, another tip regarding quoted identifiers: Might be a good reminder that, to be better interoperable with non-jOOQ code, perhaps not using quoted identifiers is better. It will be a PITA if all callers will have to always quote that identifier if they're not using jOOQ :)"

So, here we have a package named DEPARTMENT_PKG containing a user-defined type named BGT and two functions, GET_BGT() and GET_MAX_CASH(). At the source code generation stage, jOOQ will reflect this package and its content via Java sub-packages as follows:

  • jooq.generated.packages – Contains the DepartmentPkg class representing the package and exposing the DEPARTMENT_PKG static that can be used to call the functions as DEPARTMENT_PKG.getMaxCash() and DEPARTMENT_PKG.getBgt() and get the result as a Field.
  • jooq.generated.packages.department_pkg – Contains the GetBgt and GetMaxCash classes representing the two functions from the package. Moreover, it contains the UDTs class containing the static BGT for CLASSICMODELS.DEPARTMENT_PKG.BGT (jooq.generated.packages.department_pkg.udt.Bgt.BGT).
  • jooq.generated.packages.department_pkg.udt – Contains the class Bgt mapping the BGT UDT type as an extension of UDTImpl.
  • jooq.generated.packages.department_pkg.udt.records – Contains the BgtRecord class representing the BGT UDT type as an extension of UDTRecordImpl.

Calling these two functions (GET_BGT() and GET_MAX_CASH()) can be done by instantiating the GetMaxCash class, respectively the GetBgt class and call execute() as follows:

GetMaxCash gmc = new GetMaxCash();
gmc.execute(ctx.configuration());
double resultGmc = gmc.getReturnValue();
GetBgt bgt = new GetBgt();
bgt.setPProfit(50000.0);
bgt.execute(ctx.configuration());
BgtRecord resultBgt = bgt.getReturnValue();

We can also compact these examples via the statics DepartmentPkg.getBgt() and DepartmentPkg.getMaxCash() as follows:

double resultGmc = getMaxCash(ctx.configuration());
BgtRecord resultBgt = getBgt(ctx.configuration(), 50000.0);

Calling these functions from queries is also possible. For instance, here are two trivial examples of calling them in SELECT:

ctx.select(getMaxCash()).fetch();
double mc = ctx.fetchValue(getMaxCash());
The rendered SQL:
SELECT "CLASSICMODELS"."department_pkg"."get_max_cash"() 
FROM DUAL

Here is another example that uses both functions in the same query:

ctx.select(OFFICE.OFFICE_CODE, OFFICE.CITY, OFFICE.COUNTRY,
           DEPARTMENT.NAME, DEPARTMENT.LOCAL_BUDGET)
   .from(OFFICE)
   .join(DEPARTMENT)
   .on(OFFICE.OFFICE_CODE.eq(DEPARTMENT.OFFICE_CODE)
     .and(DEPARTMENT.LOCAL_BUDGET
       .in(getBgt(ctx.configuration(),
          getMaxCash(ctx.configuration()))
            .getLocalBudget())))
   .fetch();

Check out these examples next to a few others not presented here in Package for Oracle.

Oracle's member function/procedure

Mainly, an Oracle PL/SQL object type contains attributes and members (or methods). Attributes or fields have data types and they are used to store data, while members are subprograms (functions/procedures) that are defined in the object type and manipulate the attributes for implementing certain functionalities. In this way, Oracle UDTs are a fully-fledged attempt at implementing an Object Relational Database Management System (ORDBMS). PostgreSQL didn't go quite as far as Oracle.

If you are not an Oracle fan, then the following syntax should shed some light on this topic:

Figure 15.3 – Syntax of Oracle members

Figure 15.3 – Syntax of Oracle members

Based on this syntax, let's have an example. Our MANAGER table has a field named MANAGER_EVALUATION that is of type EVALUATION_CRITERIA (in jOOQ, of type TableField<ManagerRecord, EvaluationCriteriaRecord>) defined as follows:

CREATE OR REPLACE TYPE "EVALUATION_CRITERIA" AS OBJECT (
  "communication_ability" NUMBER(7), 
  "ethics" NUMBER(7), 
  "performance" NUMBER(7), 
  "employee_input" NUMBER(7),
  MEMBER FUNCTION "IMPROVE"("k" NUMBER) 
      RETURN "EVALUATION_CRITERIA",
  MAP MEMBER FUNCTION "SCORE" RETURN NUMBER
);
CREATE OR REPLACE TYPE BODY "EVALUATION_CRITERIA" AS
-- check bundled code for this skipped part
END;

Here, we have an object type containing four attributes (communication_ability, ethics, performance, and employee_input), two member functions (IMPROVE() and SCORE()), and no member procedures.

The jOOQ Code Generator produces the following artifacts:

  • jooq.generated.udt – In this package, we have the UDT type named EvaluationCriteria, representing an extension of jOOQ's UDTImpl<EvaluationCriteriaRecord>. It contains the statics EVALUATION_CRITERIA, COMMUNICATION_ABILITY, ETHICS, PERFORMANCE, and EMPLOYEE_INPUT to refer to these UDT attributes and several flavors of the improve() and score() member functions returning the plain result or wrapped in a Field.
  • jooq.generated.udt.records – Contains the EvaluationCriteriaRecord representing the UDT record as an extension of the jOOQ's UDTRecordImpl<EvaluationCriteriaRecord>. The EvaluationCriteriaRecord contains getters/setters for the object type attributes and contains the methods improve() and score() as well. These methods encapsulate the code needed to call the actual improve() and score() member functions.
  • jooq.generated.udt.evaluation_criteria – Contains the Improve and Score classes (routines), so a class for each member function. Both EvaluationCriteria and EvaluationCriteriaRecord use these classes internally.

So, we can distinguish between calling the member functions starting from an empty record or from an existing record (for instance, a record fetched from the database). The conventional approach for starting from an empty record relies on DSLContext.newRecord():

EvaluationCriteriaRecord ecr = 
   ctx.newRecord(EVALUATION_CRITERIA);        

The created record is already attached. Alternatively, we can instantiate EvaluationCriteriaRecord or use EVALUATION_CRITERIA.newRecord(), but the resulting record is not attached to a configuration (database), so you'll have to explicitly attach it by calling attach().

Next, we set the values of the attributes and call the member functions. Here, we call the score() method, which returns a BigDecimal:

ecr.setCommunicationAbility(58);
ecr.setEthics(30);
ecr.setPerformance(26);
ecr.setEmployeeInput(59);
BigDecimal result = ecr.score();

On the other hand, the improve() methods increase the evaluation attributes by the given value and return a new EvaluationCriteriaRecord:

EvaluationCriteriaRecord newEcr = ecr.improve(10);        

The newEcr record has communication_ability at 68 instead of 58, ethics at 40 instead of 30, and performance at 36 instead of 26. Only employee_input remains unchanged.

We can use ecr/newEcr in queries as well. Here is an example of using newEcr that originates from an empty record next to MANAGER.MANAGER_EVALUATION, which is fetched from the database (remember that MANAGER.MANAGER_EVALUATION is a TableField<ManagerRecord, EvaluationCriteriaRecord>):

ctx.select(MANAGER.MANAGER_ID, MANAGER.MANAGER_NAME)
   .from(MANAGER)
   .where(score(MANAGER.MANAGER_EVALUATION)
     .lt(newEcr.score()))
   .fetch();        

Here's another example that combines the calls of improve() and score():

ctx.select(MANAGER.MANAGER_ID, MANAGER.MANAGER_NAME)
   .from(MANAGER)
   .where(score(improve(
      MANAGER.MANAGER_EVALUATION, inline(10)))
        .gt(BigDecimal.valueOf(57)))
   .fetch();

Check out these examples next to others omitted here in MemberFunction for Oracle.

User-defined aggregate stored functions

Oracle and SQL Server allow us to define aggregate stored functions (if you are not familiar with this topic, then please search these on Google: Oracle User-Defined Aggregate Functions Interface for Oracle and SQL Server User-Defined Aggregate Functions for SQL Server).

Moreover, the code of such functions is quite big to be listed here so please check the bundled code. For Oracle, check the application UserDefinedAggFunction, which calls a user-defined aggregate function named secondMax() that finds the second maximum value:

ctx.select(secondMax(ORDERDETAIL.QUANTITY_ORDERED), 
                     ORDERDETAIL.PRODUCT_ID)
   .from(ORDERDETAIL)
   .groupBy(ORDERDETAIL.PRODUCT_ID)
   .having(secondMax(ORDERDETAIL.QUANTITY_ORDERED)
     .gt(BigDecimal.valueOf(55)))
   .fetch();

And for SQL Server, check the application also named UserDefinedAggFunction. Here, we call an aggregate stored function named concatenate() that simply concatenates the given strings:

ctx.select(concatenate(EMPLOYEE.FIRST_NAME))
   .from(EMPLOYEE)
   .where(EMPLOYEE.FIRST_NAME.like("M%"))
   .fetch();

In order to work, please pay attention that you need to place the StringUtilities.dll DLL file (available in the bundled code) in the path specified in the function code.

Stored procedures

jOOQ allows you to call stored procedures via the same Routines API. Next, let's see several examples of calling different kinds of stored procedures.

Stored procedures and output parameters

For instance, let's consider the following stored procedure expressed in Oracle and having an OUT parameter:

CREATE OR REPLACE NONEDITIONABLE PROCEDURE 
   "GET_AVG_PRICE_BY_PRODUCT_LINE" (
    "pl" IN VARCHAR2, "average" OUT DECIMAL) AS 
BEGIN
   SELECT AVG("PRODUCT"."BUY_PRICE")
      INTO "average"
      FROM "PRODUCT"
      WHERE "PRODUCT"."PRODUCT_LINE" = "pl";
END;

jOOQ generates the Java version of this stored procedure as a class named GetAvgPriceByProductLine in the jooq.generated.routines package. The methods of this class allow us to prepare the parameters (each input parameter has associated a setter, while each output parameter has associated a getter) and call our stored procedure as follows:

GetAvgPriceByProductLine avg = new GetAvgPriceByProductLine();
avg.setPl("Classic Cars");
avg.execute(ctx.configuration());
BigInteger result = avg.getAverage();

We can express this more compactly via the generated jooq.generated.Routines.getAvgPriceByProductLine() static as follows:

BigInteger result = getAvgPriceByProductLine(
   ctx.configuration(), "Classic Cars");

Calling a stored procedure in a jOOQ query can be done via the getAvgPriceByProductLine(Configuration configuration, String pl) flavor as in the following example:

ctx.select(PRODUCT.PRODUCT_ID, PRODUCT.PRODUCT_NAME,   
           PRODUCT.BUY_PRICE)
   .from(PRODUCT)
   .where(PRODUCT.BUY_PRICE.coerce(BigInteger.class)
     .gt(getAvgPriceByProductLine(
         ctx.configuration(), "Classic Cars"))
     .and(PRODUCT.PRODUCT_LINE.eq("Classic Cars")))
   .fetch();

jOOQ first renders the call of the stored procedure and fetches the OUT parameter value:

call "CLASSICMODELS"."get_avg_price_by_product_line" (
  'Classic Cars', ?)

Next, the fetched value (64, in this example) is used to render the SELECT:

SELECT "CLASSICMODELS"."PRODUCT"."PRODUCT_ID",
       "CLASSICMODELS"."PRODUCT"."PRODUCT_NAME",
       "CLASSICMODELS"."PRODUCT"."BUY_PRICE"
FROM "CLASSICMODELS"."PRODUCT"
WHERE ("CLASSICMODELS"."PRODUCT"."BUY_PRICE" > 64
  AND "CLASSICMODELS"."PRODUCT"."PRODUCT_LINE" 
    = 'Classic Cars')

Next, let's call a stored procedure without output parameters that fetches a single result set.

Stored procedures fetching a single result set

Here is a MySQL example of a stored procedure that doesn't contain output parameters and fetches a single result set via SELECT:

DELIMITER $$
CREATE PROCEDURE `get_product`(IN `pid` BIGINT)
  BEGIN
   SELECT * FROM `product` WHERE `product`.`product_id` = `pid`;
  END $$
DELIMITER

Calling this stored procedure from jOOQ can be done via the generated GetProduct class:

GetProduct gp = new GetProduct();
gp.setPid(1L);
gp.execute(ctx.configuration());
Result<Record> result = gp.getResults().get(0);

The result is obtained via gp.getResults(). Since there is a single result (the result set produced by the SELECT), we have to call get(0). If there were two results involved (for instance, if we had two SELECT statements in the stored procedure), then we would call get(0) to get the first result and get(1) to get the second result. Or, in the case of even more results, simply loop the results. Notice that, in the case of stored procedures, the getReturnValue() method returns void since stored procedures don't return results as a stored function (they don't contain a RETURN statement). In fact, SQL Server's procedures can return an error code, which is an int. You can see that in SQL Server generated code for procedures.

Calling the previous stored procedure via Routines.getProduct() returns void as well:

getProduct(ctx.configuration(), 1L);

The results obtained via getResults() are of type Result<Record>. This can be easily transformed into a regular Table as follows:

Table<?> t = table(gp.getResults().get(0));
Table<ProductRecord> t = table(gp.getResults()
  .get(0).into(PRODUCT));

Next, let's take the get_product() stored procedure and let's express it in Oracle by adding an OUT parameter of type SYS_REFCURSOR.

Stored procedures with a single cursor

The following stored procedure fetches the same result set as the previous get_product(), but it returns it via a cursor:

CREATE OR REPLACE NONEDITIONABLE PROCEDURE "GET_PRODUCT"(
    "pid" IN NUMBER, "cursor_result" OUT SYS_REFCURSOR) AS 
BEGIN
  OPEN "cursor_result" FOR
    SELECT * FROM "PRODUCT" 
    WHERE "PRODUCT"."PRODUCT_ID" = "pid";
END;

This time, in GetProduct, jOOQ generates a getter for the OUT parameter named getCursorResult(), which allows us to fetch the result as a Result<Record>:

GetProduct gp = new GetProduct();
gp.setPid(1L);
gp.execute(ctx.configuration());
Result<Record> result = gp.getCursorResult();

Or you can fetch it more compactly via Routines.getProduct(Configuration configuration, Number pid):

Result<Record> result = getProduct(ctx.configuration(), 1L);

As usual, this Result<Record> can be easily transformed into a regular Table:

Table<?> t = table(gp.getResults().get(0));
Table<?> t = table(getProduct(ctx.configuration(), 1L));
Table<ProductRecord> t = 
    table(gp.getCursorResult().into(PRODUCT)); 
Table<ProductRecord> t = 
    table(getProduct(ctx.configuration(), 1L)
        .into(PRODUCT));

Next, you can use this Table in queries.

Stored procedures fetching multiple result sets

Going further, let's tackle a stored procedure returning multiple result sets. Here, it's expressed in MySQL:

DELIMITER $$
CREATE PROCEDURE `get_emps_in_office`(
                           IN `in_office_code` VARCHAR(10))
  BEGIN
    SELECT `office`.`city`, `office`.`country`, 
                   `office`.`internal_budget`
      FROM `office`
      WHERE `office`.`office_code`=`in_office_code`;
     SELECT `employee`.`employee_number`,
            `employee`.`first_name`, `employee`.`last_name`
      FROM `employee`
      WHERE `employee`.`office_code`=`in_office_code`;
  END $$
DELIMITER ;

As you already know, jOOQ generates the GetEmpsInOffice class and the result sets are available via getResults():

GetEmpsInOffice geio = new GetEmpsInOffice();
geio.setInOfficeCode("1");
geio.execute(ctx.configuration());
Results results = geio.getResults();
for (Result<Record> result : results) {
  System.out.println("Result set:
");
  for (Record record : result) {
    System.out.println(record);
  }
}

Routines.getEmpsInOffice(Configuration c, String inOfficeCode) returns void.

Stored procedures with multiple cursors

Next, let's take the get_emps_in_office() stored procedure, and let's express it in Oracle by adding two OUT parameters of type SYS_REFCURSOR:

CREATE OR REPLACE NONEDITIONABLE PROCEDURE 
  "GET_EMPS_IN_OFFICE"("in_office_code" IN VARCHAR,
    "cursor_office" OUT SYS_REFCURSOR, 
    "cursor_employee" OUT SYS_REFCURSOR) AS 
BEGIN
  OPEN "cursor_office" FOR
    SELECT "OFFICE"."CITY", "OFFICE"."COUNTRY", 
        "OFFICE"."INTERNAL_BUDGET"
    FROM "OFFICE"
    WHERE "OFFICE"."OFFICE_CODE" = "in_office_code";
  OPEN "cursor_employee" FOR
    SELECT "EMPLOYEE"."EMPLOYEE_NUMBER", 
           "EMPLOYEE"."FIRST_NAME", "EMPLOYEE"."LAST_NAME"
    FROM "EMPLOYEE"
    WHERE "EMPLOYEE"."OFFICE_CODE" = "in_office_code";
END;

This time, besides getResults(), which you are already familiar with, we can take advantage of the getters produced by jOOQ for the OUT parameters as follows:

GetEmpsInOffice geio = new GetEmpsInOffice();
geio.setInOfficeCode("1");
geio.execute(ctx.configuration());
Result<Record> co = geio.getCursorOffice();
Result<Record> ce = geio.getCursorEmployee();

Also, relying on Routines.getEmpsInOffice(Configuration c, String inOfficeCode) is quite convenient:

GetEmpsInOffice results = 
   getEmpsInOffice(ctx.configuration(), "1");        

Next, you can rely on results.getCursorInfo() respectively on results.getCursorEmployee() or by looping the results as follows:

for (Result<Record> result : results.getResults()) {
  …
}

Next, loop each Result<Record> as for (Record record : result) ….

Not sure it's worth mentioning, but at least Oracle also knows typed REF CURSORS (instead of just SYS_REFCURSOR), which jOOQ will support as well soon (when you're reading this book, these features should be available): https://github.com/jOOQ/jOOQ/issues/11708.

Calling stored procedures via the CALL statement

Finally, let's tackle the API of calling a stored procedure via the CALL statement in an anonymous procedural block and via CALL directly. Consider the following stored procedure expressed in Oracle (the complete code is available in the bundled code):

CREATE OR REPLACE NONEDITIONABLE PROCEDURE 
    "REFRESH_TOP3_PRODUCT"("p_line_in" IN VARCHAR2) AS 
BEGIN
  DELETE FROM "TOP3PRODUCT"; 
  INSERT INTO ...
      FETCH NEXT 3 ROWS ONLY;         
END;

Calling this stored procedure via the CALL statement in an anonymous procedural block can be done via DSLContext.begin() as follows:

ctx.begin(call(name("REFRESH_TOP3_PRODUCT"))
   .args(val("Trains")))
   .execute();

Or, call it directly via DSLContext.call():

ctx.call(name("REFRESH_TOP3_PRODUCT"))
   .args(val("Trains"))
   .execute();

You can practice all these examples in CallProcedure.

jOOQ and creating stored functions/procedures

Starting with version 3.15, jOOQ began to add an API for creating stored functions, procedures, and triggers. Among others, we have support for CREATE FUNCTION, CREATE OR REPLACE FUNCTION, CREATE PROCEDURE, CREATE OR REPLACE PROCEDURE, DROP FUNCTION, and DROP PROCEDURE.

Creating stored functions

For instance, creating a scalar function for MySQL can be done as follows:

Parameter<Integer> quantity = in("quantity", INTEGER);
Parameter<Double> listPrice = in("list_price", DOUBLE);
Parameter<Double> fractionOfPrice = 
  in("fraction_of_price", DOUBLE);
ctx.createOrReplaceFunction("sale_price_jooq")
   .parameters(quantity, listPrice, fractionOfPrice)
   .returns(DECIMAL(10, 2))
   .deterministic()
   .as(return_(listPrice.minus(listPrice
     .mul(fractionOfPrice)).mul(quantity)))
   .execute();

Here, we create a scalar function having three input parameters created via the intuitive Parameter API by specifying their name and types. For MySQL, jOOQ renders the following code:

DROP FUNCTION IF EXISTS `sale_price_jooq`;
CREATE FUNCTION `sale_price_jooq`(`quantity` INT, 
         `list_price` DOUBLE, `fraction_of_price` DOUBLE) 
RETURNS DECIMAL(10, 2) 
DETERMINISTIC 
BEGIN 
RETURN ((`list_price` - (`list_price` * 
         `fraction_of_price`)) * `quantity`); 
END;

Notice that in order to work, you should be aware of the following note.

Important Note

In MySQL, we can execute statement batches if we turn on the allowMultiQueries flag, which defaults to false; otherwise, we get an error. The previously generated SQL chains two statements, therefore this flag needs to be turned on – we can do it via the JDBC URL as jdbc:mysql:…/classicmodels?allowMultiQueries=true. Alternatively, in this particular case, we can rely on the dropFunctionIfExists(),createFunction() combo instead of createOrReplaceFunction(). I strongly advise you to take a couple of minutes to read this article by Lukas Eder, which explains in detail the implication of this flag in the jOOQ context: https://blog.jooq.org/mysqls-allowmultiqueries-flag-with-jdbc-and-jooq/.

You already know how to call this function from jOOQ via the generated code. This means that you have to run this code to create the stored function in the database, and afterward, run the jOOQ Code Generator to obtain the expected jOOQ artifacts. On the other hand, you can call it via plain SQL via the DSL's function() as in this example:

float result = ctx.select(function(name("sale_price_jooq"),
  DECIMAL(10, 2), inline(10), inline(20.45), inline(0.33)))
  .fetchOneInto(Float.class);

You can practice this example in CreateFunction.

How about creating the following PostgreSQL function having output parameters?

CREATE OR REPLACE FUNCTION "swap_jooq"(
       INOUT "x" INT, INOUT "y" INT) 
  RETURNS RECORD LANGUAGE PLPGSQL AS $$ 
BEGIN
  SELECT "x", "y" INTO "y", "x"; 
END; $$

From jOOQ, this function can be created like this:

Parameter<Integer> x = inOut("x", INTEGER);
Parameter<Integer> y = inOut("y", INTEGER);
ctx.createOrReplaceFunction("swap_jooq")
   .parameters(x, y)                 
   .returns(RECORD)                
   .as(begin(select(x, y).into(y, x)))
   .execute();

Calling this function can be done via plain SQL as in this example:

Record1<Record> result = ctx.select(
  function(name("swap_jooq"),
    RECORD, inline(1), inline(2))).fetchOne();

You can practice this example next to another one using OUT parameters in CreateFunction for PostgreSQL. For more examples that allow you to explore this API in detail, please consider the bundled code and the jOOQ manual.

Creating stored procedures

Let's begin with a stored procedure expressed in the SQL Server dialect:

CREATE OR ALTER PROCEDURE [update_msrp_jooq] 
   @product_id BIGINT, @debit INT AS 
BEGIN
  UPDATE [classicmodels].[dbo].[product]
  SET [classicmodels].[dbo].[product].[msrp] = 
      ([classicmodels].[dbo].[product].[msrp] - @debit)
WHERE [classicmodels].[dbo].[product].[product_id] = 
      @product_id; 
END;

This stored procedure, which has two input parameters and updates the PRODUCT.MSRP field, can be created through the jOOQ API as follows:

Parameter<Long> id = in("id", BIGINT);
Parameter<Integer> debit = in("debit", INTEGER);        
ctx.createOrReplaceProcedure("update_msrp_jooq")
   .parameters(id, debit)
   .as(update(PRODUCT)
        .set(PRODUCT.MSRP, PRODUCT.MSRP.minus(debit))
        .where(PRODUCT.PRODUCT_ID.eq(id)))
   .execute();

You already know how to call this procedure from jOOQ via the generated code, so this time, let's call it via the CALL statement:

// CALL statement in an anonymous procedural block
var result = ctx.begin(call(name("update_msrp_jooq"))
       .args(inline(1L), inline(100)))
       .execute();
// CALL statement directly
var result = ctx.call(name("update_msrp_jooq"))
       .args(inline(1L), inline(100))
       .execute();

The returned result represents the number of rows affected by this UPDATE. This example is available in CreateProcedure for SQL Server and PostgreSQL.

Next, let's pick up an example expressed in Oracle dialect:

CREATE OR REPLACE NONEDITIONABLE PROCEDURE 
   "get_avg_price_by_product_line_jooq" (
      "pl" IN VARCHAR2,"average" OUT DECIMAL) AS 
BEGIN
   SELECT AVG("CLASSICMODELS"."PRODUCT"."BUY_PRICE")
   INTO "average" FROM "CLASSICMODELS"."PRODUCT"
   WHERE "CLASSICMODELS"."PRODUCT"."PRODUCT_LINE" = "pl";
END;

This time, the jOOQ code for creating this procedure is as follows:

Parameter<String> pl = in("pl", VARCHAR);
Parameter<BigDecimal> average = out("average", DECIMAL);
ctx.createOrReplaceProcedure(
    "get_avg_price_by_product_line_jooq")
   .parameters(pl, average)
   .as(select(avg(PRODUCT.BUY_PRICE)).into(average)
   .from(PRODUCT)
   .where(PRODUCT.PRODUCT_LINE.eq(pl)))
   .execute();

Since you are already familiar with this stored procedure from the previous section, Stored procedures and output parameters, you should have no problem calling it. The example is available in CreateProcedure for Oracle.

Finally, let's tackle a MySQL stored procedure that fetches a result set via SELECT:

CREATE PROCEDURE `get_office_gt_budget_jooq`(`budget` INT) 
  BEGIN
    SELECT `classicmodels`.`office`.`city`,
           `classicmodels`.`office`.`country`,
           `classicmodels`.`office`.`state`
    FROM `classicmodels`.`office`
    WHERE `classicmodels`.`office`.`internal_budget` > `budget`; 
  END;

The jOOQ code that creates the stored procedure is as follows:

Parameter<Integer> budget = in("budget", INTEGER);
ctx.createOrReplaceProcedure("get_office_gt_budget_jooq")
   .parameters(budget)
   .as(select(OFFICE.CITY, OFFICE.COUNTRY, OFFICE.STATE)
   .from(OFFICE)
   .where(OFFICE.INTERNAL_BUDGET.gt(budget)))
   .execute();

In order for this code to work, we need to turn on the allowMultiQueries flag as explained in the previous section, Creating stored functions.

You can find this example next to another one that fetches two result sets in the application named CreateProcedure for MySQL.

Summary

In this chapter, you've learned how to call and create some typical stored functions and procedures. Since these are powerful SQL tools, jOOQ strives to provide a comprehensive API to cover the tons of possibilities to express these artifacts in different dialects. Most probably, by the time you read this book, jOOQ will have already enriched this API even further and more examples will be available in the bundled code.

In the next chapter, we tackle aliases and SQL templating.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset