Stored Procedures

-- Illustrative PostgreSQL (PL/pgSQL) stored procedure: applies a discount
-- and logs the change, all in one server-side round trip
CREATE OR REPLACE PROCEDURE apply_discount(p_product_id BIGINT, p_percent NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE products
    SET price = price * (1 - p_percent)
    WHERE id = p_product_id;

    INSERT INTO price_change_log (product_id, change_type, changed_at)
    VALUES (p_product_id, 'discount', NOW());
END;
$$;

-- Illustrative stored function (returns a value, unlike a procedure):
CREATE OR REPLACE FUNCTION calculate_tax(p_amount NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN p_amount * 0.08;
END;
$$;

/* The equivalent logic expressed entirely as separate statements from the
   application side would require two distinct round trips (UPDATE, then
   INSERT) rather than one call into a single server-side procedure. */

import java.sql.*;

public class InvokingStoredProcedureRecap {
    public static void applyDiscount(Connection conn, long productId, java.math.BigDecimal percent)
            throws SQLException {
        // Single round trip — the UPDATE and the audit-log INSERT both happen
        // inside the database as part of this one call
        try (CallableStatement cs = conn.prepareCall("{call apply_discount(?, ?)}")) {
            cs.setLong(1, productId);
            cs.setBigDecimal(2, percent);
            cs.execute();
        }
    }

    public static java.math.BigDecimal calculateTax(Connection conn, java.math.BigDecimal amount)
            throws SQLException {
        try (CallableStatement cs = conn.prepareCall("{? = call calculate_tax(?)}")) {
            cs.registerOutParameter(1, Types.NUMERIC);
            cs.setBigDecimal(2, amount);
            cs.execute();
            return cs.getBigDecimal(1);
        }
    }

    // ── Contrast: the same discount logic expressed as application-side
    // statements instead — two round trips rather than one ─────────────────
    public static void applyDiscountInApplicationCode(Connection conn, long productId, java.math.BigDecimal percent)
            throws SQLException {
        try (PreparedStatement updatePrice = conn.prepareStatement(
                "UPDATE products SET price = price * (1 - ?) WHERE id = ?")) {
            updatePrice.setBigDecimal(1, percent);
            updatePrice.setLong(2, productId);
            updatePrice.executeUpdate();   // round trip 1
        }
        try (PreparedStatement logChange = conn.prepareStatement(
                "INSERT INTO price_change_log (product_id, change_type, changed_at) VALUES (?, 'discount', NOW())")) {
            logChange.setLong(1, productId);
            logChange.executeUpdate();     // round trip 2
        }
    }
}

// There's no single "correct" code example for a tradeoffs discussion — but
// here's a concrete illustration of the kind of decision point teams face:
// a multi-step inventory adjustment that COULD be a stored procedure or
// COULD stay in application code.

// ── Option A: stored procedure — one round trip, vendor-specific dialect ──
// CREATE PROCEDURE adjust_inventory(p_product_id BIGINT, p_delta INT)
// LANGUAGE plpgsql AS $$
// BEGIN
//     UPDATE inventory SET quantity = quantity + p_delta WHERE product_id = p_product_id;
//     IF (SELECT quantity FROM inventory WHERE product_id = p_product_id) < 0 THEN
//         RAISE EXCEPTION 'Insufficient inventory for product %', p_product_id;
//     END IF;
//     INSERT INTO inventory_audit (product_id, delta, adjusted_at) VALUES (p_product_id, p_delta, NOW());
// END; $$;

import java.sql.*;

public class InventoryAdjustmentTradeoff {
    // ── Option B: same logic in application code — portable, testable in Java,
    // costs an extra round trip, and the invariant check happens in app code
    // rather than inside a single atomic database-side block ───────────────
    public static void adjustInventoryInApplicationCode(Connection conn, long productId, int delta)
            throws SQLException {
        conn.setAutoCommit(false);
        try {
            try (PreparedStatement update = conn.prepareStatement(
                    "UPDATE inventory SET quantity = quantity + ? WHERE product_id = ?")) {
                update.setInt(1, delta);
                update.setLong(2, productId);
                update.executeUpdate();
            }

            try (PreparedStatement check = conn.prepareStatement(
                    "SELECT quantity FROM inventory WHERE product_id = ?")) {
                check.setLong(1, productId);
                try (ResultSet rs = check.executeQuery()) {
                    rs.next();
                    if (rs.getInt("quantity") < 0) {
                        throw new SQLException("Insufficient inventory for product " + productId);
                    }
                }
            }

            try (PreparedStatement audit = conn.prepareStatement(
                    "INSERT INTO inventory_audit (product_id, delta, adjusted_at) VALUES (?, ?, NOW())")) {
                audit.setLong(1, productId);
                audit.setInt(2, delta);
                audit.executeUpdate();
            }

            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }
}