[SQL QUERY] DIMENSION 미매핑 데이터 삽입하기

 Description
 Create by Cheon Jeonghyeon
 on 2012.03.07 in Korea 
 

---

작업 설명
 DW 계정의 모든 Dimension 테이블을 대상으로 (D_로 시작하는 테이블)
 미매핑 데이터가 없는 테이블들에 (Merge 문) 미매핑데이터를 삽입한다.
 이때 DATE 형은 오늘 날짜를, NUMBER 형은 0을,
 CHAR나 VARCHAR2 형은 'Z' 코드를 삽입하는데
 컬럼 길이가 4보다 작을 경우는 컬럼 길이에 맞추어 삽입하도록 한다. 

다음과 같은 명령을 PL/SQL을 이용하여 DYNAMIC SQL 호출 방식을 사용하여 수행하도록 한다. 

BEGIN

    FOR i IN ( SELECT  DISTINCT LISTAGG(COL) WITHIN

                GROUP (ORDER BY COLUMN_ID) OVER (PARTITION BY TABLE_NAME)||'('|| LISTAGG(COLUMN_NAME,',') WITHIN

                GROUP (ORDER BY COLUMN_ID) OVER (PARTITION BY TABLE_NAME)||')' ins_statement

        FROM   (select TABLE_NAME,

                       COLUMN_ID,

                       DECODE(COLUMN_ID, 1, 'MERGE INTO '||OWNER||'.'||TABLE_NAME||' T USING ( SELECT ')||DECODE(DATA_TYPE, 'DATE', ''''||TO_CHAR(SYSDATE,'YYYY/MM/DD')||'''', 'NUMBER', '0', 

                                                     ''''||  DECODE(

                                                              SUBSTR(COLUMN_NAME,-2),

                                                              'NM',

                                                              '미매핑',

                                                              LPAD('Z', CASE WHEN DATA_LENGTH > 4 THEN 4 ELSE DATA_LENGTH END, 'Z')

                                                              )

                                                         ||''''

                        )||' '||COLUMN_NAME||DECODE(LEAD(COLUMN_ID) OVER(PARTITION BY TABLE_NAME

                                ORDER BY COLUMN_ID), NULL, ' FROM DUAL) S ON (T.'||FIRST_VALUE(COLUMN_NAME)OVER(PARTITION BY TABLE_NAME ORDER BY COLUMN_ID)||' = S.'||FIRST_VALUE(COLUMN_NAME)OVER(PARTITION BY TABLE_NAME ORDER BY COLUMN_ID)||') WHEN NOT MATCHED THEN INSERT VALUES ' , ', ') COL,

                       'S.'||COLUMN_NAME COLUMN_NAME        

                from   all_tab_columns A

                where  TABLE_NAME like 'D#_%' ESCAPE '#'

                AND    OWNER = 'HTSBA01')) LOOP

      EXECUTE IMMEDIATE i.ins_statement;   

    END LOOP;

    commit;

END;