1.insert first/all
利用insert first和insert all可以同时插入多个表
insert first/all语法
[ ALL | FIRST ]
WHEN condition THEN insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
[WHEN condition THEN insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
]...
[ELSE insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
]
insert first对于每一行的数据,只插入到第一个condition条件成立的表。其他后续的操作不执行。
insert all执行每一个when的condition判断,满足时插入数据
多表插入语句的限制条件:
只能对表执行多表插入语句,不能对视图或物化视图执行;
不能对远端表执行多表插入语句;
不能使用表集合表达式;
不能超过999个目标列;
在RAC环境中或目标表是索引组织表或目标表上建有BITMAP索引时,多表插入语句不能并行执行;
多表插入语句不支持执行计划稳定性;
多表插入语句中的子查询不能使用序列。
2.Upsert
Upsert is a series of conditional update and/or insert operations. Records that exist within a table will be updated. New records will be inserted into the table.
Upsert functionality is implemented in Oracle with the MERGE command.
Merge is a SQL command that performs a series of conditional update and/or insert operations. A row is updated if it exists, inserted if it doesn't. Merge was first introduced with Oracle 9i.
Examples
MERGE INTO emp e1 USING emp_load e2 ON (e2.empno = e1.empno)
WHEN MATCHED THEN
update set e1.sal = e2.sal
WHEN NOT MATCHED THEN
insert (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (e2.empno, e2.ename, e2.job, e2.mgr, e2.hiredate, e2.sal, e2.comm, e2.deptno);
以上资料源于互联网
阅读(739) | 评论(0) | 转发(0) |