text: ## PostgreSQL Oracle 兼容性之 - insert all into, insert first into ... 多表写入  
              
  ### 作者       
  digoal            
            
  ### 日期     
  2018-07-13      
                                                             
  ### 标签                                                           
  PostgreSQL , Oracle , 多表批量写入 , insert all into , CTE    
                                                             
  ----                                                           
                                                             
  ## 背景     
  Oracle支持 insert all 的语法，同时往多个表插入。  
    
  https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm  
    
  https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm#i2125362  
    
  PostgreSQL可以通过CTE语法达到同样的效果。  
    
  https://www.postgresql.org/docs/11/static/queries-with.html  
    
  ## Oracle insert all into 例子  
    
  ```  
  create table a(id int, c1 int, c2 int);  
    
  create table b(id int, c1 int, c2 int);  
    
    
  set autotrace on;  
  ```  
    
  ```  
  insert all   
    into a (id,c1) values (id, col1)  
    into b (id,c2) values (id, col2)  
  select rownum as id, trunc(dbms_random.value(0, 100)) as col1, trunc(dbms_random.value(0, 100)) as col2 from dual connect by level <=10000;  
    
  20000 rows created.  
  ```  
    
  ```  
  Execution Plan  
  ----------------------------------------------------------  
  Plan hash value: 702343910  
    
  -----------------------------------------------------------------------------------------  
  | Id  | Operation                        | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
  -----------------------------------------------------------------------------------------  
  |   0 | INSERT STATEMENT                 |      |     1 |    39 |     2   (0)| 00:00:01 |  
  |   1 |  MULTI-TABLE INSERT              |      |       |       |            |          |  
  |   2 |   INTO                           | A    |       |       |            |          |  
  |   3 |   INTO                           | B    |       |       |            |          |  
  |   4 |    VIEW                          |      |     1 |    39 |     2   (0)| 00:00:01 |  
  |   5 |     COUNT                        |      |       |       |            |          |  
  |*  6 |      CONNECT BY WITHOUT FILTERING|      |       |       |            |          |  
  |   7 |       FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |  
  -----------------------------------------------------------------------------------------  
    
  Predicate Information (identified by operation id):  
  ---------------------------------------------------  
    
     6 - filter(LEVEL<=10000)  
    
    
  Statistics  
  ----------------------------------------------------------  
          531  recursive calls  
          661  db block gets  
          205  consistent gets  
            0  physical reads  
       392344  redo size  
          823  bytes sent via SQL*Net to client  
          932  bytes received via SQL*Net from client  
            3  SQL*Net roundtrips to/from client  
            6  sorts (memory)  
            0  sorts (disk)  
        20000  rows processed  
  ```  
    
  ## PostgreSQL CTE例子  
    
  ```  
  create table a(id int, c1 int, c2 int);  
    
  create table b(id int, c1 int, c2 int);  
  ```  
    
  ```  
  insert all   
    into a (id,c1) values (id, col1)  
    into b (id,c2) values (id, col2)  
  select id, random() col1, random() col2 from generate_series(1,10000) t(id);  
    
  ERROR:  42601: syntax error at or near "all"  
  LINE 1: insert all   
                 ^  
  LOCATION:  scanner_yyerror, scan.l:1087  
  ```  
    
  ```  
  with tmp as (select id, random() col1, random() col2 from generate_series(1,10000) t(id)),  
  ins1 as (insert into a (id,c1) select id,col1 from tmp)  
  insert into b (id,c2) select id,col2 from tmp;  
    
  INSERT 0 10000  
  ```  
    
  ```  
  postgres=# select count(*) from a;  
   count   
  -------  
   10000  
  (1 row)  
    
  postgres=# select count(*) from b;  
   count   
  -------  
   10000  
  (1 row)  
    
    
  postgres=# select * from a limit 10;  
   id | c1 | c2   
  ----+----+----  
    1 |  0 |     
    2 |  1 |     
    3 |  1 |     
    4 |  0 |     
    5 |  1 |     
    6 |  0 |     
    7 |  1 |     
    8 |  0 |     
    9 |  0 |     
   10 |  0 |     
  (10 rows)  
    
  postgres=# select * from b limit 10;  
   id | c1 | c2   
  ----+----+----  
    1 |    |  0  
    2 |    |  0  
    3 |    |  1  
    4 |    |  1  
    5 |    |  0  
    6 |    |  1  
    7 |    |  1  
    8 |    |  1  
    9 |    |  0  
   10 |    |  0  
  (10 rows)  
  ```  
      
  ## insert first into例子
  ```
  INSERT FIRST
     WHEN ottl < 100000 THEN
        INTO small_orders
           VALUES(oid, ottl, sid, cid)
     WHEN ottl > 100000 and ottl < 200000 THEN
        INTO medium_orders
           VALUES(oid, ottl, sid, cid)
     WHEN ottl > 290000 THEN  -- 需要注意
        INTO special_orders
     WHEN ottl > 200000 THEN
        INTO large_orders
           VALUES(oid, ottl, sid, cid)
     SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,
        o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem
        FROM orders o, customers c
        WHERE o.customer_id = c.customer_id;
  
  do language plpgsql $$
  declare
    v_oid xx;
    v_ottl xx;
    v_sid xx;
    v_cid xx;
    v_cl xx;
    v_cem xx;
  begin
    for v_oid,v_ottl,v_sid,v_cid,v_cl,v_cem in
      select o.order_id oid, o.customer_id cid, o.order_total ottl,
        o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem
        FROM orders o, customers c
        WHERE o.customer_id = c.customer_id 
    loop
      case when v_ottl < 100000 THEN
        INTO small_orders
           VALUES(v_oid, v_ottl, v_sid, v_cid);
      ....
      end case;
    end loop;
  end;
  $$;
  
  with tmp as (   SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,
        o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem
        FROM orders o, customers c
        WHERE o.customer_id = c.customer_id),
        tmp1 as (INTO small_orders select oid,ottl,sid,cid from tmp where ottl<100000),
        tmp2 as (INTO medium_orders select oid,ottl,sid,cid from tmp where ottl > 100000 and ottl < 200000),
        tmp3 as (INTO special_orders select oid,ottl,sid,cid from tmp where ottl > 290000)
        INTO large_orders select oid,ottl,sid,cid from tmp where ottl > 200000 and ottl <= 290000
  
       
  
  postgres=# create table t1(id int);
  CREATE TABLE
  postgres=# create table t2(id int);
  CREATE TABLE
  postgres=# create table t3(id int);
  CREATE TABLE
  postgres=# insert into t1 select generate_series(1,1000);
  INSERT 0 1000
  postgres=# with tmp as (select * from t1 where mod(id,2)=0), tmp1 as (insert into t2 select * from tmp where id<100) insert into t3 select * from tmp where id>=100;
  INSERT 0 451
  postgres=# select count(*) from t2;
   count 
  -------
      49
  (1 row)
  
  postgres=# select count(*) from t3;
   count 
  -------
     451
  (1 row)
  
  postgres=# select count(*) from t1;
   count 
  -------
    1000
  (1 row)
  ```
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
  #### [PostgreSQL 许愿链接](https://github.com/digoal/blog/issues/76 "269ac3d1c492e938c0191101c7238216")
  您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。[开不开森](https://github.com/digoal/blog/issues/76 "269ac3d1c492e938c0191101c7238216").  
    
    
  #### [9.9元购买3个月阿里云RDS PostgreSQL实例](https://www.aliyun.com/database/postgresqlactivity "57258f76c37864c6e6d23383d05714ea")
    
    
  #### [PostgreSQL 解决方案集合](https://yq.aliyun.com/topic/118 "40cff096e9ed7122c512b35d8561d9c8")
    
    
  #### [德哥 / digoal's github - 公益是一辈子的事.](https://github.com/digoal/blog/blob/master/README.md "22709685feb7cab07d30f30387f0a9ae")
    
    
  ![digoal's wechat](../pic/digoal_weixin.jpg "f7ad92eeba24523fd47a6e1a0e691b59")
    
  