|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.
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