oracle通过存储过程上传list保存功能_oracle_

 create TYPE "AL01TYPE" as object ( -- 描述 : 档案批量转出 -- 作者 : dt -- 时间 : 2021-05-10 -- 版本 ：dev-1.0.1 aac003 NVARCHAR2(100), aac002 NVARCHAR2(50), aat001 NVARCHAR2(50), aat002 NVARCHAR2(50), aat013 NVARCHAR2(20), aae011 NVARCHAR2(20), aae036 NVARCHAR2(20), aah002 NVARCHAR2(100) ); create type AL01TYPELIST as table of AL01TYPE; -- auto-generated definition create PROCEDURE SP_HFSZHDA_DOUPLOADAL01(LIST IN AL01TYPELIST, po_message OUT VARCHAR) IS --描述：档案转出excel上传 --作者：dt --时间：2021-05-10 --版本：dev-1.0.1 v_object AL01TYPE; le_error EXCEPTION; P_renum number(20); v_aah002 VARCHAR(100); ls_count number; ls_aaf025 VARCHAR(50); BEGIN P_renum := 0; --初始化 FOR I IN 1 .. LIST.count LOOP P_renum := 1 + P_renum; v_object := LIST(I); select replace(utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(v_object.aah002)),unistr('\0000')) into v_aah002 from dual; begin Select count(0) into ls_count from az03 where aat001 = v_object.aat001 AND aat012 = '1'; if ls_count=0 then po_message := '号：'||v_object.aat001||' 状态异常请核对后再上传！'; RAISE le_error; end if; Select count(0) into ls_count From AL01 where aaj022='1' and aat001=v_object.aat001 and aah002=v_aah002; if ls_count =0 then -- 开始插入信息 select SQ_AAF025.nextval into ls_aaf025 from dual; insert into AL01( aaf025, aat012, aat001, aac003, aac002, aat002, aat013, aaj022, aaj026, aae011, aae036, aah002)values( ls_aaf025, '1', v_object.aat001, v_object.aac003 , v_object.aac002 , v_object.aat002 , v_object.aat013, '1', 'excel上传数据', v_object.aae011, v_object.aae036, v_aah002 ); end if; IF P_renum >1000 THEN commit; P_renum:=0; END IF; po_message :='ok'; EXCEPTION WHEN le_error THEN NULL; WHEN OTHERS THEN ROLLBACK; po_message := '上传失败' || SQLCODE || SQLERRM; end; END LOOP; COMMIT; END SP_HFSZHDA_DOUPLOADAL01;

 package com.cominfo.elecfile.utils; import oracle.jdbc.OracleConnection; import oracle.sql.ARRAY; import oracle.sql.ArrayDescriptor; import oracle.sql.STRUCT; import oracle.sql.StructDescriptor; import org.springframework.jdbc.support.nativejdbc.C3P0NativeJdbcExtractor; import java.sql.Connection; import java.util.List; /** * 描述 * * @Auther: dt * @Date: 2021/5/10 0027 09:00 */ public class OracleUtil { /** * 根据数据库中你的type将List组装成Array * @param con * @param OracleObj * @param Oraclelist * @param objlist * @return * @throws Exception */ public static ARRAY getArray(Connection con, String OracleObj, String Oraclelist, List objlist) throws Exception { ARRAY array=null; C3P0NativeJdbcExtractor cp30NativeJdbcExtractor = new C3P0NativeJdbcExtractor(); OracleConnection connection = (OracleConnection) cp30NativeJdbcExtractor.getNativeConnection(con); if (objlist != null && objlist.size() > 0) { StructDescriptor structdesc = new StructDescriptor(OracleObj, connection); STRUCT[] structs = new STRUCT[objlist.size()]; for (int i = 0; i < objlist.size(); i++) { Object[] result= (Object[]) objlist.get(i); structs[i] = new STRUCT(structdesc, connection, result); } ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist,connection); array = new ARRAY(desc, connection, structs); } return array; } }

 List arrList = new ArrayList<>(); //解析数据datamap for (Map dataMap : dataMaps) { //创建保存对象 Object[] objects =new Object[]{ dataMap.get("aac003"), dataMap.get("aac002"), dataMap.get("aat001").trim(), dataMap.get("aat002"), dataMap.get("aat013"), 'admin', DateUtil.getCurrentTimeStr(), 'ec-20210510-wcdedgk2091', }; arrList.add(objects); } //开始调用过程 long startTime=System.currentTimeMillis(); Connection connection = null; CallableStatement sqlres = null; String sql = "call SP_HFSZHDA_DOUPLOADAL01(?,?)"; String msg = ""; try { connection = dataSource.getConnection(); ARRAY paramArr = OracleUtil.getArray(connection,"AL01TYPE","AL01TYPELIST",arrList); sqlres = connection.prepareCall(sql); sqlres.setArray(1, paramArr); sqlres.registerOutParameter(2, Types.VARCHAR); sqlres.execute(); msg = sqlres.getString(2); long endTime=System.currentTimeMillis()-startTime; System.out.println("上传后获取的返回参数为："+msg+"||耗时："+endTime/1000+"秒"); } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); }finally { try { if (sqlres != null) { sqlres.close(); } if (connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } if (!"ok".equals(msg)){ throw new BusinessException("上传失败！"+msg); } }