oracle insert into select 大数据量_Oracle的批量插入操作

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

近期的热文：

《数据库结构文档的生成利器》

《主键约束索引的奇葩现象》

《如何判断应用系统性能好不好？》

《Oracle Cloud创建19c数据库》

《SQL工具集-格式化结果的SQL》

《如何捕获问题SQL解决过度CPU消耗的问题》

《如何查看JVM运行的堆内存情况》

《Oracle删除字段的方式和风险，你都了解么？》

《登录缓慢的诡异问题》

《公众号600篇文章分类和索引》