解释:MERGE语句是Oracle9i新增的语法,用来合并UPDATE和INSERT语句。通过MERGE语句,根据一张表或子查询的连接条件对另外一张表进行查询,连接条件匹配上的进行UPDATE,无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于INSERT+UPDATE。
语法格式:
MERGE [INTO [schema .] table [t_alias]
USING [schema .] { table | view | subquery } [t_alias]
ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;
例子:
create table ttest(
ctel varchar2(10),
cname varchar2(10),
csex varchar2(2));
create table ttest1(
ctel varchar2(10),
cname varchar2(10),
csex varchar2(2));
insert into ttest1 values('123','zhangsan','m');
insert into ttest1 values('1234','zhangsan1','WM');
commit;
merge into ttest a
using (select t.ctel, t.cname, t.csex from ttest1 t) b
on (a.cname = b.cname and a.csex = b.csex)
when matched then
update set a.ctel = b.ctel
when not matched then
insert
(ctel, cname, csex)
values
(b.ctel, b.cname, b.csex);
select *from ttest;
执行结果: