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);

 

二、举个栗子（现实应用）

merge into DW_WOR_DETAIL wor
using (SELECT DISTINCT *
         FROM (select a.work_id,
                      a.order_id,
                      b.prod_id,
                      c.prod_name,
                      b.product_num,
                      a.flownode_code,
                      d.flownode_name,
                      a.is_automation,
                      a.work_type,
                      e.work_type_name,
                      a.busi_id,
                      f.busi_name,
                      a.action_type,
                      g.action_type_desc,
                      a.district_bureau_id,
                      h.bureau_name district_bureau_name,
                      --nvl(h.bureau_name, t.BUREAU_NAME) district_bureau_name,
                      a.bureau_id,
                      i.bureau_name,
                      a.exec_dept_id,
                      j.exec_dept_name,
                      a.get_staff_id,
                      k.staff_name           get_staff_name,
                      a.get_date,
                      a.response_staff_id,
                      l.staff_name           response_staff_name,
                      a.response_date,
                      a.work_status,
                      m.work_status_name,
                      a.create_date,
                      a.rollback_date,
                      a.status_chg_date,
                      a.rollback_reason_code,
                      n.error_reason         rollback_error_reason,
                      a.session_code,
                      o.if_grab_flag,
                      o.mos_deal_flag,
                      a.overtime_reason_code,
                      q.error_reason         overtime_error_reason
                 from t_od_work@iom107_to_iom31 a
                 left join t_od_order@iom107_to_iom31 b
                   on a.order_id = b.order_id
                 left join t_product@iom107_to_iom31 c
                   on b.prod_id = c.prod_id
                 left join t_od_flownode@iom107_to_iom31 d
                   on a.flownode_code = d.flownode_code
                 left join t_od_work_type@iom107_to_iom31 e
                   on a.work_type = e.work_type
                 left join t_od_business@iom107_to_iom31 f
                   on a.busi_id = f.busi_id
                 left join t_od_action_type@iom107_to_iom31 g
                   on a.action_type = g.action_type
                 left join t_od_bureau@iom107_to_iom31 h
                   on a.district_bureau_id = h.bureau_id
                 left join t_od_bureau@iom107_to_iom31 i
                   on a.bureau_id = i.bureau_id
                 left join t_od_exec_dept@iom107_to_iom31 j
                   on a.exec_dept_id = j.exec_dept_id
                 left join t_sys_staff@iom107_to_iom31 k
                   on a.get_staff_id = k.staff_id
                 left join t_sys_staff@iom107_to_iom31 l
                   on a.response_staff_id = l.staff_id
                 left join t_od_work_status@iom107_to_iom31 m
                   on a.work_status = m.work_status
                  and a.flownode_code = m.flownode_code
                 left join t_od_error_reason@iom107_to_iom31 n
                   on a.rollback_reason_code = n.error_reason_code
                 left join (select *
                             from (select w.*,
                                          row_number() over(partition by work_id order by mos_work_seq desc) rn
                                     from inter_mobile_work@iom107_to_iom31 w)
                            where rn = 1) o ---inter_mobile_work@iom107_to_iom31.work_id非唯一,按mos_work_seq排序取最大
                   on a.work_id = o.work_id
                 left join t_od_error_reason@iom107_to_iom31 q
                   on a.overtime_reason_code = q.error_reason_code
                where a.work_id in
                      (select id from t_dw_detail_tmp tmp where tmp.type = '4'))) aa
on (wor.work_id = aa.work_id)
when matched then
  update
     set wor.ORDER_ID              = aa.order_id,
         wor.PROD_ID               = aa.prod_id,
         wor.PROD_NAME             = aa.prod_name,
         wor.PRODUCT_NUM           = aa.product_num,
         wor.FLOWNODE_CODE         = aa.flownode_code,
         wor.FLOWNODE_NAME         = aa.flownode_name,
         wor.IS_AUTOMATION         = aa.is_automation,
         wor.WORK_TYPE             = aa.work_type,
         wor.WORK_TYPE_NAME        = aa.work_type_name,
         wor.BUSI_ID               = aa.busi_id,
         wor.BUSI_NAME             = aa.busi_name,
         wor.ACTION_TYPE           = aa.action_type,
         wor.ACTION_TYPE_DESC      = aa.action_type_desc,
         wor.DISTRICT_BUREAU_ID    = aa.district_bureau_id,
         wor.DISTRICT_BUREAU_NAME  = aa.district_bureau_name,
         wor.BUREAU_ID             = aa.bureau_id,
         wor.BUREAU_NAME           = aa.bureau_name,
         wor.EXEC_DEPT_ID          = aa.exec_dept_id,
         wor.EXEC_DEPT_NAME        = aa.exec_dept_name,
         wor.GET_STAFF_ID          = aa.get_staff_id,
         wor.GET_STAFF_NAME        = aa.get_staff_name,
         wor.GET_DATE              = aa.get_date,
         wor.RESPONSE_STAFF_ID     = aa.response_staff_id,
         wor.RESPONSE_STAFF_NAME   = aa.response_staff_name,
         wor.RESPONSE_DATE         = aa.response_date,
         wor.WORK_STATUS           = aa.work_status,
         wor.WORK_STATUS_NAME      = aa.work_status_name,
         wor.CREATE_DATE           = aa.create_date,
         wor.ROLLBACK_DATE         = aa.rollback_date,
         wor.STATUS_CHG_DATE       = aa.status_chg_date,
         wor.ROLLBACK_REASON_CODE  = aa.rollback_reason_code,
         wor.ROLLBACK_ERROR_REASON = aa.rollback_error_reason,
         wor.SESSION_CODE          = aa.session_code,
         wor.IF_GRAB_FLAG          = aa.if_grab_flag,
         wor.MOS_DEAL_FLAG         = aa.mos_deal_flag,
         wor.OVERTIME_REASON_CODE  = aa.overtime_reason_code,
         wor.OVERTIME_ERROR_REASON = aa.overtime_error_reason
when not matched then
  insert
  values
    (aa.work_id,
     aa.order_id,
     aa.prod_id,
     aa.prod_name,
     aa.product_num,
     aa.flownode_code,
     aa.flownode_name,
     aa.is_automation,
     aa.work_type,
     aa.work_type_name,
     aa.busi_id,
     aa.busi_name,
     aa.action_type,
     aa.action_type_desc,
     aa.district_bureau_id,
     aa.district_bureau_name,
     aa.bureau_id,
     aa.bureau_name,
     aa.exec_dept_id,
     aa.exec_dept_name,
     aa.get_staff_id,
     aa.get_staff_name,
     aa.get_date,
     aa.response_staff_id,
     aa.response_staff_name,
     aa.response_date,
     aa.work_status,
     aa.work_status_name,
     aa.create_date,
     aa.rollback_date,
     aa.status_chg_date,
     aa.rollback_reason_code,
     aa.rollback_error_reason,
     aa.session_code,
     aa.if_grab_flag,
     aa.mos_deal_flag,
     aa.overtime_reason_code,
     aa.overtime_error_reason);