存储过程调用

存储过程是数据库中预编译的 SQL 语句集合，可以提高数据库操作的效率和安全性。MyBatis 支持调用存储过程，并处理输入参数、输出参数和返回结果集。

概述

什么是存储过程

存储过程（Stored Procedure）是一组为了完成特定功能的 SQL 语句集，经过编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程的优点

• 性能优化：预编译执行，减少网络传输
• 安全性：可以限制用户直接访问表，只通过存储过程操作
• 代码复用：业务逻辑封装在数据库层，多个应用可以共享
• 减少网络流量：一次调用执行多个操作

MyBatis 调用存储过程的方式

MyBatis 通过 statementType="CALLABLE" 来标识这是一个存储过程调用：

<select id="callProcedure" statementType="CALLABLE">
    {call procedure_name(#{param1}, #{param2})}
</select>

基本调用

创建测试存储过程

以 MySQL 为例，创建一个简单的存储过程：

-- 查询用户总数的存储过程
DELIMITER //
CREATE PROCEDURE get_user_count(OUT count INT)
BEGIN
    SELECT COUNT(*) INTO count FROM user;
END //
DELIMITER ;

-- 根据ID查询用户的存储过程
DELIMITER //
CREATE PROCEDURE get_user_by_id(IN user_id BIGINT)
BEGIN
    SELECT * FROM user WHERE id = user_id;
END //
DELIMITER ;

简单调用示例

Mapper 接口：

public interface UserMapper {
    
    /**
     * 调用无参数存储过程
     */
    void callGetUserCount(Map<String, Object> result);
    
    /**
     * 调用带输入参数的存储过程
     */
    User callGetUserById(Long id);
}

XML 映射文件：

<!-- 调用带输出参数的存储过程 -->
<select id="callGetUserCount" statementType="CALLABLE">
    {call get_user_count(#{count, mode=OUT, jdbcType=INTEGER})}
</select>

<!-- 调用带输入参数并返回结果集的存储过程 -->
<select id="callGetUserById" statementType="CALLABLE" resultType="User">
    {call get_user_by_id(#{id})}
</select>

调用示例：

@Test
void testGetUserCount() {
    SqlSession session = SqlSessionUtil.getSqlSession();
    UserMapper mapper = session.getMapper(UserMapper.class);
    
    Map<String, Object> result = new HashMap<>();
    mapper.callGetUserCount(result);
    
    System.out.println("用户总数: " + result.get("count"));
    session.close();
}

@Test
void testGetUserById() {
    SqlSession session = SqlSessionUtil.getSqlSession();
    UserMapper mapper = session.getMapper(UserMapper.class);
    
    User user = mapper.callGetUserById(1L);
    System.out.println("用户: " + user);
    session.close();
}

参数模式

MyBatis 支持三种参数模式，通过 mode 属性指定：

模式	说明	对应 JDBC
IN	输入参数（默认）	setXxx()
OUT	输出参数	registerOutParameter() + getXxx()
INOUT	输入输出参数	两者结合

IN 参数（输入参数）

输入参数用于向存储过程传递值：

<select id="callGetUserById" statementType="CALLABLE" resultType="User">
    {call get_user_by_id(#{id, mode=IN, jdbcType=BIGINT})}
</select>

mode=IN 是默认值，可以省略：

<select id="callGetUserById" statementType="CALLABLE" resultType="User">
    {call get_user_by_id(#{id})}
</select>

OUT 参数（输出参数）

输出参数用于从存储过程获取返回值：

-- 创建带输出参数的存储过程
DELIMITER //
CREATE PROCEDURE get_user_status(
    IN user_id BIGINT,
    OUT user_status INT,
    OUT user_email VARCHAR(100)
)
BEGIN
    SELECT status, email INTO user_status, user_email 
    FROM user WHERE id = user_id;
END //
DELIMITER ;

<select id="callGetUserStatus" statementType="CALLABLE">
    {call get_user_status(
        #{userId, mode=IN, jdbcType=BIGINT},
        #{status, mode=OUT, jdbcType=INTEGER},
        #{email, mode=OUT, jdbcType=VARCHAR}
    )}
</select>

Mapper 接口：

void callGetUserStatus(Map<String, Object> params);

调用示例：

@Test
void testGetUserStatus() {
    SqlSession session = SqlSessionUtil.getSqlSession();
    UserMapper mapper = session.getMapper(UserMapper.class);
    
    Map<String, Object> params = new HashMap<>();
    params.put("userId", 1L);
    
    mapper.callGetUserStatus(params);
    
    System.out.println("状态: " + params.get("status"));
    System.out.println("邮箱: " + params.get("email"));
    session.close();
}

INOUT 参数（输入输出参数）

输入输出参数既可以传入值，也可以获取返回值：

-- 创建带 INOUT 参数的存储过程
DELIMITER //
CREATE PROCEDURE double_value(INOUT num INT)
BEGIN
    SET num = num * 2;
END //
DELIMITER ;

<select id="callDoubleValue" statementType="CALLABLE">
    {call double_value(#{num, mode=INOUT, jdbcType=INTEGER})}
</select>

调用示例：

@Test
void testDoubleValue() {
    SqlSession session = SqlSessionUtil.getSqlSession();
    UserMapper mapper = session.getMapper(UserMapper.class);
    
    Map<String, Object> params = new HashMap<>();
    params.put("num", 10);
    
    mapper.callDoubleValue(params);
    
    System.out.println("结果: " + params.get("num")); // 输出: 20
    session.close();
}

返回结果集

存储过程可以返回一个或多个结果集，MyBatis 能够将结果集映射为 Java 对象。

返回单个结果集

-- 返回结果集的存储过程
DELIMITER //
CREATE PROCEDURE get_active_users()
BEGIN
    SELECT * FROM user WHERE status = 1 ORDER BY id DESC;
END //
DELIMITER ;

<select id="callGetActiveUsers" statementType="CALLABLE" resultType="User">
    {call get_active_users()}
</select>

Mapper 接口：

List<User> callGetActiveUsers();

调用示例：

@Test
void testGetActiveUsers() {
    SqlSession session = SqlSessionUtil.getSqlSession();
    UserMapper mapper = session.getMapper(UserMapper.class);
    
    List<User> users = mapper.callGetActiveUsers();
    users.forEach(System.out::println);
    session.close();
}

返回多个结果集

某些数据库支持存储过程返回多个结果集。MyBatis 可以通过 resultSets 属性处理：

-- MySQL 返回多个结果集的存储过程
DELIMITER //
CREATE PROCEDURE get_user_with_orders(IN user_id BIGINT)
BEGIN
    -- 第一个结果集：用户信息
    SELECT * FROM user WHERE id = user_id;
    -- 第二个结果集：订单信息
    SELECT * FROM orders WHERE user_id = user_id;
END //
DELIMITER ;

<select id="callGetUserWithOrders" 
        statementType="CALLABLE" 
        resultType="User"
        resultSets="users,orders">
    {call get_user_with_orders(#{userId})}
</select>

处理多结果集：

@Test
void testGetUserWithOrders() {
    SqlSession session = SqlSessionUtil.getSqlSession();
    UserMapper mapper = session.getMapper(UserMapper.class);
    
    // 使用 SqlSession 直接调用
    List<User> users = session.selectList(
        "com.example.mybatis.mapper.UserMapper.callGetUserWithOrders", 1L);
    
    // 获取第二个结果集
    List<Order> orders = session.selectList(
        "com.example.mybatis.mapper.UserMapper.callGetUserWithOrders!orders", 1L);
    
    System.out.println("用户: " + users);
    System.out.println("订单: " + orders);
    session.close();
}

使用 resultMap 处理结果

当存储过程返回复杂的结果集时，可以使用 resultMap 进行映射：

<resultMap id="UserWithOrdersMap" type="User">
    <id column="id" property="id"/>
    <result column="username" property="username"/>
    <result column="email" property="email"/>
    <collection property="orders" ofType="Order">
        <id column="order_id" property="id"/>
        <result column="order_no" property="orderNo"/>
        <result column="amount" property="amount"/>
    </collection>
</resultMap>

<select id="callGetUserWithOrders" 
        statementType="CALLABLE" 
        resultMap="UserWithOrdersMap">
    {call get_user_with_orders(#{userId})}
</select>

Oracle 游标处理

Oracle 数据库的存储过程通常使用游标（REF CURSOR）返回结果集：

创建 Oracle 存储过程

-- Oracle 存储过程返回游标
CREATE OR REPLACE PROCEDURE get_users_cursor(
    p_cursor OUT SYS_REFCURSOR
)
AS
BEGIN
    OPEN p_cursor FOR
    SELECT * FROM user ORDER BY id;
END;

MyBatis 配置

<resultMap id="UserResultMap" type="User">
    <id column="id" property="id"/>
    <result column="username" property="username"/>
    <result column="email" property="email"/>
</resultMap>

<select id="callGetUsersCursor" 
        statementType="CALLABLE" 
        resultMap="UserResultMap">
    {call get_users_cursor(
        #{cursor, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=UserResultMap}
    )}
</select>

Mapper 接口：

void callGetUsersCursor(Map<String, Object> params);

调用示例：

@Test
void testGetUsersCursor() {
    SqlSession session = SqlSessionUtil.getSqlSession();
    UserMapper mapper = session.getMapper(UserMapper.class);
    
    Map<String, Object> params = new HashMap<>();
    mapper.callGetUsersCursor(params);
    
    // 获取游标返回的结果集
    @SuppressWarnings("unchecked")
    List<User> users = (List<User>) params.get("cursor");
    users.forEach(System.out::println);
    session.close();
}

Oracle 复杂示例

-- Oracle 带输入输出参数的存储过程
CREATE OR REPLACE PROCEDURE get_user_info(
    p_user_id IN NUMBER,
    p_user OUT SYS_REFCURSOR,
    p_order_count OUT NUMBER
)
AS
BEGIN
    -- 返回用户信息游标
    OPEN p_user FOR
    SELECT * FROM user WHERE id = p_user_id;
    
    -- 返回订单数量
    SELECT COUNT(*) INTO p_order_count 
    FROM orders WHERE user_id = p_user_id;
END;

<select id="callGetUserInfo" statementType="CALLABLE">
    {call get_user_info(
        #{userId, mode=IN, jdbcType=NUMERIC},
        #{user, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=UserResultMap},
        #{orderCount, mode=OUT, jdbcType=NUMERIC}
    )}
</select>

使用注解调用存储过程

MyBatis 注解方式也支持调用存储过程：

public interface UserMapper {
    
    /**
     * 使用 @Select 注解调用存储过程
     */
    @Select(value = "{call get_user_count(#{count, mode=OUT, jdbcType=INTEGER})}")
    @Options(statementType = StatementType.CALLABLE)
    void callGetUserCount(Map<String, Object> result);
    
    /**
     * 调用带输入参数的存储过程
     */
    @Select(value = "{call get_user_by_id(#{id})}")
    @Options(statementType = StatementType.CALLABLE)
    @ResultType(User.class)
    User callGetUserById(Long id);
    
    /**
     * 使用 @SelectProvider 动态生成调用语句
     */
    @SelectProvider(type = UserSqlProvider.class, method = "callProcedure")
    @Options(statementType = StatementType.CALLABLE)
    void callDynamicProcedure(Map<String, Object> params);
}

class UserSqlProvider {
    public String callProcedure(Map<String, Object> params) {
        return "{call get_user_by_status(" + params.get("status") + ")}";
    }
}

实际应用示例

分页存储过程

-- MySQL 分页存储过程
DELIMITER //
CREATE PROCEDURE get_users_by_page(
    IN page_num INT,
    IN page_size INT,
    OUT total_count INT
)
BEGIN
    -- 计算偏移量
    SET @offset = (page_num - 1) * page_size;
    
    -- 查询总数
    SELECT COUNT(*) INTO total_count FROM user;
    
    -- 分页查询
    SELECT * FROM user 
    ORDER BY id DESC 
    LIMIT page_size OFFSET @offset;
END //
DELIMITER ;

<select id="callGetUsersByPage" statementType="CALLABLE" resultType="User">
    {call get_users_by_page(
        #{pageNum, mode=IN, jdbcType=INTEGER},
        #{pageSize, mode=IN, jdbcType=INTEGER},
        #{totalCount, mode=OUT, jdbcType=INTEGER}
    )}
</select>

@Test
void testGetUsersByPage() {
    SqlSession session = SqlSessionUtil.getSqlSession();
    UserMapper mapper = session.getMapper(UserMapper.class);
    
    Map<String, Object> params = new HashMap<>();
    params.put("pageNum", 1);
    params.put("pageSize", 10);
    
    List<User> users = session.selectList(
        "com.example.mybatis.mapper.UserMapper.callGetUsersByPage", params);
    
    System.out.println("总数: " + params.get("totalCount"));
    System.out.println("用户列表: " + users);
    session.close();
}

批量操作存储过程

-- 批量插入用户的存储过程
DELIMITER //
CREATE PROCEDURE batch_insert_users(
    IN user_names VARCHAR(1000),
    IN user_password VARCHAR(100)
)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE name VARCHAR(50);
    
    WHILE i <= (LENGTH(user_names) - LENGTH(REPLACE(user_names, ',', '')) + 1) DO
        SET name = SUBSTRING_INDEX(SUBSTRING_INDEX(user_names, ',', i), ',', -1);
        INSERT INTO user (username, password) VALUES (name, user_password);
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

<update id="callBatchInsertUsers" statementType="CALLABLE">
    {call batch_insert_users(#{userNames}, #{password})}
</update>

复杂业务存储过程

-- 订单处理存储过程
DELIMITER //
CREATE PROCEDURE process_order(
    IN p_user_id BIGINT,
    IN p_amount DECIMAL(10,2),
    OUT p_order_id BIGINT,
    OUT p_result INT,
    OUT p_message VARCHAR(200)
)
BEGIN
    DECLARE v_balance DECIMAL(10,2);
    DECLARE v_status INT;
    
    -- 获取用户状态和余额
    SELECT status, balance INTO v_status, v_balance 
    FROM user WHERE id = p_user_id;
    
    -- 检查用户状态
    IF v_status != 1 THEN
        SET p_result = -1;
        SET p_message = '用户状态异常';
    ELSEIF v_balance < p_amount THEN
        SET p_result = -2;
        SET p_message = '余额不足';
    ELSE
        -- 创建订单
        INSERT INTO orders (user_id, amount, status, create_time)
        VALUES (p_user_id, p_amount, 1, NOW());
        
        SET p_order_id = LAST_INSERT_ID();
        
        -- 扣减余额
        UPDATE user SET balance = balance - p_amount 
        WHERE id = p_user_id;
        
        SET p_result = 1;
        SET p_message = '订单创建成功';
    END IF;
END //
DELIMITER ;

<insert id="callProcessOrder" statementType="CALLABLE">
    {call process_order(
        #{userId, mode=IN, jdbcType=BIGINT},
        #{amount, mode=IN, jdbcType=DECIMAL},
        #{orderId, mode=OUT, jdbcType=BIGINT},
        #{result, mode=OUT, jdbcType=INTEGER},
        #{message, mode=OUT, jdbcType=VARCHAR}
    )}
</insert>

@Test
void testProcessOrder() {
    SqlSession session = SqlSessionUtil.getSqlSession();
    UserMapper mapper = session.getMapper(UserMapper.class);
    
    Map<String, Object> params = new HashMap<>();
    params.put("userId", 1L);
    params.put("amount", new BigDecimal("100.00"));
    
    mapper.callProcessOrder(params);
    
    System.out.println("订单ID: " + params.get("orderId"));
    System.out.println("结果: " + params.get("result"));
    System.out.println("消息: " + params.get("message"));
    
    session.commit();
    session.close();
}

最佳实践

1. 合理选择存储过程 vs 普通 SQL

适合使用存储过程的场景：

• 复杂的业务逻辑需要多次数据库操作
• 需要在数据库层面保证事务一致性
• 性能敏感的批量操作
• 安全要求高，需要隐藏表结构

不适合使用存储过程的场景：

• 简单的 CRUD 操作
• 业务逻辑变化频繁
• 需要跨数据库兼容
• 团队缺乏数据库开发经验

2. 参数命名规范

<!-- 推荐：使用清晰的参数名 -->
<select id="callGetUser" statementType="CALLABLE">
    {call get_user(
        #{userId, mode=IN, jdbcType=BIGINT},
        #{userName, mode=OUT, jdbcType=VARCHAR},
        #{userEmail, mode=OUT, jdbcType=VARCHAR}
    )}
</select>

<!-- 不推荐：使用模糊的参数名 -->
<select id="callGetUser" statementType="CALLABLE">
    {call get_user(#{p1}, #{p2, mode=OUT}, #{p3, mode=OUT})}
</select>

3. 正确指定 jdbcType

对于 OUT 参数，必须指定 jdbcType，否则会报错：

<!-- 正确 -->
<select id="callProcedure" statementType="CALLABLE">
    {call my_proc(#{count, mode=OUT, jdbcType=INTEGER})}
</select>

<!-- 错误：OUT 参数缺少 jdbcType -->
<select id="callProcedure" statementType="CALLABLE">
    {call my_proc(#{count, mode=OUT})}
</select>

4. 使用 Map 或 POJO 接收输出参数

// 方式一：使用 Map
void callProcedure(Map<String, Object> params);

// 方式二：使用 POJO
void callProcedure(ProcedureResult result);

// POJO 示例
@Data
public class ProcedureResult {
    private Integer count;
    private String message;
}

5. 事务处理

存储过程内部的事务与 MyBatis 的事务是关联的：

@Test
void testTransaction() {
    SqlSession session = SqlSessionUtil.getSqlSession();
    try {
        UserMapper mapper = session.getMapper(UserMapper.class);
        
        // 调用存储过程
        mapper.callProcedure(...);
        
        // 其他操作
        mapper.insert(...);
        
        // 统一提交
        session.commit();
    } catch (Exception e) {
        // 存储过程内的操作也会回滚
        session.rollback();
    } finally {
        session.close();
    }
}

6. 错误处理

存储过程的错误会作为 SQLException 抛出：

@Test
void testErrorHandling() {
    SqlSession session = SqlSessionUtil.getSqlSession();
    UserMapper mapper = session.getMapper(UserMapper.class);
    
    try {
        Map<String, Object> params = new HashMap<>();
        params.put("userId", -1L);
        mapper.callProcedure(params);
    } catch (PersistenceException e) {
        // 处理存储过程执行错误
        System.err.println("存储过程执行失败: " + e.getMessage());
    } finally {
        session.close();
    }
}

不同数据库的差异

MySQL

<!-- MySQL 使用标准语法 -->
<select id="callProcedure" statementType="CALLABLE">
    {call procedure_name(#{param1}, #{param2})}
</select>

Oracle

<!-- Oracle 需要处理游标 -->
<select id="callProcedure" statementType="CALLABLE">
    {call procedure_name(
        #{cursor, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=MyResultMap}
    )}
</select>

SQL Server

<!-- SQL Server 存储过程可能有返回值 -->
<select id="callProcedure" statementType="CALLABLE">
    {? = call procedure_name(#{param1})}
</select>

PostgreSQL

<!-- PostgreSQL 函数调用 -->
<select id="callFunction" statementType="CALLABLE">
    {call function_name(#{param1}, #{param2})}
</select>

小结

本章介绍了 MyBatis 调用存储过程的方法：

• 基本概念：存储过程的优点和使用场景
• 参数模式：IN、OUT、INOUT 三种参数模式
• 结果集处理：单个和多个结果集的处理方式
• Oracle 游标：处理 Oracle 的 REF CURSOR
• 注解方式：使用注解调用存储过程
• 实际应用：分页、批量操作、复杂业务示例
• 最佳实践：参数命名、事务处理、错误处理

存储过程是一种强大的数据库特性，合理使用可以提高性能和安全性，但也需要注意维护成本和可移植性问题。