oracle如何处理批插入？

aurhwmvo 于 2023-03-17 发布在 Oracle

关注(0)|答案(1)|浏览(129)

现在我正在使用JDBC的executeBatch函数向一个表中插入多行。Java代码如下所示：

String sql = "insert into t values (?, ?, ?)";
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < 100; i++) {
preparedStatement.setInt(1, i);
preparedStatement.setString(2, "jack"+i);
preparedStatement.setString(3, "tom"+i);
preparedStatement.addBatch();
if ((i+1)%10==0) {
preparedStatement.executeBatch();
}
}

比executeUpdate一行一行快，我知道在MySql中sql可以重写为“insertinto t values（xx，xx，xx），（xx，xx，xx）......"，上面的代码在Mysql中只需要执行10次，但是在Oracle中没有这样的sql语法，是什么让Oracle这么快？而且我还发现dba_audit_trail中只有10个sql绑定，tom 0 ~ tom 8在哪里？

#1(1):9 #2(5):jack9 #3(5):tom9
  #1(2):19 #2(6):jack19 #3(6):tom19
  #1(2):29 #2(6):jack29 #3(6):tom29
  #1(2):39 #2(6):jack39 #3(6):tom39
  #1(2):49 #2(6):jack49 #3(6):tom49
  #1(2):59 #2(6):jack59 #3(6):tom59
  #1(2):69 #2(6):jack69 #3(6):tom69
  #1(2):79 #2(6):jack79 #3(6):tom79
  #1(2):89 #2(6):jack89 #3(6):tom89
  #1(2):99 #2(6):jack99 #3(6):tom99

oracle

来源：https://stackoverflow.com/questions/75681109/how-do-oracle-deal-with-batch-insert

1条答案

按热度按时间

hts6caw31#

在Oracle中，它使用FORALL。

create or replace procedure nobatch is
begin
  for x in (select * from t)
  loop
    insert into t1 (id, name1, name2, name3, name4)
    values (x.id, x.name1, x.name2, x.name3, x.name4);
  end loop;
  commit;
end nobatch;
/

create or replace procedure usebatch (p_array_size in pls_integer)
is
  type array is table of t%rowtype;
  l_data array;
  cursor c is select * from t;
begin
  open c;
  loop
    fetch c bulk collect into l_data limit p_array_size;
    forall i in 1..l_data.count insert into t1 values l_data(i);
    exit when c%notfound;
  end loop;
  commit;
  close c;
end usebatch;
/

SQL> exec nobatch;  
Elapsed: 00:00:32.92

SQL> exec usebatch(50);
Elapsed: 00:00:00.77

SQL> exec usebatch(100);
Elapsed: 00:00:00.47

SQL> exec usebatch(1000);
Elapsed: 00:00:00.19

SQL> exec usebatch(100000);
Elapsed: 00:00:00.26

赞(0）回复(0）举报 2023-03-17

我来回答

oracle如何处理批插入？

1条答案

相关问题

热门标签

最新问答