Stored Procedures & Functions

Define stored procedures and functions once with full type safety on both inputs and outputs. The builder tracks types statically — wrong argument types or missing parameters are compile errors, not runtime surprises.

Procedures

Void Procedures

Procedures with only IN parameters return Void (or Unit in Scala/Kotlin):

Kotlin

// A void procedure — no OUT parameters, just side effects
val auditLog =
    DbProcedure.define("audit_log")
        .input(PgTypes.text)       // action
        .input(PgTypes.text)       // details
        .build()

fun logAction(action: String, details: String) {
    auditLog.call(action, details).transact(tx)
}

Java

// A void procedure — no OUT parameters, just side effects
static final DbProcedure.Def2_0<String, String> auditLog =
    DbProcedure.define("audit_log")
        .input(PgTypes.text)
        .input(PgTypes.text)
        .build();

void logAction(
    String action, String details
) {
    auditLog
        .call(action, details)
        .transact(tx);
}

Scala

// A void procedure — no OUT parameters, just side effects
val auditLog =
  DbProcedure.define("audit_log")
    .input(PgTypes.text)       // action
    .input(PgTypes.text)       // details
    .build()

def logAction(action: String, details: String): Unit =
  auditLog.call(action, details).transact(tx)

Show entire file

OUT Parameters

Add .out() to declare output parameters. The builder tracks their types:

Kotlin

// OUT parameters — the builder tracks output types statically
val getUser =
    DbProcedure.define("get_user_by_id")
        .input(PgTypes.int4)       // user_id IN
        .out(PgTypes.text)        // name OUT
        .out(PgTypes.text)        // email OUT
        .build()

// call() is fully typed — wrong argument types won't compile
fun findUser(userId: Int): Pair<String, String> =
    getUser.call(userId).transact(tx)

Java

// OUT parameters — the builder tracks output types statically
static final DbProcedure.Def1_2<Integer, String, String> getUser =
    DbProcedure.define("get_user_by_id")
        .input(PgTypes.int4)
        .out(PgTypes.text)
        .out(PgTypes.text)
        .build();

// call() is fully typed — wrong argument types won't compile
Tuple.Tuple2<String, String> findUser(
    int userId
) {
    return getUser
        .call(userId)
        .transact(tx);
}

Scala

// OUT parameters — the builder tracks output types statically
val getUser =
  DbProcedure.define("get_user_by_id")
    .input(PgTypes.int4)       // user_id IN
    .out(PgTypes.text)      // name OUT
    .out(PgTypes.text)      // email OUT
    .build()

// call() is fully typed — wrong argument types won't compile
def findUser(userId: Int): (String, String) =
  getUser.call(userId).transact(tx)

Show entire file

The builder returns a typed definition that captures the input and output parameter types. The numbers in the type name encode the arity: Def1_2 means 1 input and 2 outputs. For functions, Def2 means 2 inputs (functions always have exactly one output — the return value). You never need to write these types explicitly — just use var (Java) or let type inference work (Kotlin/Scala).

INOUT Parameters

INOUT parameters count as both input and output. The value goes in and comes back modified:

Kotlin

// INOUT — the value goes in and comes back modified
val applyDiscount =
    DbProcedure.define("apply_discount")
        .input(PgTypes.text)           // discount_code IN
        .inout(PgTypes.numeric)       // price INOUT — goes in, comes back modified
        .build()

fun applyDiscount(code: String, price: BigDecimal): BigDecimal =
    applyDiscount
        .call(code, price)
        .transact(tx)

Java

// INOUT — the value goes in and comes back modified
static final DbProcedure.Def2_1<String, BigDecimal, BigDecimal> applyDiscount =
    DbProcedure.define("apply_discount")
        .input(PgTypes.text)
        .inout(PgTypes.numeric)
        .build();

BigDecimal applyDiscount(
    String code, BigDecimal price
) {
    return applyDiscount
        .call(code, price)
        .transact(tx);
}

Scala

// INOUT — the value goes in and comes back modified
val applyDiscount =
  DbProcedure.define("apply_discount")
    .input(PgTypes.text)       // discount_code IN
    .inout(PgTypes.numeric)   // price INOUT
    .build()

def applyDiscount(code: String, price: BigDecimal): BigDecimal =
  applyDiscount.call(code, price).transact(tx)

Show entire file

Functions

Functions return a single value via SELECT instead of CALL:

Kotlin

// Functions use SELECT instead of CALL — every DbType reads correctly
val calcTax =
    DbFunction.define("calculate_tax", PgTypes.numeric)
        .input(PgTypes.numeric)    // amount
        .input(PgTypes.text)       // region
        .build()

// Zero-argument function
val nextId =
    DbFunction.define("next_id", PgTypes.int4)
        .build()

fun calculateTax(amount: BigDecimal, region: String): BigDecimal =
    calcTax.call(amount, region)
        .transact(tx)

Java

// Functions use SELECT instead of CALL — every DbType reads correctly
static final DbFunction.Def2<BigDecimal, String, BigDecimal> calcTax =
    DbFunction.define(
            "calculate_tax", PgTypes.numeric)
        .input(PgTypes.numeric)
        .input(PgTypes.text)
        .build();

// Zero-argument function
static final DbFunction.Def0<Integer> nextId =
    DbFunction.define(
            "next_id", PgTypes.int4)
        .build();

BigDecimal calculateTax(
    BigDecimal amount, String region
) {
    return calcTax
        .call(amount, region)
        .transact(tx);
}

Scala

// Functions use SELECT instead of CALL — every DbType reads correctly
val calcTax =
  DbFunction.define("calculate_tax", PgTypes.numeric)
    .input(PgTypes.numeric)    // amount
    .input(PgTypes.text)       // region
    .build()

// Zero-argument function
val nextId =
  DbFunction.define("next_id", PgTypes.int4)
    .build()

def calculateTax(amount: BigDecimal, region: String): BigDecimal =
  calcTax.call(amount, region).transact(tx)

Show entire file

Any Database

The procedure and function builders work with any database's types. Just use the right *Types class:

Kotlin

// Works with any database — just use the right types
val applyDiscount =
    DbProcedure.define("apply_discount")
        .input(OracleTypes.number)         // amount IN
        .inout(OracleTypes.varchar2)      // status INOUT
        .build()

val getBalance =
    DbFunction.define("get_balance", OracleTypes.number)
        .input(OracleTypes.varchar2)       // account_id
        .build()

Java

// Works with any database — just use the right types
void example() {
    var applyDiscount =
        DbProcedure.define("apply_discount")
            .input(OracleTypes.number)
            .inout(OracleTypes.varchar2)
            .build();

    var getBalance =
        DbFunction.define(
                "get_balance", OracleTypes.number)
            .input(OracleTypes.varchar2)
            .build();
}

Scala

// Works with any database — just use the right types
val applyDiscount =
  DbProcedure.define("apply_discount")
    .input(OracleTypes.number)         // amount IN
    .inout(OracleTypes.varchar2)    // status INOUT
    .build()

val getBalance =
  DbFunction.define("get_balance", OracleTypes.number)
    .input(OracleTypes.varchar2)       // account_id
    .build()

Show entire file

How It Works

Feature	Procedures (DbProcedure)	Functions (DbFunction)
SQL	{call proc_name(?, ...)}	SELECT func_name(?, ...)
Statement	CallableStatement	PreparedStatement
Inputs	.input()	.input()
Outputs	.out(), .inout()	Return type (single value)
Max arity	10 inputs, 10 outputs	10 inputs
Result	Void, single value, or Tuple	Single value

Verifying Against the Database

Use Query Analysis to verify that your procedure and function definitions match what's actually in the database — parameter count, types, modes, and return types are all checked.

Database Support

OUT parameter support requires a DbOutParam implementation for each type. All standard types are supported for all six databases:

Database	Procedures	Functions	OUT/INOUT
PostgreSQL	Yes	Yes	Yes
MariaDB/MySQL	Yes	Yes	Yes
DuckDB	N/A	N/A	No (no stored procedure support)
Oracle	Yes	Yes	Yes
SQL Server	Yes	Yes	Yes
DB2	Yes	Yes	Yes