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
Feedback
Do you have any feedback about this page? We'd love to hear it!