1. 값이 있는지 확인

2. 있으면 UPDATE

3. 없으면 INSERT

구문

예제

기본

merge into PDM_Q_705 a using (
      select p_kindcd as kindcd,
             p_new_gbn as new_gbn,
             p_chrg_gbn as chrg_gbn,
             p_chrg_dept as chrg_dept,
             p_chrg_xuser as chrg_xuser,
             p_chrg_companycd as chrg_companycd,
             p_item_code as item_code,
             common.get_cookie('SYSCOMP') as login_company,
             common.get_cookie('SYSID') as login_id,
             sysdate as login_ymd
       from dual
       ) d 
       on (a.KINDCD = d.kindcd 
       and a.new_gbn = d.new_gbn 
       and a.chrg_gbn = d.chrg_gbn 
       and a.item_code = d.item_code)
when matched then
     update set A.CHRG_COMPANYCD = d.chrg_companycd
              , A.CHRG_DEPT = d.chrg_dept
              , A.CHRG_XUSER = d.chrg_xuser
              , A.LOGIN_COMPANY = d.login_company
              , A.LOGIN_ID = d.login_id
              , A.LOGIN_YMD = d.login_ymd
when not matched then
     insert (A.KINDCD, A.NEW_GBN, A.CHRG_GBN, A.CHRG_DEPT, A.CHRG_XUSER, A.LOGIN_COMPANY, A.LOGIN_ID, A.LOGIN_YMD, A.CHRG_COMPANYCD, a.item_code)
     values (D.KINDCD, D.NEW_GBN, D.CHRG_GBN, D.CHRG_DEPT, D.CHRG_XUSER, D.LOGIN_COMPANY, D.LOGIN_ID, D.LOGIN_YMD, D.CHRG_COMPANYCD, d.item_code)
     ;

JOIN

merge into pdm_q_705_log t
      using (
             select distinct p_kindcd as kindcd, p_new_gbn as new_gbn ,a.item_code, a.chrg_gbn, a.chrg_dept, a.chrg_xuser,
                             common.get_cookie('SYSCOMP') as login_company ,common.get_cookie('SYSID') as login_id, sysdate as login_ymd, a.chrg_companycd
             from pdm_q_705 a, dhs_410 b, dhs_010 c
             where a.item_code = c.item_code
             and   a.kindcd = c.kindcd1
             and   b.companycd = c.companycd
             and   b.project_cd = c.kindcd1
             and   b.item_code = c.item_code
             and   a.kindcd = p_kindcd
             and   a.item_code = p_item_code 
             ) d
              on (t.kindcd = d.kindcd
              and t.new_gbn = d.new_gbn
              and t.item_code = d.item_code
              and t.chrg_gbn = d.chrg_gbn)
       when matched then
            update set t.chrg_companycd = d.chrg_companycd
                      , t.chrg_dept = d.chrg_dept
                      , t.chrg_xuser = d.chrg_xuser
                      , t.login_company = d.login_company
                      , t.login_id = d.login_id
                      , t.login_ymd = d.login_ymd
       when not matched then
            insert (t.kindcd, t.new_gbn, t.item_code, t.chrg_gbn, t.chrg_dept, t.chrg_xuser, t.login_company, t.login_id, t.login_ymd, t.chrg_companycd)
            values (d.kindcd, d.new_gbn, d.item_code, d.chrg_gbn, d.chrg_dept, d.chrg_xuser, d.login_company, d.login_id, d.login_ymd, d.chrg_companycd)
            ;