Oracle insert all 讲解

柔光的暖阳◎ 2022-02-03 06:23 229阅读 0赞

# 1、概念 #

Oracle 中`insert all` 是指把 `同一批` 数据插入到 `不同的表` 中。

假如，现在有个需求，把表 `t` 的中数据分别插入到 `t1、t2`，如果你不知道 `insert all`， 你可能会使用 insert 插入 `2` 次，例如：

INSERT INTO t1(object_id, object_name) SELECT * FROM t;
    INSERT INTO t2(object_id, object_name) SELECT * FROM t;
    COMMIT;

其实，以上这样的写法，不一定正确。在 `两次 insert` 过程中， 有可能 `t` 表的数据发生了改变，从而导致 `t1、t2` 表得到的数据不一样，正确的写法是用 `insert all`

INSERT ALL
       INTO t1(object_id, object_name)
       INTO t2(object_id, object_name)
    SELECT * FROM t;
    COMMIT;

## 1.1 思维导图 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rpd2Vpa2FuZw_size_16_color_FFFFFF_t_70][]

## 1.2 基础数据 ##

DROP TABLE stu; -- if exists
    
    CREATE TABLE stu (
       s_id NUMBER,
       s_xm VARCHAR2(30)
    );
    
    INSERT INTO stu(s_id, s_xm) VALUES (1, '小游子');
    INSERT INTO stu(s_id, s_xm) VALUES (2, '小优子');
    INSERT INTO stu(s_id, s_xm) VALUES (3, '小倩子');
    COMMIT;

# 2、实例分析 #

## 2.1 无条件插入 ##

CREATE TABLE stu1 AS SELECT * from stu WHERE 1 = 2;
    CREATE TABLE stu2 AS SELECT * from stu WHERE 1 = 2;
    
    INSERT ALL
       INTO stu1(s_id, s_xm)
       INTO stu2(s_id, s_xm)
    SELECT * FROM stu;
    COMMIT;
    
    SELECT * FROM stu1;
    SELECT * FROM stu2;

执行结果

![20190508222048163.png][]

## 2.2 有条件插入 ##

### **2.2.1 insert first** ###

对于每一行数据，只插入到 `第一个when` 条件成立的表，不继续检查其他条件。

DROP TABLE stu1; -- if exists
    DROP TABLE stu2; -- if exists
    
    CREATE TABLE stu1 AS SELECT * FROM stu WHERE 1 = 2;
    CREATE TABLE stu2 AS SELECT * FROM stu WHERE 1 = 2;
    
    INSERT FIRST 
      WHEN s_id <= 2 THEN 
         INTO stu1 (s_id, s_xm) 
      WHEN s_id >= 2 THEN -- 注意 2 是重复的哦
         INTO stu2 (s_id, s_xm)
    SELECT * FROM stu;
    
    SELECT * FROM stu1;
    SELECT * FROM stu2;

执行结果

![2019050822231142.png][]

### 2.2.2 insert all ###

对于每一行数据，对 `每一个when` 条件都进行检查，如果满足条件就执行插入操作。

-- 仅将上述 FIRST 改为 ALL
    DROP TABLE stu1; -- if exists
    DROP TABLE stu2; -- if exists
    
    CREATE TABLE stu1 AS SELECT * FROM stu WHERE 1 = 2;
    CREATE TABLE stu2 AS SELECT * FROM stu WHERE 1 = 2;
    
    INSERT ALL
      WHEN s_id <= 2 THEN 
         INTO stu1 (s_id, s_xm) 
      WHEN s_id >= 2 THEN -- 注意 2 是重复的哦
         INTO stu2 (s_id, s_xm)
    SELECT * FROM stu;
    
    SELECT * FROM stu1;
    SELECT * FROM stu2;

执行结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rpd2Vpa2FuZw_size_16_color_FFFFFF_t_70 1][]

# 3、其他操作 #

## 3.1 行转列插入 ##

DROP TABLE stu1; -- if exists
    DROP TABLE stu2; -- if exists
    
    CREATE TABLE stu1 (
      s_id NUMBER,
      s_xm VARCHAR(30) -- 数据类型要一致哦
    );
    
    CREATE TABLE stu2 (
      s_id  NUMBER,
      s_xm1 VARCHAR2(30),
      s_xm2 VARCHAR2(30),
      s_xm3 VARCHAR2(30)
    );
    
    INSERT INTO stu2(s_id, s_xm1, s_xm2, s_xm3) VALUES (1, 'a1', 'b1', 'c1');
    INSERT INTO stu2(s_id, s_xm1, s_xm2, s_xm3) VALUES (2, 'a2', 'b2', 'c2');
    INSERT INTO stu2(s_id, s_xm1, s_xm2, s_xm3) VALUES (3, 'a3', 'b3', 'c3');
    
    INSERT ALL
      INTO stu1 VALUES (s_id, s_xm1) 
      INTO stu1 VALUES (s_id, s_xm2)
      INTO stu1 VALUES (s_id, s_xm3)
    SELECT * FROM stu2;
    
    SELECT * FROM stu1;

执行结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rpd2Vpa2FuZw_size_16_color_FFFFFF_t_70 2][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rpd2Vpa2FuZw_size_16_color_FFFFFF_t_70]: /images/20220203/82971c6c8fa947918977d15d631eb043.png
[20190508222048163.png]: /images/20220203/a32b24622b1a46839b4e7543a10ef336.png
[2019050822231142.png]: /images/20220203/fd36c12ed7d44610835ba80b50da449d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rpd2Vpa2FuZw_size_16_color_FFFFFF_t_70 1]: /images/20220203/e0924f666ea5431daddc13768ff9457c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Rpd2Vpa2FuZw_size_16_color_FFFFFF_t_70 2]: /images/20220203/b0ce19f2603347c582581044e3ec199a.png