简介针对MyBatis将List类型参数传递给Oracle存储过程这一常见痛点这份PDF资源整理了一套可直接落地的解决方法适合正在使用MyBatis做批量插入、希望摆脱foreach循环限制并灵活控制事务回滚的Java后端开发人员。内容覆盖数据库表设计、Oracle对象类型与数组类型创建、存储过程编写、MyBatis parameterMap与自定义TypeHandler配置等关键环节通过将List封装为Oracle ARRAY类型最终实现批量插入并获得执行结果。资料为单个PDF文件压缩包约61KB已有1961人学习。PDF中附有核心代码片段、XML配置示例和调用端写法并解释了该方法相比MyBatis自带批量插入的优势读者可据此快速复现并迁移到自己的项目中。1. 传list给Oracle存储过程为什么MyBatis直接炸了最近调一个批量接口业务上要把上千条员工编号一次性传到Oracle存储过程里做去重校验和状态更新。Mapper里写了IN (参数)没用存储过程要的是一个list于是我想当然地把ListString直接塞进存储过程入参结果MyBatis报了一串ORA-06550根本定位不了问题。查了半天才明白Oracle存储过程根本不能声明一个LIST参数它只认对象类型、数组或游标而MyBatis默认把List当普通Java对象传给JDBCOracle驱动并不认这个账。这个标题真正要解决的就是“如何在MyBatis里把List参数安全地传给Oracle存储过程”核心有两条路自定义TypeHandler去适配Oracle集合类型或者用临时表做中转。适合正在用MyBatis Oracle开发后台服务、被批量参数折磨过的Java工程师。2. 先看参数类型Oracle存储过程到底能接什么list2.1 存储过程的“list”是类型对象不是泛型Oracle PL/SQL里没有像Java那样的ListT。如果你写过存储过程知道入参可以是数字、字符串、日期、游标但接收一个“多个值”的入参时最常见的是两种方式一种是声明一个自定义TYPE另一种是用临时表作为中转。真正在存储过程内部把入参当作集合遍历的写法必须先定义类型比如CREATE OR REPLACE TYPE P_VARCHAR2_TABLE AS TABLE OF VARCHAR2(64);这个P_VARCHAR2_TABLE就是Oracle里的一张“内存表类型”创建完以后存储过程才能拿它当入参CREATE OR REPLACE PROCEDURE PRO_BATCH_UPDATE( P_LIST IN P_VARCHAR2_TABLE, P_COUNT OUT NUMBER ) AS BEGIN P_COUNT : 0; -- 遍历集合入参的常见写法 FOR i IN 1 .. P_LIST.COUNT LOOP IF P_LIST(i) IS NOT NULL THEN P_COUNT : P_COUNT 1; END IF; END LOOP; END; /这里P_LIST实际上是一个“表类型”变量P_LIST(i)类似Java里的list.get(i)。注意P_COUNT是OUT参数用来给调用方返回处理行数。这个结构看起来不复杂但到了MyBatis这一侧就麻烦MyBatis的Mapper接口写的是ListString它不知道要把List转换成一个P_VARCHAR2_TABLE对象JDBC标准的setObject()也不会自动创建Oracle的集合类型。2.2 MyBatis的CALLABLE语句与JDBC类型映射MyBatis调用存储过程时XML里的statement通常要标成statementTypeCALLABLE这样MyBatis才会走CallableStatement。但关键是参数绑定的过程——MyBatis在CallableStatement.setObject()时只能识别数据库的VARCHAR、NUMBER、DATE这类基础类型。你传一个ArrayList进去驱动端没法把它理解成Oracle的TABLE OF VARCHAR2对象于是报错。解决思路有两个方向要么自定义一个TypeHandler让MyBatis在参数入参时主动创建Oracle集合对象要么不用集合入参先把List写到一张临时表存储过程再去查临时表。后一种方式避开了JDBC对Oracle类型的适配问题牺牲了一点性能但换来的是兼容性和维护性。方案三则是最不推荐的循环调用逐条执行存储过程数据量一上去连接等待和日志都能把接口拖垮。2.3 三分方案选型TypeHandler、临时表、循环调用方案实现要点优点缺点适合场景自定义TypeHandler写一个TypeHandler把List转成Oracle的ARRAY对象存储过程入参语义清晰一条SQL完成需要写Java扩展代码类型名和驱动细节耦合数据量中等要求最少的SQL操作临时表中转先批量insert到临时表再调用存储过程不用处理Oracle类型调试方便接近“所见即所得”多一次插入操作事务控制更严格数据量大、改动周期短、团队对SQL更熟循环逐条调用Mapper里foreach循环调用存储过程实现最简单慢、开销大、每一条一次网络往返数据量几十条以内且没有批量要求我个人在日常开发中数据量在几百条以内用TypeHandler上万条或同时有复杂校验业务就用临时表。循环调用基本只在老项目维护时见过新代码不要这么写。3. 方案一自定义TypeHandler把List转成Oracle集合类型3.1 先建类型和存储过程跑通SQL层别急着写Java先在SQL客户端里把Oracle侧的类型和存储过程准备好确认能手动执行。以最常用的字符串列表为例CREATE OR REPLACE TYPE P_VARCHAR2_TABLE AS TABLE OF VARCHAR2(64); / CREATE OR REPLACE PROCEDURE PRO_BATCH_UPDATE( P_LIST IN P_VARCHAR2_TABLE, P_COUNT OUT NUMBER ) AS BEGIN P_COUNT : 0; FOR i IN 1 .. P_LIST.COUNT LOOP IF P_LIST(i) IS NOT NULL THEN P_COUNT : P_COUNT 1; END IF; END LOOP; END; /测试调用DECLARE V_LIST P_VARCHAR2_TABLE; V_COUNT NUMBER; BEGIN V_LIST : P_VARCHAR2_TABLE(A001, B002, C003); PRO_BATCH_UPDATE(V_LIST, V_COUNT); DBMS_OUTPUT.PUT_LINE(count || V_COUNT); END; /这里的关键是P_VARCHAR2_TABLE(A001,B002,C003)这种构造方法它把一个字符串序列直接生成一个表类型实例。存储过程内部用P_LIST.COUNT拿到元素个数用P_LIST(i)遍历。能跑通这一步说明类型定义和过程逻辑没问题接下来才是MyBatis的适配。3.2 写TypeHandler核心是OracleConnection.createARRAYMyBatis的TypeHandler接口不复杂核心要覆盖四个方法。我这里选择继承BaseTypeHandlerListString因为只需要处理入参setNonNullParameter查询和游标结果集转换可以先留空或者抛异常。Java代码是这样的import oracle.sql.ARRAY; import org.apache.ibatis.type.BaseTypeHandler; import org.apache.ibatis.type.JdbcType; import org.apache.ibatis.type.MappedJdbcTypes; import org.apache.ibatis.type.MappedTypes; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; MappedTypes(List.class) MappedJdbcTypes(JdbcType.ARRAY) public class OracleStringListTypeHandler extends BaseTypeHandlerListString { private static final String TYPE_NAME P_VARCHAR2_TABLE; Override public void setNonNullParameter(PreparedStatement ps, int i, ListString parameter, JdbcType jdbcType) throws SQLException { Connection connection ps.getConnection(); // 关键一步把 List 转成 Oracle 的 ARRAY 对象 ARRAY oracleArray createOracleArray(connection, parameter); ps.setArray(i, oracleArray); } private ARRAY createOracleArray(Connection connection, ListString list) throws SQLException { // OracleConnection 是 oracle.jdbc 包下的实现类 oracle.jdbc.OracleConnection oracleConn connection.unwrap(oracle.jdbc.OracleConnection.class); return oracleConn.createARRAY(TYPE_NAME, list.toArray(new String[0])); } Override public ListString getNullableResult(ResultSet rs, String columnName) throws SQLException { throw new UnsupportedOperationException(查询结果集转List暂不支持); } Override public ListString getNullableResult(ResultSet rs, int columnIndex) throws SQLException { throw new UnsupportedOperationException(查询结果集转List暂不支持); } Override public ListString getNullableResult(CallableStatement cs, int columnIndex) throws SQLException { // 这里可以处理 OUT 参数返回集合但本方案只处理 IN 参数 throw new UnsupportedOperationException(OUT参数返回List暂不支持); } }逻辑说明setNonNullParameter是MyBatis在绑定参数时调用的方法。先拿PreparedStatement再通过getConnection()拿到底层连接用unwrap方法把连接转换成oracle.jdbc.OracleConnection最后用createARRAY(TYPE_NAME, array)生成Oracle数据库能识别的数组对象。这么做绕过了JDBC的基本类型映射直接把Java数组塞给了Oracle驱动。参数说明TYPE_NAME必须和SQL里定义的P_VARCHAR2_TABLE完全一样包括大小写否则报ORA-22806。ps.setArray(i, oracleArray)是JDBC标准方法但实际驱动内部会调用Oracle的ARRAY处理器。如果你的Oracle驱动版本是12c之后推荐用connection.unwrap而不是强转因为后面的驱动版本可能不再兼容oracle.jdbc.OracleConnection的强制类型转换。3.3 在Mapper XML里注册TypeHandler并调用Mapper接口声明很简单public interface BatchMapper { MapString, Object callBatchUpdate(Param(list) ListString list); }XML对应的写法select idcallBatchUpdate statementTypeCALLABLE parameterTypemap {call PRO_BATCH_UPDATE( #{list, modeIN, jdbcTypeARRAY, typeHandlercom.example.handler.OracleStringListTypeHandler}, #{count, modeOUT, jdbcTypeNUMERIC} )} /select这里要把存储过程的调用包在call里注意每个参数都要显式声明mode。list参数用了jdbcTypeARRAY这是为了让MyBatis在元数据检查时能预判参数类型真正起作用的是typeHandler。count参数是OUT类型调用完成后会被写入同一个Map的count字段。逻辑说明MyBatis看到statementTypeCALLABLE后会创建CallableStatement然后在注册参数时遇到list参数就会调用我们写的OracleStringListTypeHandler。modeIN表示入参modeOUT表示返回值。调用结果通过MapString, Object返回count键就能拿到存储过程返回的数字。参数说明parameterTypemap完全可以省略因为接口方法只有一个Param参数MyBatis会自动包装成Map。jdbcTypeARRAY不是所有驱动都支持但如果映射的是Oracle的全局类型这个值最贴近语义。如果改成jdbcTypeOTHER在某些版本也能跑通可维护性差一点。3.4 关键细节TypeHandler生效条件与驱动版本TypeHandler能不能生效取决于两件事第一Mapper接口的参数注解Param名称必须和XML里的参数名一致第二SQL中必须显式写上typeHandler或者全局配置里注册了这个TypeHandler并且类型匹配。只写MappedTypes(List.class)并不能保证所有List参数都自动走这个HandlerMyBatis的自动映射能力很薄弱碰到存储过程参数时几乎都需要在XML里显式声明。我用的驱动是ojdbc8createARRAY方法的签名在Oracle 12c以后仍是稳定的。但11g及之前的驱动没有unwrap(OracleConnection.class)方法吗实际上也有但更老的版本可能要用DriverManager.getConnection拿到的连接再强转。如果你碰上ClassCastException大概率是驱动太旧建议尽快升级到ojdbc8或ojdbc11。4. 方案二临时表中转数据量大时更稳4.1 临时表到底用什么类型用会话级还是事务级Oracle的临时表有两个方向ON COMMIT PRESERVE ROWS会话级和ON COMMIT DELETE ROWS事务级。如果存储过程调用和临时表写入在同一个事务里我推荐事务级如果临时表数据在一个会话里需要被多次引用比如分页拉取后再处理就用会话级。CREATE GLOBAL TEMPORARY TABLE TEMP_EMP_LIST( EMP_NO VARCHAR2(64) ) ON COMMIT DELETE ROWS;存储过程里直接查这张表CREATE OR REPLACE PROCEDURE PRO_BATCH_UPDATE( P_COUNT OUT NUMBER ) AS BEGIN P_COUNT : 0; FOR rec IN (SELECT EMP_NO FROM TEMP_EMP_LIST WHERE EMP_NO IS NOT NULL) LOOP P_COUNT : P_COUNT 1; -- 这里可以做业务更新例如对每一行做状态置为Y UPDATE EMP SET STATUS Y WHERE EMP_NO rec.EMP_NO; END LOOP; COMMIT; END; /临时表方案把“传入List”变成了“在数据库里查一张表”存储过程的入参就不再有集合类型MyBatis侧也就不用处理TypeHandler。Oracle临时表对并发有隔离不同会话看不到彼此数据这一点比普通表安全得多。4.2 MyBatis批量插入临时表分批次比一条大SQL更稳数据量小的时候在XML里写一个foreach拼成一条INSERT即可。但数据量上万时一条SQL里的绑定变量个数会逼近Oracle的IN列表上限1045实际上绑定变量上限是65535而且解析长SQL非常耗时。我更倾向于分批次批量插入先在Mapper里定义插入方法public interface TempMapper { int insertTempList(Param(list) ListString list); }XMLinsert idinsertTempList parameterTypemap INSERT INTO TEMP_EMP_LIST(EMP_NO) VALUES foreach collectionlist itemitem separator, (#{item}) /foreach /insert然后Service里分批调用public void batchUpdateWithTemp(ListString empNos) { int batchSize 500; for (int i 0; i empNos.size(); i batchSize) { int end Math.min(i batchSize, empNos.size()); tempMapper.insertTempList(empNos.subList(i, end)); } // 这里通过调用存储过程的Mapper执行 MapString, Object result batchMapper.callBatchUpdate(); // 完成后临时表随事务提交自动清空 }逻辑说明每一批插入500条避免一次拼接过多绑定变量。subList返回的是视图但MyBatis处理时只是遍历取值不会有并发问题。存储过程的Mapper接口不需要任何入参存储过程自己查临时表因此XML里也是一个CALLABLE调用只是参数只有OUT。参数说明批次大小500是我在实际项目里验证过的量级全字段VARCHAR(64)时一次SQL的绑定变量数量在500左右Oracle解析时间稳定在几百毫秒内。如果你每条数据字段更多批次要降到200甚至100。关键衡量标准是单次SQL不要超过Oracle绑定变量的安全线而且尽量控制在网络报文可以接受的范围内。4.3 事务控制与临时表清理别把数据带到下一笔临时表方案最大的坑是“残留数据”。我用事务级临时表写完后调用存储过程存储过程内部COMMIT会把临时表数据清除因为ON COMMIT DELETE ROWS。但要注意如果存储过程没有COMMIT事务本身也未提交那么临时表数据会一直在当前事务里如果事务回滚数据也一样没了。这在逻辑上是一致的但容易让不熟悉的人怀疑“明明插入了存储过程查不到”。在Service外层可以加注解控制Transactional(rollbackFor Exception.class) public void batchUpdateWithTemp(ListString empNos) { // 插入临时表 tempMapper.insertTempList(empNos.subList(0, 200)); // 调用存储过程 batchMapper.callBatchUpdate(); }逻辑说明Transactional确保插入和调用存储过程在同一个事务里这样临时表的数据在存储过程执行时一定可见。如果存储过程内部有COMMIT外层事务的隔离级别需要特别注意COMMIT会被Oracle视为当前事务结束。如果希望整体要么全成要么全败存储过程就不要写COMMIT而是靠外层事务提交。注意临时表的数据并不会在事务外被读取如果遇到“数据查不到”先确认是不是不同会话或已经提交了事务。这类问题排查比TypeHandler方案更依赖数据库层面的经验。4.4 为什么大数据量推荐临时表而不是TypeHandlerTypeHandler看起来更优雅但有一个隐藏问题Oracle的VARRAY和TABLE类型有元素类型和长度限制更重要的是当你把它当存储过程入参时Oracle要先把整个集合打包传输执行计划里会占用大量的PGA内存。数据量达到十万级时集合的构造和序列化会有明显延迟。而临时表方案是标准的关系型数据库操作插入和查询都走SQL执行计划可以建索引也可以分页维护性更高。另外在团队协作场景里临时表方案可以直接用SQL客户端调试存储过程不需要Java代码而TypeHandler方案必须跟着Java进程一起跑才能验证出了问题很难分辨是MyBatis配置问题还是Oracle集合类型问题。所以记住一个口诀小批量用TypeHandler大批量用临时表。5. 避坑传list调Oracle存储过程的常见翻车点5.1 现象ORA-06550/PLS-00382: expression is of wrong type原因MyBatis把Java List传递给存储过程时Oracle驱动不识别类型或者调用时没有匹配的TypeHandler。我在初学时最容易犯的错是以为存储过程里写了TABLE OF VARCHAR2MyBatis就会自动转换。解决把XML里的参数显式绑上typeHandler并确保Oracle侧类型名一致。或者放弃集合入参改用临时表。5.2 现象ORA-22806: not an object or REF原因这个错经常出现在createARRAY的类型名写错的时候比如Oracle里定义的是P_VARCHAR2_TABLE但Java里写成了小写p_varchar2_table。Oracle的类型名默认是大小写敏感的如果用双引号创建了大写那么查询时也要大写。解决先用SQL客户端执行SELECT * FROM ALL_TYPES WHERE TYPE_NAME P_VARCHAR2_TABLE确认类型名再检查createARRAY的第二个参数new String[0]在往Oracle驱动传时要注意数组元素的类型如果是Long列表就得换一个对应的Oracle类型比如TABLE OF NUMBER不能通用。5.3 现象Parameter index out of range或invalid argument(s)原因MyBatis XML里存储过程的参数顺序和数据库中存储过程的定义顺序不一致。特别是在混合IN和OUT参数时很容易写错。比如我写成了{call PRO_BATCH_UPDATE(#{count, modeOUT}, #{list, modeIN})}数据库会认为第一个入参是list第二个是count但类型对不上。解决严格按存储过程定义的参数顺序书写。如果不想依赖顺序可以在存储过程定义里全部用默认名称而且Oracle的存储过程不支持按名传参在JDBC的CallableStatement里所以顺序绝对不能乱。5.4 现象批量插入临时表时报ORA-01000: maximum open cursors exceeded原因MyBatis每次insertTempList都要打开一个游标如果循环分批时不复用会话游标会累积。另外在ExecutorType.BATCH模式下如果没有每批flushStatements也会堆积游标。解决分批次插入时每批500条执行完就调用sqlSession.flushStatements()。用Spring时可以让Mapper方法批量执行或者直接调SqlSessionTemplate的批量模式。5.5 现象临时表数据在存储过程中查不到但Java侧明明insert成功了原因临时表使用了ON COMMIT PRESERVE ROWS会话级而Java应用和存储过程分别在两个连接里其实不是——在Spring事务里通常是同一个连接问题更多是出在存储过程内部开启了新事务或者临时表被别的会话截断。另一个常见原因是Oracle的全局临时表在多个节点RAC环境下数据共享一致性问题虽然概率低但要注意使用ON COMMIT DELETE ROWS并及时提交。解决确保Java调用存储过程前当前线程已经绑定事务避免在存储过程内单独COMMIT如果仍查不到可以先手动在SQLPlus里模拟两条SQL的调用顺序验证临时表的生命周期。6. 验证与进阶用游标返回影响行数和纯JDBC兜底6.1 用OUT参数返回实际处理行数不管用哪种方案存储过程最好都能返回一个处理统计。在上面的TypeHandler方案里我们已经有P_COUNT的OUT参数调用后从Map里取MapString, Object result batchMapper.callBatchUpdate(list); Integer count (Integer) result.get(count);这个count在Mapper接口Map返回值中直接可用。这是最轻的验证方式我在写完存储过程后一定会先在Mapper测试里打印这个值来判断参数是否真的传了进去。6.2 用SYS_REFCURSOR返回结果集存储过程也可以用一个OUT SYS_REFCURSOR返回游标比如批量更新后想返回所有失败记录。此时MyBatis的XML可以这么写select idcallBatchUpdateWithCursor statementTypeCALLABLE resultTypemap {call PRO_BATCH_UPDATE_CURSOR( #{list, modeIN, jdbcTypeARRAY, typeHandlercom.example.handler.OracleStringListTypeHandler}, #{cursor, modeOUT, jdbcTypeREF_CURSOR} )} /select这里resultTypemap会把游标里的每一行转成Map。如果用这个方式存储过程内部的动态返回记录要用OPEN p_cursor FOR SELECT ...。这比返回行数更能验证业务实际更新情况比如可以直接看到哪些编号没匹配上。6.3 纯JDBC兜底当MyBatis版本或驱动不兼容时有些老项目用MyBatis 3.2unwrap方法和ARRAY转换都不好使这时候可以直接绕过MyBatis的TypeHandler在Service里用一个JDBC模板手动调用存储过程public int callWithJdbc(ListString list) { Connection conn DataSourceUtils.getConnection(dataSource); oracle.jdbc.OracleConnection oracleConn conn.unwrap(oracle.jdbc.OracleConnection.class); ARRAY array oracleConn.createARRAY(P_VARCHAR2_TABLE, list.toArray(new String[0])); CallableStatement cs conn.prepareCall({call PRO_BATCH_UPDATE(?, ?)}); cs.setArray(1, array); cs.registerOutParameter(2, Types.NUMERIC); cs.execute(); int count cs.getInt(2); cs.close(); return count; }这个兜底方案不需要动Mapper XML甚至不需要TypeHandler只需要一个数据源。缺点是手写资源释放务必在finally里关闭CallableStatement。用DataSourceUtils.getConnection可以绑定到Spring事务比裸DriverManager.getConnection安全得多。我一般做一个方案前会先用SQLDeveloper把存储过程调通再写Java代码不然出了问题都不知道是SQL层还是MyBatis层。这个习惯帮我少走很多弯路。希望帮到你。本文还有配套的精品资源点击获取