Oracle中merge语法的使用

1、基本语法

MERGE INTO 表名
USING (子查询) 
ON (主键连接)
-- 当匹配得上连接条件时，不包含主键连接的字段
WHEN MATCHED THEN 
更新、删除操作
-- 当匹配不上连接条件时，包含全部字段
WHEN NOT MATCHED THEN 
更新、删除、插入操作

2、使用实例

merge into hdc_dim.dim_department a
using (

select 
       t.deptcode as DEPTCODE, --科室编码
       t.deptname as DEPTNAME, --科室名称
       nvl(t.deptclass, '6') as DEPT_CLASS, --科室类型 
       t.wardcode as WARDCODE, --病区编码
       t.deptcode_case as DEPTCODE_CASE, --病案科室编码
       t.deptname_case as DEPTNAME_CASE, --病案科室名称
       t.deptcode_account as DEPTCODE_ACCOUNT, --核算科室编码
       t.deptname_account as DEPTNAME_ACCOUNT, --核算科室名称
       t.deptcode_standard as DEPTCODE_STANDARD, --标准科室编码
       t.deptname_standard as DEPTNAME_STANDARD, --标准科室名称
       t.sign_void as SIGN_VOID, --作废标记 Y作废 N有效
       sysdate as CREATE_TIME, --创建时间
       sysdate as UPDATE_TIME --更新时间
  from department t
         
       ) b
on (a.deptcode = b.deptcode )
when matched then
  update
     set a.DEPTNAME          = b.DEPTNAME,
         a.DEPT_CLASS        = b.DEPT_CLASS,
         a.WARDCODE          = b.WARDCODE,
         a.DEPTCODE_CASE     = b.DEPTCODE_CASE,
         a.DEPTNAME_CASE     = b.DEPTNAME_CASE,
         a.DEPTCODE_ACCOUNT  = b.DEPTCODE_ACCOUNT,
         a.DEPTNAME_ACCOUNT  = b.DEPTNAME_ACCOUNT,
         a.DEPTCODE_STANDARD = b.DEPTCODE_STANDARD,
         a.DEPTNAME_STANDARD = b.DEPTNAME_STANDARD,
         a.SIGN_VOID         = b.SIGN_VOID,
         a.UPDATE_TIME       = b.UPDATE_TIME
when not matched then
  insert
    (
     DEPTCODE,
     DEPTNAME,
     DEPT_CLASS,
     WARDCODE,
     DEPTCODE_CASE,
     DEPTNAME_CASE,
     DEPTCODE_ACCOUNT,
     DEPTNAME_ACCOUNT,
     DEPTCODE_STANDARD,
     DEPTNAME_STANDARD,
     SIGN_VOID,
     CREATE_TIME,
     UPDATE_TIME)
  values
    (
     b.DEPTCODE,
     b.DEPTNAME,
     b.DEPT_CLASS,
     b.WARDCODE,
     b.DEPTCODE_CASE,
     b.DEPTNAME_CASE,
     b.DEPTCODE_ACCOUNT,
     b.DEPTNAME_ACCOUNT,
     b.DEPTCODE_STANDARD,
     b.DEPTNAME_STANDARD,
     b.SIGN_VOID,
     b.CREATE_TIME,
     b.UPDATE_TIME)