Oracle中的Merge into的用法

在进行SQL语句编写时，我们经常会遇到大量的同时进行Insert/Update的语句 ，也就是说当存在记录时，就更新(Update)，不存在数据时，就插入(Insert)。
在写程序的时候可能是以下的形式，但是oracle中是不能这么用的。所以有merge into的写法来代替。

if (条件){
   insert/delete/update table_name 
else
   insert /delete/update table_name 
}

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)
    WHERE 条件！;

下面举一个T100的例子：将imao_t中单位字段跟imaa_t中的进行同步

merge into imao_t a
USING (select * from imaa_t where imaaent='100') b
on ( a.imao001=b.imaa001 and  a.imaoent='100')
when matched then update set a.imao002=b.imaa006   --如果匹配则更新
when  not matched then     --如果没有则新增
insert (a.imaoent,a.imao001,a.imao002,a.imaoud001) values (b.imaaent,b.imaa001,b.imaa006,1)

在看个列子

--根据完工入库单去更新特征值
merge into inaj_t a 
using (select * from sfec_t where sfecent=100 and sfecsite='200'
and sfec001 in(select sfaadocno from sfaa_t where sfaadocno like 'CJ%' and sfaa010 like '3%' AND sfaaent=100 and sfaasite='200' GROUP BY sfaadocno) 
) b on( a.inaj020=b.sfec001 and a.inajent=b.sfecent and a.inajsite=b.sfecsite and a.inaj002=b.sfecseq and a.inaj005=b.sfec005 and a.inaj001=b.sfecdocno)
when matched then update set a.inaj006 = b.sfec006

另外一个比较经典的改写看例子

merge into (select a.col1, a.col4, a.nchar_col
              from f3111 a
             where a.nchar_col = 'CD10'
               and a.col17 = 'WX'
               and a.col8 = 'CD1999'
               AND A.COL19 = 0) a
using (select b.col2, b.col3, b.col6
         from f3112 b
        group by b.col2, b.col3, b.col6) b
on (b.col3 = a.col4 and a.nchar_col = b.col6)
when matched then
  update set a.col1 = to_char(b.col2) where a.clo1 <> to_char(b.col2)

用MERGE INTO 改写update的多个子查询

merge into a
using (select a.rowid as rid,
              x1.d_id,
              x1.a_person,
              x1.a_date,
              x1.c_date,
              x2.s_date
         from a
         left join x1
           on (x1.r_number = a.r_number)
         left join (select max(s_date) as s_date, x2.u_id
                     from x2
                    where x2.fee_date = :b4
                    group by x2.u_id) x2
           on (x2.u_id = a, u_id)
        where a.city_code = :b3
          and mod(a.u_id, :b2) = :b1 - 1) b
on (b.rid = a.rowid)
when matched then
  update
     set a.op_d_id    = b.d_id,
         a.op_word_no = b.a_person,
         a.c_date     = b.c_date,
         a.s_date     = b.s_date,
         a.a_date     = b.a_date