存储过程¶

本文你会学到：

存储过程的优势和适用场景
CallableStatement 的两种调用语法
IN、OUT、INOUT 三种参数模式的区别和用法
registerOutParameter 注册输出参数类型

📖 为什么要把逻辑放到存储过程里？¶

存储过程（Stored Procedure）是数据库中预编译的一组 SQL 语句，封装为一个可复用的单元，通过名称调用。当你发现应用和数据库之间频繁往返执行多条 SQL 时，存储过程就派上用场了：

优势	说明
`减少网络往返`	一条调用代替多条 SQL，降低客户端与数据库之间的通信开销
`复用与一致性`	预编译后存储在数据库中，多个应用共享同一套逻辑
`安全（参数化）`	参数通过占位符传递，防止 SQL 注入，还可配合数据库权限控制访问
`封装业务逻辑`	将复杂查询和数据转换封装在数据库层，简化应用代码

📞 CallableStatement 基本用法¶

调用语法¶

CallableStatement 是 JDBC 中专门用于调用存储过程和存储函数的接口，通过 Connection.prepareCall() 创建。JDBC 定义了两种标准转义语法：

语法	适用场景	说明
`{call procedure_name(?, ?, ?)}`	存储过程	无返回值占位符，参数全为 `?`
`{? = call function_name(?, ?)}`	存储函数	第一个 `?` 是返回值占位符

完整调用流程：

CallableStatement 调用语法总结
    /**
     * CallableStatement 语法总结
     *
     * <p>JDBC 通过 Connection.prepareCall() 创建 CallableStatement，
     * 使用标准转义语法调用存储过程和函数：</p>
     *
     * <pre>
     * // 1. 调用无返回值的存储过程
     * {call procedure_name(?, ?, ?)}
     *
     * // 2. 调用有返回值的存储函数（第一个 ? 是返回值）
     * {? = call function_name(?, ?)}
     *
     * // 3. 调用无参数的存储过程
     * {call procedure_name}
     * </pre>
     *
     * <p>完整流程：
     * <pre>
     * try (CallableStatement cs = conn.prepareCall("{call proc(?, ?)}")) {
     *     // 1. 注册 OUT 参数类型
     *     cs.registerOutParameter(2, Types.VARCHAR);
     *     // 2. 设置 IN / INOUT 参数值
     *     cs.setString(1, "input");
     *     // 3. 执行
     *     cs.execute();
     *     // 4. 获取 OUT 参数值
     *     String result = cs.getString(2);
     * }
     * </pre>
     * </p>
     */
    @Test
    @DisplayName("语法总结：CallableStatement 调用模式")
    void testCallableSyntaxSummary() {
        // 此测试仅作为语法参考文档，验证 CallableStatement 的基本模式
        // 实际数据库（MySQL、Oracle）中的存储过程创建语法如下：
        //
        // MySQL 示例：
        //   CREATE PROCEDURE add_user(IN name VARCHAR(50), OUT id INT)
        //   BEGIN
        //       INSERT INTO users(name) VALUES(name);
        //       SET id = LAST_INSERT_ID();
        //   END
        //
        // Oracle 示例：
        //   CREATE PROCEDURE get_dept_name(p_dept_id IN NUMBER, p_name OUT VARCHAR2)
        //   AS
        //   BEGIN
        //       SELECT dept_name INTO p_name FROM dept WHERE dept_id = p_dept_id;
        //   END
        //
        // 调用方式统一使用 JDBC 转义语法：
        //   {call add_user(?, ?)}
        //   {call get_dept_name(?, ?)}

        System.out.println("CallableStatement 语法总结测试通过");
        assertTrue(true, "语法总结测试");
    }

参数模式¶

JDBC 存储过程支持三种参数模式，决定参数的数据流向：

模式	方向	说明
`IN`	输入	向存储过程传入值（默认模式）
`OUT`	输出	存储过程返回值，必须调用 `registerOutParameter()` 注册类型
`INOUT`	双向	传入初始值，存储过程修改后返回更新后的值

📥 IN 参数示例¶

IN 参数是最常用的模式，通过 setString()、setInt() 等方法设置传入值。当存储函数有返回值时，使用 {? = call ...} 语法，并通过 registerOutParameter() 注册返回值类型。

IN 参数：调用带返回值的存储函数
    /**
     * 演示带 IN 参数的存储函数调用
     *
     * <p>使用 {@code {? = call function_name(?)}} 语法调用有返回值的存储函数。
     * 第一个 {@code ?} 是返回值占位符，需要通过 registerOutParameter 注册。</p>
     */
    @Test
    @DisplayName("IN 参数：调用有返回值的存储函数")
    void testInParams() throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL)) {
            // 创建一个简单的存储函数：接收姓名，返回问候语
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("CREATE ALIAS greet AS $$\n"
                        + "String greet(String name) { return \"Hello, \" + name; }\n"
                        + "$$");
            }

            // 使用 CallableStatement 调用存储函数
            // ? = call greet(?) — 第一个 ? 是返回值，第二个 ? 是 IN 参数
            try (CallableStatement cs = conn.prepareCall("{? = call greet(?)}")) {
                // 注册返回值参数（索引 1）
                cs.registerOutParameter(1, Types.VARCHAR);
                // 设置 IN 参数（索引 2）
                cs.setString(2, "World");

                // 执行存储函数调用
                cs.execute();

                // 获取返回值
                String result = cs.getString(1);
                System.out.println("存储函数返回值: " + result);

                assertEquals("Hello, World", result, "应返回问候语");
            }
        }
    }

📤 OUT 参数示例¶

OUT 参数用于从存储过程中获取输出值。调用前必须通过 registerOutParameter() 注册参数的 SQL 类型，执行后通过 getString()、getInt() 等方法读取返回值。

OUT 参数：获取存储过程的输出值
    /**
     * 演示带 OUT 参数的存储过程调用
     *
     * <p>使用 {@code {call procedure_name(?, ?)}} 语法调用存储过程。
     * OUT 参数需要通过 registerOutParameter 注册类型，执行后通过 getString 获取值。</p>
     *
     * <p>注意：H2 数据库没有原生 OUT 参数支持。这里使用返回 {@link ResultSet} 的函数来模拟
     * 存储过程的输出行为。在真实数据库（MySQL、Oracle）中，OUT 参数是原生支持的：</p>
     * <pre>
     * // MySQL 原生 OUT 参数示例
     * CREATE PROCEDURE get_status(IN input VARCHAR(50), OUT status VARCHAR(100))
     * BEGIN
     *     SET status = CONCAT('OK: ', input);
     * END
     * // JDBC 调用
     * cs.registerOutParameter(2, Types.VARCHAR);
     * cs.setString(1, "test");
     * cs.execute();
     * String result = cs.getString(2);
     * </pre>
     */
    @Test
    @DisplayName("OUT 参数：模拟带输出参数的存储过程")
    void testOutParams() throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL)) {
            // H2 中使用返回 ResultSet 的函数模拟 OUT 参数
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("CREATE ALIAS get_status AS $$\n"
                        + "ResultSet getStatus(Connection conn, String input) throws SQLException {\n"
                        + "    return conn.createStatement().executeQuery(\n"
                        + "        \"SELECT 'OK: \" + input + \"' AS status\");\n"
                        + "}\n"
                        + "$$");
            }

            // 使用 CallableStatement 调用
            try (CallableStatement cs = conn.prepareCall("{call get_status(?)}")) {
                // 设置 IN 参数
                cs.setString(1, "test");

                // 执行并获取返回的 ResultSet
                try (ResultSet rs = cs.executeQuery()) {
                    assertTrue(rs.next(), "应返回一行结果");
                    String status = rs.getString("status");
                    System.out.println("OUT 参数模拟返回的状态: " + status);

                    assertEquals("OK: test", status, "应返回包含输入的状态信息");
                }
            }
        }
    }

H2 数据库不支持原生 OUT 参数，示例中通过返回 ResultSet 的函数模拟。在 MySQL / Oracle 中，OUT 参数是原生支持的，调用方式见代码注释中的 MySQL 示例。

🔄 INOUT 参数示例¶

INOUT 参数兼具输入和输出功能：调用前设置初始值，存储过程内部修改后，通过同一个参数索引获取更新后的值。

INOUT 参数：传入初始值并获取修改后的值
    /**
     * 演示 INOUT 参数（输入输出参数）
     *
     * <p>INOUT 参数同时承担输入和输出角色：调用前设置值，执行后获取修改后的值。
     * 在 MySQL 中用 INOUT 关键字声明，Oracle 中同样支持。</p>
     *
     * <p>H2 数据库没有原生 INOUT 参数支持，这里使用返回 {@link ResultSet} 的函数来模拟。
     * 在真实数据库中的用法：</p>
     * <pre>
     * // MySQL 原生 INOUT 参数示例
     * CREATE PROCEDURE modify_value(INOUT val VARCHAR(100), IN suffix VARCHAR(50))
     * BEGIN
     *     SET val = CONCAT(val, ' ', suffix);
     * END
     * // JDBC 调用
     * cs.setString(1, "Hello");      // 设置 INOUT 初始值
     * cs.setString(2, "World");       // 设置 IN 参数
     * cs.registerOutParameter(1, Types.VARCHAR);  // 注册 INOUT 输出类型
     * cs.execute();
     * String result = cs.getString(1);  // 获取修改后的值
     * </pre>
     */
    @Test
    @DisplayName("INOUT 参数：模拟输入输出参数的存储过程")
    void testInOutParams() throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL)) {
            // H2 中使用返回 ResultSet 的函数模拟 INOUT 参数
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("CREATE ALIAS modify_value AS $$\n"
                        + "ResultSet modifyValue(Connection conn, String initialValue, String suffix) throws SQLException {\n"
                        + "    String modified = initialValue + \" \" + suffix;\n"
                        + "    return conn.createStatement().executeQuery(\n"
                        + "        \"SELECT '\" + modified + \"' AS modified_value\");\n"
                        + "}\n"
                        + "$$");
            }

            // 使用 CallableStatement 调用
            try (CallableStatement cs = conn.prepareCall("{call modify_value(?, ?)}")) {
                // 设置 INOUT 参数的初始值（索引 1）
                cs.setString(1, "Hello");
                // 设置 IN 参数（索引 2）
                cs.setString(2, "World");

                // 执行并获取模拟的 INOUT 返回值
                try (ResultSet rs = cs.executeQuery()) {
                    assertTrue(rs.next(), "应返回一行结果");
                    String modifiedValue = rs.getString("modified_value");
                    System.out.println("INOUT 参数修改后的值: " + modifiedValue);

                    assertEquals("Hello World", modifiedValue, "INOUT 参数应被追加后缀");
                }
            }
        }
    }

与 OUT 参数类似，H2 数据库不支持原生 INOUT 参数，示例通过返回 ResultSet 模拟。MySQL 原生写法见代码注释。

📝 registerOutParameter 注册输出参数¶

OUT 和 INOUT 参数在执行存储过程之前，必须调用 registerOutParameter() 注册输出参数的 SQL 类型，告知 JDBC 驱动如何解析返回值。

// 注册返回值类型（存储函数的第一个参数）
cs.registerOutParameter(1, Types.VARCHAR);

// 注册 OUT 参数类型
cs.registerOutParameter(2, Types.INTEGER);

常用的 java.sql.Types 常量：

Types 常量	对应 SQL 类型	获取方法
`Types.VARCHAR`	`VARCHAR` / `CHAR`	`getString()`
`Types.INTEGER`	`INT` / `INTEGER`	`getInt()`
`Types.BIGINT`	`BIGINT`	`getLong()`
`Types.DOUBLE`	`DOUBLE` / `FLOAT`	`getDouble()`
`Types.BOOLEAN`	`BOOLEAN`	`getBoolean()`
`Types.TIMESTAMP`	`TIMESTAMP`	`getTimestamp()`

H2 数据库限制

H2 不支持原生 OUT/INOUT 参数。上述示例通过返回 ResultSet 的函数模拟，代码注释中附带了 MySQL 原生写法。实际项目中如需使用存储过程，建议在 MySQL / PostgreSQL / Oracle 等完整功能数据库上测试。