Dapper操作Oracle批量插入:RefCursor设为Input参数失效如何传对象列表
待解决问题
我在C#项目中持有一组待插入Oracle表的对象列表,当前基于Dapper实现的逻辑为:通过foreach遍历列表,逐行将单条数据传入Oracle包(pck)中的指定函数完成插入。但当数据量较大(如插入35000条记录时),整体运行耗时极长。
我希望能将整个对象列表直接传入数据库端,由数据库侧一次性完成所有插入操作。此前我尝试使用OracleMappingType.RefCursor配置ParameterDirection.Input方向传参,但RefCursor仅支持输出方向,无法作为入参正常生效。想请教是否有可行方案,能够将整个对象列表传入数据库并完成批量插入?
当前低效实现代码
string query = "PCK_TEST.FC_IMPORT_VALUES"; foreach (var item in ListOfValues) { var param = new OracleDynamicParameters(); param.Add("P_VALUE_A", dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input, value: item.ValueA); param.Add("P_VALUE_B", dbType: OracleMappingType.Date, direction: ParameterDirection.Input, value: item.ValueB); param.Add("P_VALUE_C", dbType: OracleMappingType.Decimal, direction: ParameterDirection.Input, value: item.ValueC); param.Add("P_VALUE_D", dbType: OracleMappingType.Decimal, direction: ParameterDirection.Input, value: item.ValueD); param.Add("P_VALUE_E", dbType: OracleMappingType.Decimal, direction: ParameterDirection.Input, value: item.ValueE); param.Add("P_VALUE_F", dbType: OracleMappingType.Decimal, direction: ParameterDirection.Input, value: item.ValueF); param.Add("P_VALUE_G", dbType: OracleMappingType.Decimal, direction: ParameterDirection.Input, value: item.ValueG); param.Add("P_VALUE_H", dbType: OracleMappingType.Decimal, direction: ParameterDirection.Input, value: item.ValueH); param.Add("P_VALUE_I", dbType: OracleMappingType.Decimal, direction: ParameterDirection.Input, value: item.ValueI); param.Add("P_VALUE_J", dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input, value: item.ValueJ); param.Add("P_VALUE_K", dbType: OracleMappingType.Decimal, direction: ParameterDirection.Input, value: item.ValueK); param.Add("P_VALUE_L", dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input, value: item.ValueL); param.Add("P_RETORN", dbType: OracleMappingType.Varchar2, size: 4000, direction: ParameterDirection.ReturnValue); await SqlMapper.ExecuteAsync(_con, query, param: param, commandType: CommandType.StoredProcedure); var erro = param.Get<string>("P_RETORN"); }
可行实现方案
核心原理:不要使用RefCursor作为入参,RefCursor是Oracle专为服务端游标结果集设计的输出类型,本身不支持入参方向,属于Oracle底层设计限制。要实现集合批量传参,需要使用Oracle原生的嵌套表/自定义对象数组类型作为入参,配合ODP.NET的数组绑定能力,一次性把整个列表传到数据库端执行批量插入,完全兼容原有包内的业务逻辑。
步骤1:数据库侧定义匹配的类型与批量处理逻辑
首先在Oracle库中创建与C#实体字段一一对应的对象类型、嵌套表集合类型,改造原有包函数为接收集合入参,实现一次性批量处理:
-- 1. 定义单条记录对应的对象类型,字段与C#实体的ValueA~ValueL完全匹配 CREATE OR REPLACE TYPE OBJ_IMPORT_VALUE AS OBJECT ( VALUE_A VARCHAR2(100), VALUE_B DATE, VALUE_C NUMBER, VALUE_D NUMBER, VALUE_E NUMBER, VALUE_F NUMBER, VALUE_G NUMBER, VALUE_H NUMBER, VALUE_I NUMBER, VALUE_J VARCHAR2(500), VALUE_K NUMBER, VALUE_L VARCHAR2(200) ); / -- 2. 定义该对象类型对应的嵌套表类型,用于存储批量传入的多行数据 CREATE OR REPLACE TYPE TBL_IMPORT_VALUE IS TABLE OF OBJ_IMPORT_VALUE; / -- 3. 改造原有包的函数,将原单字段入参替换为嵌套表集合入参 CREATE OR REPLACE PACKAGE PCK_TEST AS FUNCTION FC_IMPORT_VALUES( P_IMPORT_DATA TBL_IMPORT_VALUE ) RETURN VARCHAR2; END PCK_TEST; / CREATE OR REPLACE PACKAGE BODY PCK_TEST AS FUNCTION FC_IMPORT_VALUES( P_IMPORT_DATA TBL_IMPORT_VALUE ) RETURN VARCHAR2 IS BEGIN -- 替换为实际的目标表名,将传入的集合通过TABLE函数转成行集,一次性完成批量插入 INSERT INTO YOUR_TARGET_TABLE ( COL_A, COL_B, COL_C, COL_D, COL_E, COL_F, COL_G, COL_H, COL_I, COL_J, COL_K, COL_L ) SELECT VALUE_A, VALUE_B, VALUE_C, VALUE_D, VALUE_E, VALUE_F, VALUE_G, VALUE_H, VALUE_I, VALUE_J, VALUE_K, VALUE_L FROM TABLE(P_IMPORT_DATA); COMMIT; -- 可根据原有业务逻辑调整提交时机 RETURN 'SUCCESS'; EXCEPTION WHEN OTHERS THEN ROLLBACK; RETURN 'ERROR: ' || SQLERRM; -- 保留原有错误返回逻辑 END FC_IMPORT_VALUES; END PCK_TEST; /
步骤2:C#端基于Dapper实现集合传参
替换原有逐行循环的逻辑,直接将整个列表绑定为Oracle自定义数组类型入参,一次性完成调用:
// 拆分实体列表为字段级别的数组,长度与列表总条数一致 var valueA = ListOfValues.Select(x => x.ValueA).ToArray(); var valueB = ListOfValues.Select(x => x.ValueB).ToArray(); var valueC = ListOfValues.Select(x => x.ValueC).ToArray(); var valueD = ListOfValues.Select(x => x.ValueD).ToArray(); var valueE = ListOfValues.Select(x => x.ValueE).ToArray(); var valueF = ListOfValues.Select(x => x.ValueF).ToArray(); var valueG = ListOfValues.Select(x => x.ValueG).ToArray(); var valueH = ListOfValues.Select(x => x.ValueH).ToArray(); var valueI = ListOfValues.Select(x => x.ValueI).ToArray(); var valueJ = ListOfValues.Select(x => x.ValueJ).ToArray(); var valueK = ListOfValues.Select(x => x.ValueK).ToArray(); var valueL = ListOfValues.Select(x => x.ValueL).ToArray(); var param = new OracleDynamicParameters(); // 绑定集合入参,指定数据库端自定义类型的完整路径 param.Add( name: "P_IMPORT_DATA", dbType: OracleMappingType.Array, direction: ParameterDirection.Input, value: new object[] { valueA, valueB, valueC, valueD, valueE, valueF, valueG, valueH, valueI, valueJ, valueK, valueL }, typeName: "YOUR_SCHEMA.TBL_IMPORT_VALUE" // 替换为实际的schema与自定义嵌套表类型名称 ); // 保留原有函数返回值参数 param.Add("P_RETORN", dbType: OracleMappingType.Varchar2, size: 4000, direction: ParameterDirection.ReturnValue); // 一次性调用包函数,无需循环 await SqlMapper.ExecuteAsync( _con, "PCK_TEST.FC_IMPORT_VALUES", param: param, commandType: CommandType.StoredProcedure); var result = param.Get<string>("P_RETORN");
关键注意事项
- 若包函数中存在逐行的数据校验、特殊处理逻辑,可在批量插入前通过
FORALL循环遍历传入的集合逐行执行逻辑,相比原逐行网络往返的实现,耗时仍会有数量级的下降。 - 性能对比:原逐行调用35000条数据时,单次网络往返的累积耗时通常在数分钟级别;改为集合传参后,仅需1次网络往返,数据库端批量执行,35000条记录的总耗时通常可降至数秒级别。
- 若不需要兼容包内的特殊业务逻辑,也可直接使用ODP.NET的
OracleBulkCopy实现裸表批量插入,但该方式无法触达原有包函数的校验、处理逻辑,适合无特殊业务规则的纯数据导入场景。
内容的提问来源于stack exchange,提问作者Guibson Oliveira Krause
相关产品推荐
相关产品推荐

