The jOOQ User Manual SQL building SQL Statements (DDL) The CREATE statement CREATE PROCEDURE SQL data access characteristics

previous : next

SQL data access characteristics

Applies to ❌ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Some dialects require the explicit specification of a few characteristics of a procedure, defining what kind of content a procedure is allowed (and expected) to have. These act both as contracts for your development (similar to the java.lang.FunctionalInterface annotation), as well as hints to the database regarding whether a procedure is expected to have side-effects (and thus maybe cannot be used indirectly via a stored function in a SELECT statement), or whether it depends on data, or is purely deterministic. The SQL data access characteristics include:

While procedures are generally expected to yield side effects, it may be useful to use a procedure as a "function with quirky syntax" to be consumed by other functions, because it can return several OUT parameters, instead of just a single RETURN value, like function, hence the utility of these characteristics also in procedures.

• NO SQL
• CONTAINS SQL
• READS SQL DATA
• MODIFIES SQL DATA

While the semantics seem pretty clear, please refer to your database manual for the details, as there may be subtle differences, e.g. regarding what particular procedural statement constitues "SQL".

If a characteristic is not supported by your dialect, you can still specify it, and jOOQ will simply ignore it in generated SQL.

Parameter<Integer> o = out("o", INTEGER);

create.createProcedure("p1")
      .parameters(o)
      .noSQL()
      .as(o.set(1))
      .execute();

create.createProcedure("p2")
      .parameters(o)
      .containsSQL()
      .as(o.set(select(val(1))))
      .execute();

create.createProcedure("p3")
      .parameters(o)
      .readsSQLData()
      .as(o.set(selectCount().from(BOOK)))
      .execute();

create.createProcedure("p4")
      .parameters(o)
      .modifiesSQLData()
      .as(
        insertInto(LOGS).columns(LOGS.TEXT).values("Function F4 was called"),
        o.set(1)
      )
      .execute();

This works just like SQL data access characteristics for functions

The jOOQ User Manual SQL building SQL Statements (DDL) The CREATE statement CREATE PROCEDURE SQL data access characteristics

previous : next