您好,登錄后才能下訂單哦!
這篇文章將為大家詳細(xì)講解有關(guān)oracle如何通過存儲(chǔ)過程上傳list保存功能,小編覺得挺實(shí)用的,因此分享給大家做個(gè)參考,希望大家閱讀完這篇文章后可以有所收獲。
create TYPE "AL01TYPE" as object ( -- 描述 : 檔案批量轉(zhuǎn)出 -- 作者 : dt -- 時(shí)間 : 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 --描述:檔案轉(zhuǎn)出excel上傳 --作者:dt --時(shí)間: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 := '號(hào):'||v_object.aat001||' 狀態(tài)異常請(qǐng)核對(duì)后再上傳!'; 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上傳數(shù)據(jù)', 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 { /** * 根據(jù)數(shù)據(jù)庫中你的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<Object[]> 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<Object[]> arrList = new ArrayList<>(); //解析數(shù)據(jù)datamap for (Map<String, String> dataMap : dataMaps) { //創(chuàng)建保存對(duì)象 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); } //開始調(diào)用過程 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("上傳后獲取的返回參數(shù)為:"+msg+"||耗時(shí):"+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); } }
關(guān)于“oracle如何通過存儲(chǔ)過程上傳list保存功能”這篇文章就分享到這里了,希望以上內(nèi)容可以對(duì)大家有一定的幫助,使各位可以學(xué)到更多知識(shí),如果覺得文章不錯(cuò),請(qǐng)把它分享出去讓更多的人看到。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。