## 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")
  
