데이터베이스 질문과 답변
여러분에 많은 도움으로 일단...데이터 동기화하는걸 최종으로 만들었는데요!
프로시저를 처음 만들어봐서...혹시 개선사항이나 이렇게 했으면 좋을것같은게...있으면
봐주시면 감사하겠습니다!!
머지문은 데이터 많은것들만 시행했습니당!
CREATE OR REPLACE PROCEDURE TOSS.SD_TABLE_SYNC
IS
/******************************************************************************
NAME: TOSS.SD_TABLE_SYNC
PURPOSE: 운영DB -> 개발 DB 동기화 프로시저
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2024/04/03 yongsei 1. Created this procedure.
NOTES:
Automatically available Auto Replace Keywords:
Object Name: SD_TABLE_SYNC
Sysdate: 2024/04/03
Date and Time: 2024/04/03, 오후 :17:18, and 2024/04/03 오후 :17:18
Username: yongsei (set in TOAD Options, Procedure Editor)
Table Name: (set in the "New PL/SQL Object" dialog)
******************************************************************************/
V_SQL VARCHAR(500);
V_TABLE_NM VARCHAR(30);
V_DB_LINK_NM VARCHAR(30);
V_STRT_PRIOD VARCHAR(5);
V_END_PRIOD VARCHAR(5);
V_DATE VARCHAR(8);
V_PFMC_CYCLE VARCHAR(5);
BEGIN
DBMS_OUTPUT.PUT_LINE('운영 테이블 -> 개발 테이블 DB 동기화');
FOR TABLE_LIST IN (
SELECT TABLE_NAME
, DB_LINK_NAME
, STRT_PRIOD
, END_PRIOD
, PFMC_CYCLE /* M 머지 / A 전체*/
FROM TOSS.SD_INF_STD_DATE
WHERE USE_YN = 'Y'
)
LOOP
-- 테이블 & DB링크 명칭 변수 매칭
V_TABLE_NM := TABLE_LIST.TABLE_NAME;
V_DB_LINK_NM := TABLE_LIST.DB_LINK_NAME;
V_STRT_PRIOD := TABLE_LIST.STRT_PRIOD;
V_END_PRIOD := TABLE_LIST.END_PRIOD;
V_DATE := 'YYYYMMDD';
V_PFMC_CYCLE := TABLE_LIST.PFMC_CYCLE;
IF V_DB_LINK_NM IS NOT NULL THEN /* DB링크 없을 시 리턴. */
/* 전체 삭제 동기화(A) */
IF V_PFMC_CYCLE = 'A' THEN
-- 개발 테이블 TRUNCATE
DBMS_UTILITY.EXEC_DDL_STATEMENT('TRUNCATE TABLE TOSS.'|| V_TABLE_NM);
DBMS_OUTPUT.PUT_LINE(V_TABLE_NM || ' DELETE 완료');
-- 운영테이블 -> 개발 테이블 INSERT
V_SQL := 'INSERT /*+ prallel ( select_table 4 ) */ INTO TOSS.' || V_TABLE_NM || ' SELECT /*+ prallel ( select_table 4 ) */ * FROM '|| V_TABLE_NM || V_DB_LINK_NM;
EXECUTE IMMEDIATE V_SQL;
DBMS_OUTPUT.PUT_LINE(V_TABLE_NM || ' INSERT 완료');
ELSIF V_PFMC_CYCLE = 'M' THEN
/* MERGE(M) */
IF V_TABLE_NM = 'SD_CUST_ORDER_ITEM' THEN
/* 운영에 삭제된 데이터 개발 삭제*/
FOR DEL1 IN (
SELECT A.COMP_CD
, A.CUST_CD
, A.DATA_TP
, A.ITEM_CD
FROM SD_CUST_ORDER_ITEM A
LEFT JOIN SD_CUST_ORDER_ITEM@WEBORD_REAL B
ON A.COMP_CD = B.COMP_CD
AND A.CUST_CD = B.CUST_CD
AND A.DATA_TP = B.DATA_TP
AND A.ITEM_CD = B.ITEM_CD
WHERE B.COMP_CD IS NULL
)
LOOP
DELETE FROM SD_CUST_ORDER_ITEM A
WHERE A.COMP_CD = DEL1.COMP_CD
AND A.CUST_CD = DEL1.CUST_CD
AND A.DATA_TP = DEL1.DATA_TP
AND A.ITEM_CD = DEL1.ITEM_CD;
END LOOP;
/* 운영에 추가 및 수정된 데이터 개발 동기화 */
MERGE INTO TOSS.SD_CUST_ORDER_ITEM A
USING (
SELECT *
FROM (
SELECT *
FROM TOSS.SD_CUST_ORDER_ITEM@WEBORD_REAL
WHERE UPDATED >= TRUNC(SYSDATE)-1
)
) B
ON
(
A.COMP_CD = B.COMP_CD
AND A.CUST_CD = B.CUST_CD
AND A.DATA_TP = B.DATA_TP
AND A.ITEM_CD = B.ITEM_CD
)
WHEN MATCHED THEN
UPDATE SET
A.INPUTTED = B.INPUTTED
, A.INPUT_ID = B.INPUT_ID
, A.UPDATED = B.UPDATED
, A.UPDATE_ID = B.UPDATE_ID
, A.TERMINALCD = B.TERMINALCD
, A.START_DT = B.START_DT
, A.STOP_DT = B.STOP_DT
, A.BRAND = B.BRAND
WHEN NOT MATCHED THEN
INSERT (
A.COMP_CD
, A.CUST_CD
, A.DATA_TP
, A.ITEM_CD
, A.INPUTTED
, A.INPUT_ID
, A.UPDATED
, A.UPDATE_ID
, A.TERMINALCD
, A.START_DT
, A.STOP_DT
, A.BRAND
) VALUES (
B.COMP_CD
, B.CUST_CD
, B.DATA_TP
, B.ITEM_CD
, B.INPUTTED
, B.INPUT_ID
, B.UPDATED
, B.UPDATE_ID
, B.TERMINALCD
, B.START_DT
, B.STOP_DT
, B.BRAND
);
ELSIF V_TABLE_NM = 'SD_CUST_CLOSE' THEN
/* 운영에 삭제된 데이터 개발 삭제*/
FOR DEL2 IN (
SELECT A.COMP_CD
, A.HIRCHY_CD
, A.CUST_CD
, A.YMD
FROM SD_CUST_CLOSE A
LEFT JOIN SD_CUST_CLOSE@WEBORD_REAL B
ON A.COMP_CD = B.COMP_CD
AND A.HIRCHY_CD = B.HIRCHY_CD
AND A.CUST_CD = B.CUST_CD
AND A.YMD = B.YMD
WHERE B.COMP_CD IS NULL
)
LOOP
DELETE FROM SD_CUST_CLOSE A
WHERE A.COMP_CD = DEL2.COMP_CD
AND A.HIRCHY_CD = DEL2.HIRCHY_CD
AND A.CUST_CD = DEL2.CUST_CD
AND A.YMD = DEL2.YMD;
END LOOP;
/* 운영에 추가 및 수정된 데이터 개발 동기화 */
MERGE INTO TOSS.SD_CUST_CLOSE A
USING (
SELECT *
FROM (
SELECT *
FROM TOSS.SD_CUST_CLOSE@WEBORD_REAL
WHERE UPDATED >= TRUNC(SYSDATE)-1
)
) B
ON (
A.COMP_CD = B.COMP_CD
AND A.HIRCHY_CD = B.HIRCHY_CD
AND A.CUST_CD = B.CUST_CD
AND A.YMD = B.YMD
)
WHEN MATCHED THEN
UPDATE
SET A.REMARKS = B.REMARKS
, A.UPDATED = B.UPDATED
, A.UPDATE_ID = B.UPDATE_ID
, A.TERMINALCD = B.TERMINALCD
WHEN NOT MATCHED THEN
INSERT (
A.COMP_CD
, A.HIRCHY_CD
, A.CUST_CD
, A.YMD
, A.REMARKS
, A.UPDATED
, A.UPDATE_ID
, A.TERMINALCD
) VALUES (
B.COMP_CD
, B.HIRCHY_CD
, B.CUST_CD
, B.YMD
, B.REMARKS
, B.UPDATED
, B.UPDATE_ID
, B.TERMINALCD
);
ELSIF V_TABLE_NM = 'BC_ITEM_PRICE' THEN
/* 운영에 삭제된 데이터 개발 삭제*/
FOR DEL3 IN (
SELECT A.COMP_CD
, A.ITEM_CD
, A.CUST_CD
, A.START_DT
, A.STOP_DT
, A.PRICE_TP
FROM BC_ITEM_PRICE A
LEFT JOIN BC_ITEM_PRICE@WEBORD_REAL B
ON A.COMP_CD = B.COMP_CD
AND A.ITEM_CD = B.ITEM_CD
AND A.CUST_CD = B.CUST_CD
AND A.START_DT = B.START_DT
AND A.STOP_DT = B.STOP_DT
AND A.PRICE_TP = B.PRICE_TP
WHERE B.COMP_CD IS NULL
)
LOOP
DELETE FROM BC_ITEM_PRICE A
WHERE A.COMP_CD = DEL3.COMP_CD
AND A.ITEM_CD = DEL3.ITEM_CD
AND A.CUST_CD = DEL3.CUST_CD
AND A.START_DT = DEL3.START_DT
AND A.STOP_DT = DEL3.STOP_DT
AND A.PRICE_TP = DEL3.PRICE_TP;
END LOOP;
/* 운영에 추가 및 수정된 데이터 개발 동기화 */
MERGE INTO TOSS.BC_ITEM_PRICE A
USING (
SELECT *
FROM (
SELECT *
FROM TOSS.BC_ITEM_PRICE@WEBORD_REAL
WHERE UPDATED >= TRUNC(SYSDATE)-1
)
) B
ON (
A.COMP_CD = B.COMP_CD
AND A.ITEM_CD = B.ITEM_CD
AND A.CUST_CD = B.CUST_CD
AND A.START_DT = B.START_DT
AND A.STOP_DT = B.STOP_DT
AND A.PRICE_TP = B.PRICE_TP
)
WHEN MATCHED THEN
UPDATE
SET A.UNIT_PRICE = B.UNIT_PRICE
, A.REMARKS = B.REMARKS
, A.HIRCHY_CD = B.HIRCHY_CD
, A.USE_YN = B.USE_YN
, A.INPUTTED = B.INPUTTED
, A.INPUT_ID = B.INPUT_ID
, A.UPDATED = B.UPDATED
, A.UPDATE_ID = B.UPDATE_ID
, A.TERMINALCD = B.TERMINALCD
, A.APPR_NO = B.APPR_NO
WHEN NOT MATCHED THEN
INSERT (
A.COMP_CD
, A.ITEM_CD
, A.CUST_CD
, A.START_DT
, A.STOP_DT
, A.UNIT_PRICE
, A.PRICE_TP
, A.REMARKS
, A.HIRCHY_CD
, A.USE_YN
, A.INPUTTED
, A.INPUT_ID
, A.UPDATED
, A.UPDATE_ID
, A.TERMINALCD
, A.APPR_NO
) VALUES (
B.COMP_CD
, B.ITEM_CD
, B.CUST_CD
, B.START_DT
, B.STOP_DT
, B.UNIT_PRICE
, B.PRICE_TP
, B.REMARKS
, B.HIRCHY_CD
, B.USE_YN
, B.INPUTTED
, B.INPUT_ID
, B.UPDATED
, B.UPDATE_ID
, B.TERMINALCD
, B.APPR_NO
);
ELSIF V_TABLE_NM = 'BC_HIER_PRICE' THEN
/* 운영에 삭제된 데이터 개발 삭제*/
FOR DEL4 IN (
SELECT A.COMP_CD
, A.ITEM_CD
, A.HIER_CD
, A.START_DT
, A.STOP_DT
, A.PRICE_TP
FROM BC_HIER_PRICE A
LEFT JOIN BC_HIER_PRICE@WEBORD_REAL B
ON A.COMP_CD = B.COMP_CD
AND A.ITEM_CD = B.ITEM_CD
AND A.HIER_CD = B.HIER_CD
AND A.START_DT = B.START_DT
AND A.STOP_DT = B.STOP_DT
AND A.PRICE_TP = B.PRICE_TP
WHERE B.COMP_CD IS NULL
)
LOOP
DELETE FROM BC_HIER_PRICE A
WHERE A.COMP_CD = DEL4.COMP_CD
AND A.ITEM_CD = DEL4.ITEM_CD
AND A.HIER_CD = DEL4.HIER_CD
AND A.START_DT = DEL4.START_DT
AND A.STOP_DT = DEL4.STOP_DT
AND A.PRICE_TP = DEL4.PRICE_TP;
END LOOP;
/* 운영에 추가 및 수정된 데이터 개발 동기화 */
MERGE INTO TOSS.BC_HIER_PRICE A
USING (
SELECT *
FROM (
SELECT *
FROM TOSS.BC_HIER_PRICE@WEBORD_REAL
WHERE UPDATED >= TRUNC(SYSDATE)-1
)
) B
ON (
A.COMP_CD = B.COMP_CD
AND A.ITEM_CD = B.ITEM_CD
AND A.HIER_CD = B.HIER_CD
AND A.START_DT = B.START_DT
AND A.STOP_DT = B.STOP_DT
AND A.PRICE_TP = B.PRICE_TP
)
WHEN MATCHED THEN
UPDATE
SET A.UNIT_PRICE = B.UNIT_PRICE
, A.REMARKS = B.REMARKS
, A.USE_YN = B.USE_YN
, A.INPUTTED = B.INPUTTED
, A.INPUT_ID = B.INPUT_ID
, A.UPDATED = B.UPDATED
, A.UPDATE_ID = B.UPDATE_ID
, A.TERMINALCD = B.TERMINALCD
, A.APPR_NO = B.APPR_NO
WHEN NOT MATCHED THEN
INSERT (
A.COMP_CD
, A.ITEM_CD
, A.HIER_CD
, A.START_DT
, A.STOP_DT
, A.UNIT_PRICE
, A.PRICE_TP
, A.REMARKS
, A.USE_YN
, A.INPUTTED
, A.INPUT_ID
, A.UPDATED
, A.UPDATE_ID
, A.TERMINALCD
, A.APPR_NO
) VALUES (
B.COMP_CD
, B.ITEM_CD
, B.HIER_CD
, B.START_DT
, B.STOP_DT
, B.UNIT_PRICE
, B.PRICE_TP
, B.REMARKS
, B.USE_YN
, B.INPUTTED
, B.INPUT_ID
, B.UPDATED
, B.UPDATE_ID
, B.TERMINALCD
, B.APPR_NO
);
ELSIF V_TABLE_NM = 'BC_ITEM_PLANT' THEN
/* 운영에 삭제된 데이터 개발 삭제*/
FOR DEL5 IN (
SELECT A.COMP_CD
, A.ITEM_CD
, A.CUST_CD
, A.LOGISTIC_CD
FROM BC_ITEM_PLANT A
LEFT JOIN BC_ITEM_PLANT@WEBORD_REAL B
ON A.COMP_CD = B.COMP_CD
AND A.ITEM_CD = B.ITEM_CD
AND A.CUST_CD = B.CUST_CD
AND A.LOGISTIC_CD = B.LOGISTIC_CD
WHERE B.COMP_CD IS NULL
)
LOOP
DELETE FROM BC_ITEM_PLANT A
WHERE A.COMP_CD = DEL5.COMP_CD
AND A.ITEM_CD = DEL5.ITEM_CD
AND A.CUST_CD = DEL5.CUST_CD
AND A.LOGISTIC_CD = DEL5.LOGISTIC_CD;
END LOOP;
/* 운영에 추가 및 수정된 데이터 개발 동기화 */
MERGE INTO TOSS.BC_ITEM_PLANT A
USING (
SELECT *
FROM (
SELECT *
FROM TOSS.BC_ITEM_PLANT@WEBORD_REAL
WHERE UPDATED >= TRUNC(SYSDATE)-1
)
) B
ON (
A.COMP_CD = B.COMP_CD
AND A.ITEM_CD = B.ITEM_CD
AND A.CUST_CD = B.CUST_CD
AND A.LOGISTIC_CD = B.LOGISTIC_CD
)
WHEN MATCHED THEN
UPDATE
SET A.LOGISTIC1 = B.LOGISTIC1
, A.LOGISTIC2 = B.LOGISTIC2
, A.LOGISTIC3 = B.LOGISTIC3
, A.STATUS = B.STATUS
, A.INPUTTED = B.INPUTTED
, A.INPUT_ID = B.INPUT_ID
, A.UPDATED = B.UPDATED
, A.UPDATE_ID = B.UPDATE_ID
WHEN NOT MATCHED THEN
INSERT (
A.COMP_CD
, A.ITEM_CD
, A.CUST_CD
, A.LOGISTIC_CD
, A.LOGISTIC1
, A.LOGISTIC2
, A.LOGISTIC3
, A.STATUS
, A.INPUTTED
, A.INPUT_ID
, A.UPDATED
, A.UPDATE_ID
) VALUES (
B.COMP_CD
, B.ITEM_CD
, B.CUST_CD
, B.LOGISTIC_CD
, B.LOGISTIC1
, B.LOGISTIC2
, B.LOGISTIC3
, B.STATUS
, B.INPUTTED
, B.INPUT_ID
, B.UPDATED
, B.UPDATE_ID
);
END IF;
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE(V_TABLE_NM || ' 테이블의 DB링크가 없습니다.');
END IF;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('운영 테이블 => 개발 테이블 DB 동기화 완료');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(V_TABLE_NM || V_DB_LINK_NM || '데이터 이전중 오류가 발생하였습니다.');
END SD_TABLE_SYNC;
) 버튼을 클릭하여 작성 하시면 됩니다.