语法：(其中as可以省略)
merge into table_name as table_alias
    using (table|view|sub_query) as alias
    on (join condition)
    when matched then
    update set
    col1 = col_val1,
    col2 = col2_val --9i 不可以有where条件，10g 可以
    when not matched then
    insert (column_list)—多个列以逗号分割 //可以不指定列
    values (column_values); --9i 不可以有where条件，10g 可以
作用：将源数据(来源于实际的表,视图,子查询)更新或插入到指定的表中(必须实际存在),依赖于on条件,好处是避免了多个insert 和update操作。merge是一个目标性明确的操作符,不允许在一个merge 语句中对相同的行insert 或update 操作。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于insert＋update。例子如下：
drop table t;
    create table t as select rownum id, a.* from dba_objects a;
    drop table t1;
    create table t1 as
    select rownum id, owner, table_name, cast('table' as varchar2(100)) object_type
    from dba_tables;
    select * from dba_objects;
    select * from dba_tables;
merge into t1 using t
    on (t.owner = t1.owner and t.object_name = t1.table_name and t.object_type = t1.object_type)
    when matched then update set t1.id = t.id
    when not matched then insert values (t.id, t.owner, t.object_name, t.object_type);--insert
    后面不写表示插入全部列
merge into t1 using t
    on (t.owner = t1.owner and t.object_name = t1.table_name)
    when matched then update set t1.id = t.id
    when not matched then insert values (t.id, t.owner, t.object_name, t.object_type);--常见错误，
    连接条件不能获得稳定的行，可以使用下面的用子查询
merge into t1
    using (select owner, object_name, max(id) id from t group by owner, object_name) t
    on (t.owner = t1.owner and t.object_name = t1.table_name)
    when matched then update set t1.id = t.id
    when not matched then insert values (t.id, t.owner, t.object_name);
select id, owner, object_name, object_type from t
    minus
    select * from t1;
    drop table subs;
    create table subs(msid number(9),
    ms_type char(1),
    areacode number(3)
    );
    drop table acct;
    create table acct(msid number(9),
    bill_month number(6),
    areacode number(3),
    fee number(8,2) default 0.00);
    insert into subs values(905310001,0,531);
    insert into subs values(905320001,1,532);
    insert into subs values(905330001,2,533);
    commit;
    merge into acct a --操作的表
    using subs b on (a.msid=b.msid)--使用原始数据来源的表，并且制定条件，条件必须有括号
    when matched then
    update set a.areacode=b.areacode--当匹配的时候，执行update操作，和直接update的语法
    不一样，不需要制定表名
    when not matched then--当不匹配的时候，执行insert操作，也不需要制定表名，若指定字段插入，则在insert后用括号标明，不指定是全部插入
    insert(msid,bill_month,areacode) values(b.msid,'200702',b.areacode);
另外，merge语句的update不能修改用于连接的列，否则会报错
    select * from acct;
    select * from subs;
    --10g新特性，单个操作
    merge into acct a
    using subs b on(a.msid=b.msid)
    when not matched then--只有单个not matched的时候，只做插入，不做更新，只有单个matched的时候，只做更新操作
    insert(a.msid,a.bill_month,a.areacode) values(b.msid,'200702',b.areacode);
    update acct set areacode=800 where msid=905320001;
    delete from acct where areacode=533 or areacode=531;
    insert into acct values(905320001,'200702',800,0.00);
    --删除重复行
    delete from subs b where b.rowid    select max(a.rowid) from subs a where a.msid=b.msid and a.ms_type=b.ms_type and
    a.areacode=b.areacode);
    --10g新特性，merge操作之后，只有匹配的update操作才可以，用delete where子句删除目标表中满足条件的行。
    merge into acct a
    using subs b on (a.msid=b.msid)
    when matched then
    update set a.areacode=b.areacode
    delete where (b.ms_type!=0)
    when not matched then
    insert(msid,bill_month,areacode)
    values(b.msid,'200702',b.areacode)
    where b.ms_type=0;
    --10g新特性，满足条件的插入和更新
    merge into acct a
    using subs b on (a.msid=b.msid)
    when matched then
    update set a.areacode=b.areacode
    where b.ms_type=0
    when not matched then
    insert(msid,bill_month,areacode)
    values(b.msid,'200702',b.areacode)
    where b.ms_type=0;
    select * from subs where ms_type=0;