Skip to main content

Procedures and functions

Passing and receiving parameters​

Use IN/OUT/INOUT parameters for scalar values. For example, create a procedure that adds two numbers:

CREATE PROCEDURE add_numbers(IN a INT, IN b INT, INOUT result INT)
BEGIN SET result = a + b; END

Send the complete definition as one statement. Then bind the input values and the output parameter:

Map<String, Object> result = jdbc.call(
        "CALL add_numbers(?, ?, ?)",
        new Object[] { 10, 5, SqlArg.asInOut("result", 0, Types.INTEGER) });
Integer sum = (Integer) result.get("result"); // 15

Named parameters, parameter type declarations, and multiple scalar outputs are also supported. JDBC REF_CURSOR output mapping is not supported on this path. To receive rows returned by a procedure, use result-set rules, not a REF_CURSOR parameter.

Reading a function value​

A MySQL stored function returns one scalar value, not a table or an OUT-parameter record. Read it with a query method:

CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT DETERMINISTIC RETURN a + b;
Integer sum = jdbc.queryForObject("SELECT add_numbers(?, ?)",
        new Object[] { 10, 5 }, Integer.class);

Positional and named arguments, type conversion, and CallableStatement callbacks are supported. For multiple output fields or returned rows, use a procedure rather than declaring a table-returning function.