Oracle的批量插入操作

bisal

发布于 2020-11-12 11:27:11

2.5K0

发布于 2020-11-12 11:27:11

MySQL中支持一条SQL语句执行批量插入，Oracle中支持的形式有些不同，但是殊途同归，用的就是insert all into语法。

创建测试表，

SQL> create table a1(id number, a varchar2(1), b varchar2(1), c varchar2(1), d varchar2(1));
Table created.

可以用如下语句，执行批量插入，

SQL> insert all 
  2    into a1(id, a, b, c, d) values (1, 'a', 'a', 'a', 'a')   
  3    into a1(id, a, b, c, d) values (2, 'b', 'b', 'b', 'b')
  4  select 1 from dual;
2 rows created.

按照Oracle的解释，insert all into其实是根据子查询执行了每个insert into子句，注意到上面SQL中每个into子句用的值都是字面量，子查询"select 1 from dual"返回1条记录，支持每个insert into子句插入指定的1条记录，

“ALL into_clause: Specify ALL followed by multiple insert_into_clauses to perform an unconditional multitable insert. Oracle Database executes each insert_into_clause once for each row returned by the subquery.”

因此，如果在初始状态，子查询改成"select ... from a1"，由于a1当前是空，返回记录数是0，所以这条SQL，插入就是0条，

SQL> insert all 
  2    into a1(id, a, b, c, d) values (1, 'a', 'a', 'a', 'a') 
  3    into a1(id, a, b, c, d) values (2, 'b', 'b', 'b', 'b') 
  4  select id, a, b, c, d from a1;
0 rows created.

如果当前表中存在两条记录，

SQL> select * from a1;
        ID A B C D
---------- - - - -
         1 a a a a
         2 b b b b

再次执行insert all into，则会插入4条记录，因为子查询，返回2条记录，

SQL> insert all 
  2    into a1(id, a, b, c, d) values (1, 'a', 'a', 'a', 'a') 
  3    into a1(id, a, b, c, d) values (2, 'b', 'b', 'b', 'b') 
  4  select id, a, b, c, d from a1;
4 rows created.

因此，最简单的形式，就是子查询用select 1 from dual。

但insert all into中子查询不支持使用序列，如下操作，提示错误，

SQL> insert all
  2    into a1(id, a, b, c, d) values (seq_a1.nextval,  'a', 'a', 'a', 'a')
  3    into a1(id, a, b, c, d) values (seq_a1.nextval,  'b', 'b', 'b', 'b')
  4  select seq_a1.nextval, a, b, c, d from a1;
select seq_a1.nextval, a, b, c, d from a1
              *
ERROR at line 4:
ORA-02287: sequence number not allowed here

改为这种，能正常执行，

SQL> insert all 
  2    into a1(id, a, b, c, d) values (seq_a1.nextval,  'a', 'a', 'a', 'a') 
  3    into a1(id, a, b, c, d) values (seq_a1.nextval,  'b', 'b', 'b', 'b') 
  4  select 1 from dual;
2 rows created.

但实际上，id得到的sequence值，是相同的，

SQL> select * from a1;
        ID A B C D
---------- - - - -
         1 a a a a
         1 b b b b

一种解决方式，是采用触发器，BEFORE INSERT在插入之前找到正确的序列，另外一种方式，就是创建函数，读取序列，他可以骗过Oracle，

SQL> create or replace function f_getseq return number as 
  2  v_seq number;
  3  begin
  4  select seq_a1.nextval into v_seq from dual;
  5  return v_seq;
  6  end;
  7  /
Function created.

此时能正常插入，而且id值，都正确了，

SQL> insert all 
  2    into a1(id, a, b, c, d) values (f_getseq,  'a', 'a', 'a', 'a') 
  3    into a1(id, a, b, c, d) values (f_getseq,  'b', 'b', 'b', 'b') 
  4  select 1 from dual;
2 rows created.


SQL> select * from a1;


        ID A B C D
---------- - - - -
         1 a a a a
         2 b b b b

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2020/11/03 ，如有侵权请联系 cloudcommunity@tencent.com 删除

oracle