Procedimientos Almacenados y Funciones en H2

H2 es una base de datos relacional escrita en Java que es especialmente popular por su ligereza y facilidad de uso. Aunque es muy común utilizar H2 como una base de datos en memoria para pruebas, también es lo suficientemente potente para manejar operaciones más avanzadas, como procedimientos almacenados y funciones. 

Un procedimiento almacenado es un conjunto de instrucciones SQL que pueden ser almacenadas en la base de datos y ejecutadas cuando sea necesario. Los procedimientos almacenados son útiles porque permiten encapsular lógica compleja en un solo lugar, lo que facilita su mantenimiento y reutilización.

Las funciones son similares a los procedimientos almacenados, pero con algunas diferencias clave. Mientras que un procedimiento almacenado puede ejecutar una serie de comandos SQL y no necesariamente retornar un valor, una función siempre retorna un valor y está diseñada para ser utilizada en expresiones SQL.

En H2, los procedimientos almacenados se crean utilizando la palabra clave `CREATE ALIAS`. Veamos un ejemplo sencillo de cómo crear un procedimiento almacenado que inserta un nuevo registro en una tabla:

CREATE ALIAS insert_user AS $$

void insert_user(Connection conn, String username, String email) throws SQLException {

    PreparedStatement prep = conn.prepareStatement("INSERT INTO users(username, email) VALUES(?, ?)");

    prep.setString(1, username);

    prep.setString(2, email);

    prep.execute();

    prep.close();

}

$$;

Una vez que el procedimiento almacenado ha sido creado, puedes llamarlo usando `CALL`:

CALL insert_user('JohnDoe', 'john.doe@example.com');

Si necesitas que tu procedimiento devuelva resultados, puedes modificarlo ligeramente:

CREATE ALIAS get_user_by_id AS $$

ResultSet get_user_by_id(Connection conn, int id) throws SQLException {

    PreparedStatement prep = conn.prepareStatement("SELECT * FROM users WHERE id = ?");

    prep.setInt(1, id);

    return prep.executeQuery();

}

$$;

Luego puedes utilizar el procedimiento en una consulta:

CALL get_user_by_id(1);

Al igual que los procedimientos, las funciones en H2 se crean utilizando `CREATE ALIAS`, pero la diferencia es que las funciones siempre retornan un valor. Aquí tienes un ejemplo de una función que calcula el IVA de un precio dado:

CREATE ALIAS calculate_vat AS $$

double calculate_vat(double price) {

    return price * 0.21;

}

$$;

Una vez creada, puedes utilizar la función en tus consultas SQL:

SELECT calculate_vat(100) AS vat_amount;

Esto te devolverá el valor con el IVA aplicado.

H2 ofrece un soporte robusto para procedimientos almacenados y funciones, lo que lo hace muy útil incluso en aplicaciones más allá del simple almacenamiento de datos en memoria. Con la capacidad de encapsular lógica SQL compleja en procedimientos y funciones, puedes mantener tu código más limpio y organizado.