oracle merge into的用法实例讲解

对一张表，在不同的条件下实现不同的操作（update/insert），在 oracle 中可以用 merge into

///////////////

有一个表T，有两个字段a、b，我们想在表T中做Insert/Update，如果条件满足，则更新T中b的值，否则在T中插入一条记录。在Microsoft的SQL语法中，很简单的一句判断就可以了，SQL Server中的语法如下：　　

if exists(select 1 from T where T.a='1001' )
    update T set T.b=2 Where T.a='1001' 
else 
    insert into T(a,b) values('1001',2); 

Oracle中，要实现相同的功能，要用到Merge into来实现（Oracle 9i引入的功能），其语法如下：

MERGE INTO table_name alias1 
USING (table|view|sub_query) alias2
ON (join condition) 
WHEN MATCHED THEN 
    UPDATE table_name 
    SET col1 = col_val1, 
           col2 = col_val2 
WHEN NOT MATCHED THEN 
    INSERT (column_list) VALUES (column_values); 

　　严格意义上讲，”在一个同时存在Insert和Update语法的Merge语句中，总共Insert/Update的记录数，就是Using语句中alias2的记录数”。所以，要实现上面的功能，可以这样写：

MERGE INTO T T1
USING (SELECT '1001' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
    UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN 
    INSERT (a,b) VALUES(T2.a,T2.b);

///////////////////////////////////

使用例子：

create table TEST (ID INTEGER,VALUE VARCHAR2(255) );   
insert into TEST values (1, 'test1');   
insert into TEST values (2, 'test2');   

我们想插入一条数据 {ID=2,NAME=’newtest2’} 那么可以这么写

MERGE INTO  TEST T1
USING (SELECT '2' as A FROM dual) T2 on (T1.ID=T2.ID)
WHEN MATCHED THEN UPDATE SET T1.NAME='newtest2'
WHEN NOT MATCHED THEN  INSERT (T1.ID, T1.NAME) VALUES ('1', 'newtest2'); 

如果ID为2的数据存在那么 UPDATE，如果不存在INSERT

注意事项：

Merge Into的原理是，从using 搜出来的结果逐条与on条件匹配，然后决定是update还是Insert。 当USING后面的sql没有查询到数据的时候，Merge Into语句是不会执行update和Insert操作的。

所以要想让Merge Into正常运行，要保证USING 后面的SELECT有数据，个人喜欢使用DUAL表作为USING后的表，方便自己控制。