multi_table_insert

pivoting Insert ：行列转换多表插入

unconditional insert：oracle将执行每个insert，不进行排除

conditional all insert：有条件的insert all

conditional first insert：符合前面第一个的记录不再应用到下面的条件判断

示例：（参考）

-- Unconditional insert into ALL tables INSERT ALL   INTO sal_history VALUES(empid,hiredate,sal)   INTO mgr_history VALUES(empid,mgr,sysdate) SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR   FROM employees WHERE employee_id > 200; -- Pivoting insert to split non-relational data INSERT ALL   INTO Sales_info VALUES (employee_id,week_id,sales_MON)   INTO Sales_info VALUES (employee_id,week_id,sales_TUE)   INTO Sales_info VALUES (employee_id,week_id,sales_WED)   INTO Sales_info VALUES (employee_id,week_id,sales_THUR)   INTO Sales_info VALUES (employee_id,week_id, sales_FRI) SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,     sales_WED, sales_THUR,sales_FRI FROM Sales_source_data; -- Conditionally insert into ALL tables INSERT ALL   WHEN SAL>10000 THEN     INTO sal_history VALUES(EMPID,HIREDATE,SAL)   WHEN MGR>200 THEN     INTO mgr_history VALUES(EMPID,MGR,SYSDATE) SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR   FROM employees WHERE employee_id > 200; -- Insert into the FIRST table with a matching condition INSERT FIRST   WHEN SAL > 25000  THEN     INTO special_sal VALUES(DEPTID,SAL)   WHEN HIREDATE like ('%00%') THEN     INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)   WHEN HIREDATE like ('%99%') THEN     INTO hiredate_history_99 VALUES(DEPTID,HIREDATE)   ELSE     INTO hiredate_history VALUES(DEPTID, HIREDATE) SELECT department_id DEPTID, SUM(salary) SAL,     MAX(hire_date) HIREDATE   FROM employees GROUP BY department_id;

 

参考：

INSERT http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm#i2125362

这几种Insert有什么区别？
http://www.itpub.net/thread-1281678-1-1.html

sql入门之23 pivoting insert等 http://wmlm.itpub.net/post/12871/279365

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9537053/viewspace-746468/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9537053/viewspace-746468/