Oracle insert多表插入语句

Oracle 多表插入

代码如下：

CREATE TABLE EMP1 AS SELECT EMPNO, ENAME, JOB FROM EMP WHERE 1=2
;
CREATE TABLE EMP2 AS SELECT EMPNO, ENAME, DEPTNO FROM EMP WHERE 1=2
;

--没有条件insert
INSERT ALL
INTO EMP1(EMPNO, ENAME, JOB) VALUES (EMPNO, ENAME, JOB) --可以指定EMP1部分字段插入
INTO EMP2(EMPNO, ENAME, DEPTNO)VALUES (EMPNO, ENAME, DEPTNO)
SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP

--有条件的insert all
INSERT ALL
  WHEN JOB IN('SALESMAN', 'MANAGER')
    THEN INTO EMP1(EMPNO, ENAME, JOB) VALUES(EMPNO, ENAME, JOB) --可以指定EMP1部分字段插入
  WHEN DEPTNO IN('20', '30')
    THEN INTO EMP2(EMPNO, ENAME, DEPTNO) VALUES(EMPNO, ENAME, DEPTNO)
SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP

--insert first
--first 语句中，当第一个表符合条件后，第二个表就不会再插入对应的行，
--表emp2中不会有与emp1相同的数据存在，这就是与insert all的不同之处
INSERT first
  WHEN JOB IN('SALESMAN', 'MANAGER')
    THEN INTO EMP1(EMPNO, ENAME, JOB) VALUES(EMPNO, ENAME, JOB) --可以指定EMP1部分字段插入
  WHEN DEPTNO IN('20', '30')
    THEN INTO EMP2(EMPNO, ENAME, DEPTNO) VALUES(EMPNO, ENAME, DEPTNO)
SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP

--转置insert
create table t2(d varchar2(10), des varchar2(50));
create table t1 as
select '熊样，精神不佳' as d1,
       '猫样，温驯听话' as d2,
       '狗样，神气活现' as d3,
       '鸟样，向往明天' as d4,
       '花样，愿你快乐像花儿一样' as d5
 from dual;

insert all
into t2(d, des) values('周一', d1)
into t2(d, des) values('周二', d2)
into t2(d, des) values('周三', d3)
into t2(d, des) values('周四', d4)
into t2(d, des) values('周五', d5)
select d1, d2, d3, d4, d5 from t1;

select * from t2
--等价于
insert into t2(d,des)
select '周一', d1 from t1 union all
select '周二', d1 from t1 union all
select '周三', d1 from t1 union all
select '周四', d1 from t1 union all
select '周五', d1 from t1 union all

代码均摘自书本

^  - ^ ……