데이터 있는지 없는지에 따른 UPDATE, INSERT 처리

MERGE INTO 테이블명
  USING ( 서브쿼리 및 데이터 )
  ON( 비교 대상 )
  WHEN MATCHED THEN  
     데이터가 맞츨때 처리할 것  SQL
  WHEN NOT MATCHED THEN
      데이터가 틀렸을때 처리할 SQL

예제 1)
//JSP 페이지에서 데이터를 가지고 와서 바로 처리할때
 MERGE INTO ih_mw_gj
 USING ( DUAL )
  ON( a.mw_id = ? and a.sisulmool_id = ? and a.s_gj_cd = ? )
  WHEN NOT MATCHED THEN  
         UPDATE 
               SET date = ?

 WHERE mw_id = ?
      AND sisulmool_id = ?
      AND s_gj_cd = ?
   WHEN NOT MATCHED THEN
 INSERT( mw_gj_id, mw_id,  sisulmool_id, s_gj_cd)
VALUES( ?, ?, ?)
        
예제 1) 
//데이터를 서브쿼리를 이용하여 데이터를 입력 받는 방법      
MERGE INTO ih_mw_gj a
 USING ( select "+next_value+" as mw_id, "+dept_id+" AS dept_id ,b.* from ih_mw_gj_temp b where ih_user_id= '"+user_id+"'  ) b
  ON( a.mw_id = b.mw_id and a.sisulmool_id = b.in_sisulmool_id and a.s_gj_cd = b.in_s_gj_cd  )
  WHEN NOT MATCHED THEN  
        UPDATE
             SET date = ?
         WHERE mw_id = ?
           AND sisulmool_id = ?
           AND s_gj_cd = ?
 WHEN NOT MATCHED THEN
          INSERT( mw_gj_id, mw_id,  sisulmool_id, s_gj_cd)
        VALUES( ?, ?, ?)

위에 두가지에 SQL의 사용에 따른 데이터를 처리하면 JAVA에서 처리하는 부분이 훨씬 간편해 질수 있다.

실제 처리 SQL
==================================================================================================
MERGE INTO TF_CRDT_090428BAK A
 USING (SELECT * FROM TF_CRDT B WHERE CARD_NO LIKE '5%') B
    ON ( A.SLIP_YYYY = B.SLIP_YYYY AND A.SLIP_SEQ = B.SLIP_SEQ AND A.LINE_SEQ = B.LINE_SEQ)
  WHEN MATCHED THEN 
        UPDATE SET A.CRDT_STATUS = B.CRDT_STATUS
         WHERE A.SLIP_YYYY = B.SLIP_YYYY
           AND A.SLIP_SEQ = B.SLIP_SEQ
           AND A.LINE_SEQ = B.LINE_SEQ          
 WHEN NOT MATCHED THEN
          INSERT( SLIP_YYYY, SLIP_SEQ, JRNL_SEQ, LINE_SEQ, CRDT_STATUS, CARD_NO, APPR_NO, 
                       STTL_TRANS_YN, USE_AREA_TYPE, CLIENT_CODE, CLIENT_NAME, BZ_REG_NO, 
                       TRANS_DATE, CURR_CODE, TRANS_KOR_AMT, CURR_RATE, TRANS_CURR_AMT, 
                       REQ_DATE, REQ_KOR_AMT, REQ_CURR_AMT, CLOSE_DOC, CREATE_EMP_NO,
                       CREATE_DATE_TIME, UPDATE_EMP_NO, UPDATE_DATE_TIME, PROJ_CODE,
                       ORG_ACCT_CODE, TRANS_SEQ) 
        VALUES( B.SLIP_YYYY, B.SLIP_SEQ, B.JRNL_SEQ, B.LINE_SEQ, B.CRDT_STATUS, B.CARD_NO, 
                      B.APPR_NO, B.STTL_TRANS_YN, B.USE_AREA_TYPE, B.CLIENT_CODE,
                      B.CLIENT_NAME, B.BZ_REG_NO, B.TRANS_DATE, B.CURR_CODE,
                      B.TRANS_KOR_AMT, B.CURR_RATE, B.TRANS_CURR_AMT,
                      B.REQ_DATE, B.REQ_KOR_AMT, B.REQ_CURR_AMT, B.CLOSE_DOC,
                      B.CREATE_EMP_NO, B.CREATE_DATE_TIME, B.UPDATE_EMP_NO, 
                      B.UPDATE_DATE_TIME, B.PROJ_CODE, B.ORG_ACCT_CODE, B.TRANS_SEQ
                   );