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后的表，方便自己控制。

oracle merge into的用法实例讲解

相关推荐