Oracle 두 동일한 테이블간의 Merge 문 자동 생성

Oracle 두 동일한 테이블간의 Merge 문 자동 생성

#/bin/bash

TARGET_DB="TEST"
T_USER="TEST"
T_PWD="wony12"

####################
# AUTO GENERATE MERGE QUERY
####################
TAB_NM=$1
MERGE_DML=$(sqlplus -S $T_USER/$T_PWD@$TARGET_DB << EOF 
SET FEEDBACK OFF
SET SERVEROUTPUT ON
DECLARE
    CUR NUMBER;
    RES NUMBER;
    CNT INTEGER;
    DESC_TAB    DBMS_SQL.DESC_TAB;
    V_VARCHAR VARCHAR2(32767);
    V_DATE DATE;
    V_TIMESTAMP TIMESTAMP;
    V_NUMBER NUMBER;
    COL_TYPE_NM VARCHAR2(300);
    SKIP_YN VARCHAR2(20);
    SQL_TEXT VARCHAR2(2000) := 'SELECT * FROM ${TAB_NM}';
    WHERE_TEXT VARCHAR2(2000) := ' WHERE ROWNUM < 1';
    OUTPUT VARCHAR2(32767) := 'MERGE INTO ${TAB_NM} A USING (SELECT * FROM ${TAB_NM}_MIG WHERE ';
BEGIN
    DBMS_OUTPUT.ENABLE (BUFFER_SIZE => NULL);
    CUR := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(CUR, SQL_TEXT||WHERE_TEXT, DBMS_SQL.NATIVE);
    DBMS_SQL.DESCRIBE_COLUMNS (CUR, CNT, DESC_TAB);

    FOR C IN (SELECT * FROM ALL_CONS_COLUMNS WHERE OWNER = 'TEST' AND CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'P' AND OWNER = 'TEST' AND TABLE_NAME = '${TAB_NM}')) LOOP
        OUTPUT := OUTPUT||C.COLUMN_NAME||' = CUR_ROW(C).'||C.COLUMN_NAME||' AND ';
    END LOOP;
        OUTPUT := SUBSTR(OUTPUT, 1, LENGTH(OUTPUT) - 4);
        OUTPUT := OUTPUT||') B ';
    
    OUTPUT := OUTPUT||'ON (';
    FOR C IN (SELECT * FROM ALL_CONS_COLUMNS WHERE OWNER = 'TEST' AND CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'P' AND OWNER = 'TEST' AND TABLE_NAME = '${TAB_NM}')) LOOP
        OUTPUT := OUTPUT||'A.'||C.COLUMN_NAME||' = B.'||C.COLUMN_NAME||' AND ';
    END LOOP;
        OUTPUT := SUBSTR(OUTPUT, 1, LENGTH(OUTPUT) - 4);
        OUTPUT := OUTPUT||') ';
        
    OUTPUT := OUTPUT||'WHEN MATCHED THEN UPDATE SET ';
    FOR I IN 1 .. CNT LOOP
        SKIP_YN := 'FALSE';
        FOR C IN (SELECT * FROM ALL_CONS_COLUMNS WHERE OWNER = 'TEST' AND CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'P' AND OWNER = 'TEST' AND TABLE_NAME = 'TB_M00_ATTRS')) LOOP
            IF ( C.COLUMN_NAME = DESC_TAB(I).COL_NAME ) THEN
                SKIP_YN := 'TRUE';
            END IF;
        END LOOP;
        IF ( SKIP_YN = 'FALSE' ) THEN
            OUTPUT := OUTPUT||DESC_TAB(I).COL_NAME||'=CUR_ROW(C).'||DESC_TAB(I).COL_NAME||',';
        END IF;
    END LOOP;  
    OUTPUT := SUBSTR(OUTPUT, 1, LENGTH(OUTPUT) - 1);
    OUTPUT := OUTPUT||' WHEN NOT MATCHED THEN INSERT VALUES CUR_ROW(C)';
    DBMS_OUTPUT.PUT_LINE(OUTPUT);    
END;
/
EOF
)
echo "${MERGE_DML}"
RES=$(sqlplus -S $T_USER/$T_PWD@$TARGET_DB << EOF 
SET FEEDBACK OFF
SET SERVEROUTPUT ON
DECLARE
	ERRORS NUMBER;
    CURSOR CUR_TAB IS SELECT * FROM ${TAB_NM}_MIG;
    TYPE CUR_TYPE IS TABLE OF ${TAB_NM}%ROWTYPE ;
    CUR_ROW CUR_TYPE;
BEGIN
    DBMS_OUTPUT.ENABLE (BUFFER_SIZE => NULL);
    
    OPEN CUR_TAB;
    LOOP    
        FETCH CUR_TAB BULK COLLECT INTO CUR_ROW;
        FORALL C IN CUR_ROW.FIRST .. CUR_ROW.LAST SAVE EXCEPTIONS
            "${MERGE_DML}"
        EXIT WHEN CUR_ROW.COUNT = 0;
    END LOOP;      
    CLOSE CUR_TAB;
    EXCEPTION
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE(SQLERRM);
	WHEN DML_ERRORS THEN
		ERRORS := SQL%BULK_EXCEPTIONS.COUNT;
		DBMS_OUTPUT.PUT_LINE ('THE TOTAL NUMBER OF ERRORS OCCURED ARE '|| ERRORS);
		FOR J IN 1..ERRORS LOOP
		DBMS_OUTPUT.PUT_LINE ('THE ERROR ITERATION IS  '
							  || SQL%BULK_EXCEPTIONS(J).ERROR_INDEX
							  || ' AND THE ERROR CODE IS '
							  || SQL%BULK_EXCEPTIONS(J).ERROR_CODE
							  || ' AND THE ERROR MESSAGE IS '
							  || SQLERRM (- SQL%BULK_EXCEPTIONS(J).ERROR_CODE)
							);
		END LOOP;
	COMMIT;
END;
/
EOF
)
echo "$RES"