[SQL] Oracle Merge 문법 자동 생성기(PL/SQL)

오라클 Marge문을 자동으로 생성 하는 방법에 대해서 작성하고자 합니다. 업무를 진행할 때 Insert와 Update를 동시에 하고자 한다면 Merge문을 최대한 활용하게 됩니다. 한번 작성하면 다시 변경할 일을 없기는 하지만, 여러개의 원장 데이터를 서비스 테이블로 옮기는 작업을 매번 진행할 때 유용하게 사용 가능 합니다.

따라서 아래 내용은 단일 테이블을 기준으로 작성했으나, 필요에 따라 프로시저를 반복 호출해서 사용해도 됩니다.

1. Oracle Merge문 자동 생성 PL/SQL

DECLARE
   V_OWNER          VARCHAR2(30);
   V_PE_TABLE_NM    VARCHAR2(40);
   V_PT_TABLE_NM    VARCHAR2(40);
BEGIN
   
  V_OWNER           := 'HR';         /* 스키마명 */
  V_PE_TABLE_NM     := 'EMPLOYEES';  /* 원장 테이블명 */
  V_PT_TABLE_NM     := 'EMPLOYEES';  /* 타겟 테이블명 */         
  
  DECLARE CURSOR C IS
  SELECT   
           A.OWNER
          ,A.TABLE_NAME
          ,A.COLUMN_NAME
          ,A.DATA_TYPE
          ,A.COLUMN_ID
          ,A.LAST_COLUMN_ID
          ,b.COLUMN_NAME AS PK_COLUMN_NAME
  FROM     (
            SELECT 
                   A.OWNER
                  ,A.TABLE_NAME
                  ,A.COLUMN_NAME
                  ,DECODE(A.DATA_TYPE, 'VARCHAR2', A.DATA_TYPE||'('||A.DATA_LENGTH||')'
                                     , 'CHAR',     A.DATA_TYPE||'('||A.DATA_LENGTH||')'  
                                     , 'NUMBER'  , A.DATA_TYPE||'('||A.DATA_PRECISION||', '||A.DATA_SCALE||')' 
                                     , 'DATE', 'DATE') AS DATA_TYPE
                  ,A.COLUMN_ID AS COLUMN_ID
                  ,LAST_VALUE(A.COLUMN_ID) OVER (PARTITION BY A.TABLE_NAME ORDER BY A.COLUMN_ID 
                                                 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_COLUMN_ID -- 테이블 컬럼ID 중에서 최고 값
            FROM   DBA_TAB_COLUMNS A
            WHERE  A.OWNER       = V_OWNER
            AND    A.TABLE_NAME  = V_PT_TABLE_NM      /* 특정 테이블에 대해서 만들 경우 */
            ) A,
            (
            SELECT   B.COLUMN_NAME
            FROM     DBA_CONSTRAINTS A
                    ,DBA_CONS_COLUMNS B
            WHERE    A.OWNER           = V_OWNER
            AND      A.TABLE_NAME      = V_PT_TABLE_NM 
            AND      A.CONSTRAINT_TYPE = 'P'         /* PK 컬럼을 조회 */
            AND      B.OWNER           = A.OWNER
            AND      B.CONSTRAINT_NAME = A.CONSTRAINT_NAME
            AND      B.TABLE_NAME      = A.TABLE_NAME
            ) B
  WHERE     B.COLUMN_NAME (+)= A.COLUMN_NAME       
  ORDER  BY A.TABLE_NAME, A.COLUMN_ID ;
   
  BEGIN

    -- Oracle Merge Auto Script Start
     
    DBMS_OUTPUT.PUT_LINE('MERGE INTO '|| V_OWNER ||'.'||V_PT_TABLE_NM||' A USING');
    DBMS_OUTPUT.PUT_LINE('      (');
    DBMS_OUTPUT.PUT_LINE('      SELECT');
     FOR CTL_LIST IN C LOOP
       -- SQL*Loader  Control File 형식에 해당하는 콤마(,)를 마지막에는 제거 함.
       IF CTL_LIST.COLUMN_ID <> CTL_LIST.LAST_COLUMN_ID THEN 
         DBMS_OUTPUT.PUT_LINE( '            S.' || CTL_LIST.COLUMN_NAME || ',' );
       ELSE
         DBMS_OUTPUT.PUT_LINE( '            S.' || CTL_LIST.COLUMN_NAME  );
       END IF;         

     END LOOP;
    DBMS_OUTPUT.PUT_LINE('      FROM '|| V_OWNER ||'.'||V_PE_TABLE_NM||' S');
    DBMS_OUTPUT.PUT_LINE('      ) B');
    DBMS_OUTPUT.PUT_LINE('ON    (');
     FOR CTL_LIST IN C LOOP
       -- SQL*Loader  Control File 형식에 해당하는 콤마(,)를 마지막에는 제거 함.
       IF     CTL_LIST.PK_COLUMN_NAME IS NOT NULL AND CTL_LIST.COLUMN_ID = 1  THEN 
         DBMS_OUTPUT.PUT_LINE( '            A.' || CTL_LIST.COLUMN_NAME || ' = B.' ||CTL_LIST.COLUMN_NAME );
       ELSIF  CTL_LIST.PK_COLUMN_NAME IS NOT NULL AND CTL_LIST.COLUMN_ID != 1 THEN
         DBMS_OUTPUT.PUT_LINE( '        AND A.' || CTL_LIST.COLUMN_NAME || ' = B.' ||CTL_LIST.COLUMN_NAME );
       END IF;         
     END LOOP;
    DBMS_OUTPUT.PUT_LINE('      )');
    DBMS_OUTPUT.PUT_LINE('WHEN  MATCHED THEN');
    DBMS_OUTPUT.PUT_LINE('      UPDATE  SET');
     FOR CTL_LIST IN C LOOP
       IF CTL_LIST.PK_COLUMN_NAME IS NULL THEN
         -- SQL*Loader  Control File 형식에 해당하는 콤마(,)를 마지막에는 제거 함.
         IF CTL_LIST.COLUMN_ID <> CTL_LIST.LAST_COLUMN_ID THEN 
           DBMS_OUTPUT.PUT_LINE( '            A.' || CTL_LIST.COLUMN_NAME || ' = B.' ||CTL_LIST.COLUMN_NAME || ',' );
         ELSE
           DBMS_OUTPUT.PUT_LINE( '            A.' || CTL_LIST.COLUMN_NAME || ' = B.' ||CTL_LIST.COLUMN_NAME  );
         END IF;         
       END IF;
     END LOOP;       
    DBMS_OUTPUT.PUT_LINE('WHEN  NOT MATCHED THEN ');
    DBMS_OUTPUT.PUT_LINE('      INSERT ');
    DBMS_OUTPUT.PUT_LINE('               ( ');
     FOR CTL_LIST IN C LOOP
       -- SQL*Loader  Control File 형식에 해당하는 콤마(,)를 마지막에는 제거 함.
       IF CTL_LIST.COLUMN_ID <> CTL_LIST.LAST_COLUMN_ID THEN 
         DBMS_OUTPUT.PUT_LINE( '            A.' || CTL_LIST.COLUMN_NAME || ',' );
       ELSE
         DBMS_OUTPUT.PUT_LINE( '            A.' || CTL_LIST.COLUMN_NAME  );
       END IF;         
     END LOOP;
    DBMS_OUTPUT.PUT_LINE('               ) ');
    DBMS_OUTPUT.PUT_LINE('      VALUES ');
    DBMS_OUTPUT.PUT_LINE('               ( ');
     FOR CTL_LIST IN C LOOP
       -- SQL*Loader  Control File 형식에 해당하는 콤마(,)를 마지막에는 제거 함.
       IF CTL_LIST.COLUMN_ID <> CTL_LIST.LAST_COLUMN_ID THEN 
         DBMS_OUTPUT.PUT_LINE( '            B.' || CTL_LIST.COLUMN_NAME || ',' );
       ELSE
         DBMS_OUTPUT.PUT_LINE( '            B.' || CTL_LIST.COLUMN_NAME  );
       END IF;         
     END LOOP;                    
    DBMS_OUTPUT.PUT_LINE('               ) ');
    DBMS_OUTPUT.PUT_LINE(';');        

  END;  


EXCEPTION
   WHEN NO_DATA_FOUND THEN
      NULL;
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERROR');
      NULL;
END;
--

2. DBMS Output 결과 

  - HR 스키마에 속한 EMPLOYEES 테이블을 기준으로 Merge 문법을 자동으로 확인 가능 합니다.

  - USING 절에 있는 원장 데이터에 대한 내용은 용도에 따라 변경 하시면 됩니다.

MERGE INTO HR.EMPLOYEES A USING
      (
      SELECT
            S.EMPLOYEE_ID,
            S.FIRST_NAME,
            S.LAST_NAME,
            S.EMAIL,
            S.PHONE_NUMBER,
            S.HIRE_DATE,
            S.JOB_ID,
            S.SALARY,
            S.COMMISSION_PCT,
            S.MANAGER_ID,
            S.DEPARTMENT_ID
      FROM HR.EMPLOYEES S
      ) B
ON    (
            A.EMPLOYEE_ID = B.EMPLOYEE_ID
      )
WHEN  MATCHED THEN
      UPDATE  SET
            A.FIRST_NAME = B.FIRST_NAME,
            A.LAST_NAME = B.LAST_NAME,
            A.EMAIL = B.EMAIL,
            A.PHONE_NUMBER = B.PHONE_NUMBER,
            A.HIRE_DATE = B.HIRE_DATE,
            A.JOB_ID = B.JOB_ID,
            A.SALARY = B.SALARY,
            A.COMMISSION_PCT = B.COMMISSION_PCT,
            A.MANAGER_ID = B.MANAGER_ID,
            A.DEPARTMENT_ID = B.DEPARTMENT_ID
WHEN  NOT MATCHED THEN 
      INSERT 
               ( 
            A.EMPLOYEE_ID,
            A.FIRST_NAME,
            A.LAST_NAME,
            A.EMAIL,
            A.PHONE_NUMBER,
            A.HIRE_DATE,
            A.JOB_ID,
            A.SALARY,
            A.COMMISSION_PCT,
            A.MANAGER_ID,
            A.DEPARTMENT_ID
               ) 
      VALUES 
               ( 
            B.EMPLOYEE_ID,
            B.FIRST_NAME,
            B.LAST_NAME,
            B.EMAIL,
            B.PHONE_NUMBER,
            B.HIRE_DATE,
            B.JOB_ID,
            B.SALARY,
            B.COMMISSION_PCT,
            B.MANAGER_ID,
            B.DEPARTMENT_ID
               ) 
;