Oracle的批量插入操作

我就是我 2022-12-19 01:23 305阅读 0赞

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

**近期的热文：**

《[数据库结构文档的生成利器][Link 1]》

《[主键约束索引的奇葩现象][Link 2]》

《[如何判断应用系统性能好不好？][Link 3]》

《[Oracle Cloud创建19c数据库][Oracle Cloud_19c]》

《[SQL工具集-格式化结果的SQL][SQL_-_SQL]》[  
][Link 4]

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

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

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

《[登录缓慢的诡异问题][Link 5]》

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

[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815a43ddf6d355d2016952b8619d38fe02d29425399ae59ce4955f53d7550fd0b9e09dcaf3&idx=1&mid=2247491504&scene=21&sn=9eaedbca194eebea3bc2ac6ec27f163f#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815a52ddf6d34410fc367c116948a693d98005f6f43ffd9d0468a0af83e4e60c6af3d3045e&idx=1&mid=2247491489&scene=21&sn=f2cc824ff4a7148dc8289d03eb8e7418#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815ad4ddf6d3c28b6bc547fe08a3de87e06d8c3292a6cc9bdffc7fc5f7d432263637d86231&idx=1&mid=2247491367&scene=21&sn=89f5263257059b2c7f61be001bc27b94#wechat_redirect
[Oracle Cloud_19c]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815ad1ddf6d3c71da6d160381513bc2cb57b4d1b801bfde274c999abae90fadaa4fb27c14c&idx=1&mid=2247491362&scene=21&sn=0aeaf1f06a566c5e34ce258cfe5ef8f2#wechat_redirect
[SQL_-_SQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815b02ddf6d214ff8098c44d2f1aabacb383ae1fb4e25a94db4ac0bfa4fa8c8e65610cec94&idx=1&mid=2247491313&scene=21&sn=8ee15fea049d85c2534c2bbfad4eaab8#wechat_redirect
[Link 4]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815b24ddf6d232f8395bbf2ee5dcec7f71c5c63ede0630d34a21f363ba02f77559801d2b3a&idx=1&mid=2247491287&scene=21&sn=7327002ce4b56f72a84a09bd04bdafd6#wechat_redirect
[SQL_CPU]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815b6addf6d27c0f85b9b84c237e4ca93f497fe293c31877990cef9259c6ab8450745b31be&idx=1&mid=2247491225&scene=21&sn=ac866f3d0bbba602e31aa53ca880b74f#wechat_redirect
[JVM]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815b74ddf6d2621f3b547a49d4b6c8025612fb1ab0892928365b881ad2f76f01206ed68e45&idx=1&mid=2247491207&scene=21&sn=11ad7551928df895633f9b73f103eb0e#wechat_redirect
[Oracle]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815939ddf6d02f97942e6dd5b5c60e60cd732d31c893f8879244f66d7d914b9f23fe7fc207&idx=1&mid=2247490762&scene=21&sn=67f5fdf567297ab86db77a57162f7a6a#wechat_redirect
[Link 5]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815955ddf6d0434a80b00ba0e31ba96198814d630a9a255ec7b427a210ab01ad5bba685038&idx=1&mid=2247490726&scene=21&sn=f8836d3e25f6496fab88258526f048a9#wechat_redirect
[600]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815fa3ddf6d6b521f9a1fccaeaf2b2ed2b547c3102b18c51397ef670985f477ee102d76fe8&idx=1&mid=2247490128&scene=21&sn=3e9b7f11a1bf04b4f302830fcdbed57d#wechat_redirect