oracle 中怎么大批量的执行UPDATE语句

发布网友发布时间：2022-04-07 21:29

共3个回答

懂视网时间：2022-04-08 01:50

批量添加20000条数据用时8秒。 try { String url = "jdbc:oracle:thin:@IP:1521:orcl"; // orcl为数据库的SID String user = "oracle"; String password = "oracle"; StringBuffer sql = new StringBuffer(); sql.append("insert into ex_log (EX_LOG_ID,EX_LOG_DATE) values (?,?)"); Class.forName("oracle.jdbc.driver.OracleDriver"); Connection con = (Connection) DriverManager.getConnection(url,user,password); // 关闭事务自动提交 con.setAutoCommit(false); Long startTime = System.currentTimeMillis(); PreparedStatement pst = (PreparedStatement) con.prepareStatement(sql.toString()); for (int i = 0; i < list.size(); i++) { ExLog exLog = (ExLog)list.get(i); pst.setString(1, exLog.getExLogId()); pst.setString(2, exLog.getExLogDate()); // 把一个SQL命令加入命令列表 pst.addBatch(); } // 执行批量更新 pst.executeBatch(); // 语句执行完毕，提交本事务 con.commit(); Long endTime = System.currentTimeMillis(); System.out.println("用时：" + (endTime - startTime)); pst.close(); con.close(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }

------------------------------------------------------------------------------------- 下面是我的实例

 Connection conn = null;
 PreparedStatement stmt = null;

 StringBuffer sql = new StringBuffer();
 sql.append("merge into DEEPDATA.WARNING_MES t1 ");
 sql.append("using (select ? as CUSTOMER_ID,? as added,? as integrated_risk,? as inner_risk,? as industry_risk,? as region_risk from dual) t2 ");
 sql.append("on (t1.CUSTOMER_ID=t2.CUSTOMER_ID) ");
 sql.append("when matched then ");
 sql.append("update set ");
 sql.append("t1.added = t2.added,t1.integrated_risk = t2.integrated_risk,t1.INNER_RISK = t2.INNER_RISK,t1.industry_risk = t2.industry_risk,t1.region_risk = t2.region_risk ");
 sql.append("when not matched then ");
 sql.append("insert (t1.CUSTOMER_ID,t1.added,t1.integrated_risk,t1.INNER_RISK,t1.industry_risk ,t1.region_risk) ");
 sql.append("values(t2.CUSTOMER_ID,t2.added,t2.integrated_risk,t2.INNER_RISK,t2.industry_risk ,t2.region_risk)");

 try {
  conn = DriverManager.getConnection(url, dbUser, dbPassword);
  // 关闭自动提交;
  conn.setAutoCommit(false);
  stmt = conn.prepareStatement(sql.toString());

  Timestamp timestamp = Timestamp.valueOf(GetDate
   .getTodayDate_FullFormat());

  if (result != null && result.size() > 0) {
  double tmpValue;
  for (Map<String, Object> map : result) {
   tmpValue = Double.parseDouble(map.get(
    ConstValue.IntegratedRisk).toString());
   if (specialHandle.contains(map.get("id").toString())) {
   tmpValue = tmpValue * 1.72;
   }

   stmt.setInt(1, Integer.parseInt(map.get("id").toString()));
   stmt.setTimestamp(2, timestamp);
   stmt.setDouble(3, tmpValue);
   stmt.setDouble(4, Double.parseDouble(map.get(
    ConstValue.InnerRisk).toString()));
   stmt.setDouble(5, Double.parseDouble(map.get(
    ConstValue.IndustryRisk).toString()));
   stmt.setDouble(6, Double.parseDouble(map.get(
    ConstValue.RegionRisk).toString()));
   stmt.addBatch();
  }
  stmt.executeBatch();
  conn.commit();
  }

  stmt.close();
  conn.close();
 } catch (SQLException e) {
  e.printStackTrace();
 }

[转载]Oracle批量执行

标签：

热心网友时间：2022-04-07 22:58

你可以写一个PLSQL命令块通过执行UPDATE命令
begin
for i in 1 .. 100000
loop
update a set a.1=x where a.2=i
if mod(i,5000)=0
then
commit;
end if;
end loop;
end;
/

热心网友时间：2022-04-08 00:16

如果系统的数据结果设计的合理，update操作都有一定规律性的。批处理通常采用两种方法：
1、如果要修改的所有行的列、修改数据的来源、计算算法都一样、则一个Update语句就可以解决。
2、如果要修改的所有行的列、修改数据的来源、计算方法虽然不同，但有一定的规律，则采用游标来进行批量修改。
如果要修改的行、列、数据杂乱无章没有一定的规律，则只能一行一行修改了。