DB CAFE
메뉴
둘러보기
대문
최근 바뀜
임의 문서로
도움말
검색
여기를 가리키는 문서
가리키는 글의 최근 바뀜
특수 문서 목록
문서 정보
최근 바뀜
도움말
로그인
행위
문서
토론
원본 보기
역사
MS MIG 전환솔루션 문서 원본 보기
←
MS MIG 전환솔루션
문서 편집 권한이 없습니다. 다음 이유를 확인해주세요:
요청한 명령은 다음 권한을 가진 사용자에게 제한됩니다:
사용자
.
이 문서는 편집하거나 다른 명령을 할 수 없도록 보호되어 있습니다.
문서의 원본을 보거나 복사할 수 있습니다.
== MS MIG 전환 솔루션 == === 레파지토리 정보 === ==== MIG 환경설정 ==== # TB_MIG_CONFIG : 데이터 입력, 집계함수 정보 입력 , 환경설정 # TB_MIG_CONFIG_DBOWNER : ( owner 별 db 접속정보/ asis-tobe) # TB_MIG_CONFIG_USER # TB_MIG_COD_CLS : 코드분류 # TB_MIG_COD : 코드 , CD008 : 집계함수 항목추가 # TB_MIG_COL_DIC_ASIS : ASIS 컬럼 정보입력 ( asis 딕셔너리) # TB_MIG_COL_DIC_TOBE : TOBE 컬럼 정보입력 (tobe 딕셔너리) , 신규테이블 추가시 ==== 전환 매핑 관리 ==== # 테이블 매핑 ## TB_MIG_MAP_TBL_USER -- 전환 설계자(담당자) 테이블 매핑시 TB_MIG_MAP_TBL_SCDUL 과 같이 입력 ## TB_MIG_MAP_TBL -- 테이블 전환방식/ 정보 저장/ ... 중요... ##: TB_MIG_MAP_TBL_HST : 이력 ## TB_MIG_MAP_TBL_SCDUL -- 테이블별 일정관리 ## TB_MIG_MAP_TBL_SUBSET -- 테이블 매핑 설계 ##: TB_MIG_MAP_TBL_SUBSET_HST : 테이블 매핑 설계 이력 ## TB_MIG_MAP_TBL_PGM -- 전환프로그램 컴파일 이력 ## TB_MIG_MAP_TBL_ASIS -- ASIS 테이블 전환방식/ 정보 저장/ ... 중요... # 컬럼 매핑 ## TB_MIG_MAP_COL : 컬럼매핑설계 , UV_MIG_TOBE_COL_MAP_DEF 관련뷰 ##: TB_MIG_MAP_COL_HST 컬럼매핑 설계 이력 ## TB_MIG_MAP_COL_ASIS : ASIS 매핑 안되는경우 필요 ##: TB_MIG_MAP_COL_ASIS_HST ==== 전환검증 ==== # TB_MIG_VERIFY_AGG_FUNC -- 집계함수 검증 결과 # TB_MIG_VERIFY_ASIS_ROWS_COUNT -- 일괄검증 후 생성된 로우카운터를 가져온다. # TB_MIG_VERIFY_TOBE_ROWS_COUNT -- TB_MIG_LOG에 ROW카운트 대체 # TB_MIG_VERIFY_BIZRULE - B/R 검증 # TB_MIG_VERIFY_BIZRULE_RSLT -- B/R 검증 실행 결과 수행이력 # TB_MIG_ASIS_LOAD_VERIFY_COUNT 검증 및 카운트 ==== 전환결과 리포트 ==== # TB_MIG_LOG -- 중요 - 전환결과 로그(전환계/운영계) 임으로 타겟서버에 로그테이블/프로시져 이 필수 존재해야함 ==== 추가기능 ==== # TB_MIG_MAP_TBL_ROWS_SPLIT -- 분리전환 미사용 . # TB_MIG_CONFIG_SESSION - 불필요 /* 생성보류(미사용 추정) */ # TB_MIG_MAP_AUTOMAP_OBJ -- 미사용 (자동매핑기능) 막혔음. # TB_MIG_CONFIG_LOAD_TARGET_DB - 불필요 # TB_MIG_VERIFY_TBL_COUNT -- 미사용 ==== 주요 VIEW ==== # 기능 뷰 ## UV_MIG_SUBJECT_AREA ## UV_MIG_COL_DIC_TOBE ## UV_MIG_COL_DIC_ASIS ## UV_MIG_ASIS_COL_MAP_DEF ASIS 오너 추가시 수정해야함 ## UV_MIG_TOBE_COL_MAP_DEF TOBE 오너 추가시 수정해야함 ## UV_MIG_LOG -- 전환 개발 FAIL 내역 ## UV_MIG_LOG_DASHBOARD -- 불필요 추정 # 참조용 뷰 ## MIGCOMMON.ALL_TABLES ## MIGCOMMON.ALL_TAB_COMMENTS ## MIGCOMMON.ALL_VIEWS ## MIGCOMMON.ALL_OBJECTS ## MIGCOMMON.ALL_TAB_COLUMNS ## MIGCOMMON.ALL_TAB_COLS ## MIGCOMMON.ALL_CONS_COLUMNS ## MIGCOMMON.ALL_CONSTRAINTS ## MIGCOMMON.ALL_COL_COMMENTS ## DBA_TAB_PARTITIONS_ASIS -- 빈껍데기만 생성, AS-IS 파티션 있을시 변경할 것. ## UV_MIG_CONFIG_USER ## UV_MIG_ASIS_STATS_TOTAL ## UV_MIG_STATS_SCDUL_PLAN ## UV_MIG_MAP_TBL_STATS -- OWNER 확인할것. ## UV_MIG_STATS_SA_OWNER -- OWNER 확인할것. ## UV_MIG_STATS_SA_OWNER_TOT ## UV_MIG_VERIFY_ASIS_ROWS_COUNT # 매핑용 뷰 ## UV_MIG_ASIS_COD_CLS /* AS-IS 공통코드 그룹 */ ## UV_MIG_DATE_CHK /* 날짜 체크 */ ## UV_MIG_FULL_DATE_CHK ## UV_MIG_FULL_TIME_CHK ## UV_MIG_VERIFY_STAT ## UV_MIG_TOBE_COD : (중요)변경필요 ## UV_MIG_ASIS_COD : (중요)변경필요 # 매핑 관련 ## UV_MIG_MAP_COL ## UV_MIG_MAP_COL_HST ## UV_MIG_MAP_TBL_PGM ## UV_MIG_MAP_TBL_SUBSET_HST ## UV_MIG_MAP_TBL_HST ## UV_MIG_MAP_TBL_ASIS ## UV_MIG_MAP_TBL_BASE ## UV_MIG_MAP_TBL :(중요) ## UV_MIG_MAP_TBL_DEF : (중요) ## UV_MIG_MAP_TBL_SUBSET : (중요) === MIG 레파지토리 테이블/컬럼 정보 일괄 갱신 프로시져 === <source lang=sql> CREATE OR REPLACE PROCEDURE MIGCOMMON.SP_TOBE_TAB_REFRESH /* MIG_TOBE_TABLE 갱신 프로시져 BY CYKIM (2020/11/03) * -- 0. 사용법 * EXEC SP_TOBE_TAB_REFRESH(); * * -- 1.오너 전체 테이블 리플레쉬 * EXEC SP_TOBE_TAB_REFRESH(IN_OWNER=>'TBA', IN_TABLE_NAME=>NULL); * * -- 2.오너별, 개별테이블 리플레쉬 * EXEC SP_TOBE_TAB_REFRESH(IN_OWNER=>'오너명', IN_TABLE_NAME=>'TABLE명'); * */ ( IN_OWNER IN VARCHAR2 DEFAULT NULL, IN_TABLE_NAME IN VARCHAR2 DEFAULT NULL ) AUTHID CURRENT_USER IS CURSOR REFRESH_OBJECT IS SELECT A.OWNER,A.TABLE_NAME FROM ALL_TABLES A WHERE A.OWNER = IN_OWNER AND A.TABLE_NAME=IN_TABLE_NAME ; V_D_SQL VARCHAR2(200); V_SQL VARCHAR2(32767); V_OWNER VARCHAR2(100); V_TABLE_NAME VARCHAR2(100); V_MSG long; BEGIN IF IN_OWNER IS NULL THEN DBMS_OUTPUT.ENABLE(100); DBMS_OUTPUT.PUT_LINE('usage: '); DBMS_OUTPUT.PUT_LINE('SP_TOBE_TAB_REFRESH(IN_OWNER => '''' , IN_TABLE_NAME => ''''); '); RETURN; END IF; DBMS_OUTPUT.ENABLE(32767); IF V_TABLE_NAME IS NOT NULL THEN FOR V_ROW IN REFRESH_OBJECT LOOP BEGIN V_OWNER:= V_ROW.OWNER; V_TABLE_NAME:= V_ROW.TABLE_NAME; DBMS_OUTPUT.ENABLE(32767); IF V_TABLE_NAME IS NOT NULL THEN -- OWNER.개별 테이블 갱신 처리 V_D_SQL :='DELETE FROM MIGCOMMON.TB_MIG_COL_DIC_TOBE WHERE OWNER='''||V_ROW.OWNER||''' AND TABLE_NAME='''||V_ROW.TABLE_NAME||''''; V_SQL :='INSERT INTO MIGCOMMON.TB_MIG_COL_DIC_TOBE (OWNER,TABLE_NAME,LOGI_NAME,COLUMN_ID,COLUMN_NAME,ATTR_NAME,DATA_TYPE,CONV_COL_LEN,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,KEY,COL_CRT_TYPE) SELECT OWNER,TABLE_NAME,LOGI_NAME,COLUMN_ID,COLUMN_NAME,ATTR_NAME,DATA_TYPE,NULL AS CONV_COL_LEN,NULL AS DATA_LENGTH,NULL AS DATA_PRECISION,NULL AS DATA_SCALE,NULL AS NULLABLE,NULL AS KEY,''가상컬럼'' AS COL_CRT_TYPE FROM TB_MIG_COL_DIC_TOBE_DUMMY WHERE OWNER = '''||V_ROW.OWNER||''' AND TABLE_NAME = '''||V_ROW.TABLE_NAME||''' UNION ALL SELECT TB.OWNER, TB.TABLE_NAME, SUBSTR (LOGI.COMMENTS, 1, 40) AS LOGI_NAME, COL.COLUMN_ID, COL.COLUMN_NAME, SUBSTR (ATTR.COMMENTS, 1, 40) AS ATTR_NAME, COL.DATA_TYPE, CASE WHEN COL.DATA_TYPE IN (''VARCHAR2'', ''CHAR'') THEN TO_CHAR (COL.DATA_LENGTH) WHEN COL.DATA_TYPE IN (''NUMBER'') AND COL.DATA_PRECISION IS NOT NULL THEN TO_CHAR (COL.DATA_PRECISION) || DECODE (COL.DATA_SCALE, ''0'', '''', '''','''' || COL.DATA_SCALE) ELSE '''' END AS CONV_COL_LEN, COL.DATA_LENGTH, COL.DATA_PRECISION, COL.DATA_SCALE, COL.NULLABLE, CON.KEY, ''물리컬럼'' AS COL_CRT_TYPE FROM MIGCOMMON.ALL_TABLES TB INNER JOIN MIGCOMMON.ALL_TAB_COLUMNS COL ON TB.OWNER = COL.OWNER AND TB.TABLE_NAME = COL.TABLE_NAME LEFT OUTER JOIN ( SELECT CCL.OWNER, CCL.TABLE_NAME, COLUMN_NAME, CASE WHEN SUM (DECODE (CONSTRAINT_TYPE, ''P'', 1, 0)) > 0 AND SUM (DECODE (CONSTRAINT_TYPE, ''R'', 1, 0)) > 0 THEN ''PK,FK'' WHEN SUM (DECODE (CONSTRAINT_TYPE, ''P'', 1, 0)) > 0 THEN ''PK'' WHEN SUM (DECODE (CONSTRAINT_TYPE, ''R'', 1, 0)) > 0 THEN ''FK'' ELSE '''' END AS KEY FROM MIGCOMMON.ALL_CONS_COLUMNS CCL, MIGCOMMON.ALL_CONSTRAINTS CNS WHERE CCL.CONSTRAINT_NAME = CNS.CONSTRAINT_NAME GROUP BY CCL.OWNER, CCL.TABLE_NAME, COLUMN_NAME) CON ON TB.OWNER = CON.OWNER AND TB.TABLE_NAME = CON.TABLE_NAME AND COL.COLUMN_NAME = CON.COLUMN_NAME LEFT OUTER JOIN MIGCOMMON.ALL_COL_COMMENTS ATTR ON ATTR.OWNER = TB.OWNER AND ATTR.TABLE_NAME = TB.TABLE_NAME AND ATTR.COLUMN_NAME = COL.COLUMN_NAME LEFT OUTER JOIN MIGCOMMON.ALL_TAB_COMMENTS LOGI ON LOGI.OWNER = TB.OWNER AND LOGI.TABLE_NAME = TB.TABLE_NAME WHERE (TB.OWNER IN (SELECT DB_OWNER_NM FROM (SELECT DB_OWNER_NM FROM MIGCOMMON.TB_MIG_CONFIG_DBOWNER WHERE DB_TARGET_CD = ''2'' UNION ALL SELECT ''MIGCOMMON'' AS DB_OWNER_NM FROM DUAL UNION ALL SELECT ''MIGBASEDATA'' AS DB_OWNER_NM FROM DUAL))) AND TB.OWNER = '''||V_ROW.OWNER||''' AND TB.TABLE_NAME = '''||V_ROW.TABLE_NAME||''''; -- DBMS_OUTPUT.PUT_LINE('DEL_SQL:'||V_D_SQL); EXECUTE IMMEDIATE V_D_SQL; -- DBMS_OUTPUT.PUT_LINE('INS_SQL:'||V_SQL); EXECUTE IMMEDIATE V_SQL; END IF; EXCEPTION WHEN OTHERS THEN V_MSG := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500); DBMS_OUTPUT.PUT_LINE('ERR_SQL:'||V_SQL); END; END LOOP; ELSE -- 해당 오너의 전체 테이블 REFRESH V_D_SQL :='DELETE FROM MIGCOMMON.TB_MIG_COL_DIC_TOBE WHERE OWNER='''||IN_OWNER||''''; V_SQL :='INSERT INTO MIGCOMMON.TB_MIG_COL_DIC_TOBE (OWNER,TABLE_NAME,LOGI_NAME,COLUMN_ID,COLUMN_NAME,ATTR_NAME,DATA_TYPE,CONV_COL_LEN,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,KEY,COL_CRT_TYPE) SELECT OWNER,TABLE_NAME,LOGI_NAME,COLUMN_ID,COLUMN_NAME,ATTR_NAME,DATA_TYPE,NULL AS CONV_COL_LEN,NULL AS DATA_LENGTH,NULL AS DATA_PRECISION,NULL AS DATA_SCALE,NULL AS NULLABLE,NULL AS KEY,''가상컬럼'' AS COL_CRT_TYPE FROM TB_MIG_COL_DIC_TOBE_DUMMY WHERE OWNER = '''||IN_OWNER||''' UNION ALL SELECT TB.OWNER, TB.TABLE_NAME, SUBSTR (LOGI.COMMENTS, 1, 40) AS LOGI_NAME, COL.COLUMN_ID, COL.COLUMN_NAME, SUBSTR (ATTR.COMMENTS, 1, 40) AS ATTR_NAME, COL.DATA_TYPE, CASE WHEN COL.DATA_TYPE IN (''VARCHAR2'', ''CHAR'') THEN TO_CHAR (COL.DATA_LENGTH) WHEN COL.DATA_TYPE IN (''NUMBER'') AND COL.DATA_PRECISION IS NOT NULL THEN TO_CHAR (COL.DATA_PRECISION) || DECODE (COL.DATA_SCALE, ''0'', '''', '''','''' || COL.DATA_SCALE) ELSE '''' END AS CONV_COL_LEN, COL.DATA_LENGTH, COL.DATA_PRECISION, COL.DATA_SCALE, COL.NULLABLE, CON.KEY, ''물리컬럼'' AS COL_CRT_TYPE FROM MIGCOMMON.ALL_TABLES TB INNER JOIN MIGCOMMON.ALL_TAB_COLUMNS COL ON TB.OWNER = COL.OWNER AND TB.TABLE_NAME = COL.TABLE_NAME LEFT OUTER JOIN ( SELECT CCL.OWNER, CCL.TABLE_NAME, COLUMN_NAME, CASE WHEN SUM (DECODE (CONSTRAINT_TYPE, ''P'', 1, 0)) > 0 AND SUM (DECODE (CONSTRAINT_TYPE, ''R'', 1, 0)) > 0 THEN ''PK,FK'' WHEN SUM (DECODE (CONSTRAINT_TYPE, ''P'', 1, 0)) > 0 THEN ''PK'' WHEN SUM (DECODE (CONSTRAINT_TYPE, ''R'', 1, 0)) > 0 THEN ''FK'' ELSE '''' END AS KEY FROM MIGCOMMON.ALL_CONS_COLUMNS CCL, MIGCOMMON.ALL_CONSTRAINTS CNS WHERE CCL.CONSTRAINT_NAME = CNS.CONSTRAINT_NAME GROUP BY CCL.OWNER, CCL.TABLE_NAME, COLUMN_NAME) CON ON TB.OWNER = CON.OWNER AND TB.TABLE_NAME = CON.TABLE_NAME AND COL.COLUMN_NAME = CON.COLUMN_NAME LEFT OUTER JOIN MIGCOMMON.ALL_COL_COMMENTS ATTR ON ATTR.OWNER = TB.OWNER AND ATTR.TABLE_NAME = TB.TABLE_NAME AND ATTR.COLUMN_NAME = COL.COLUMN_NAME LEFT OUTER JOIN MIGCOMMON.ALL_TAB_COMMENTS LOGI ON LOGI.OWNER = TB.OWNER AND LOGI.TABLE_NAME = TB.TABLE_NAME WHERE (TB.OWNER IN (SELECT DB_OWNER_NM FROM (SELECT DB_OWNER_NM FROM MIGCOMMON.TB_MIG_CONFIG_DBOWNER WHERE DB_TARGET_CD = ''2'' UNION ALL SELECT ''MIGCOMMON'' AS DB_OWNER_NM FROM DUAL UNION ALL SELECT ''MIGBASEDATA'' AS DB_OWNER_NM FROM DUAL))) AND TB.OWNER = '''||IN_OWNER||''''; -- DBMS_OUTPUT.PUT_LINE('DEL_SQL:'||V_D_SQL); EXECUTE IMMEDIATE V_D_SQL; -- DBMS_OUTPUT.PUT_LINE('INS_SQL:'||V_SQL); EXECUTE IMMEDIATE V_SQL; -- LOG 기록 -- INSERT INTO ZB_MGR_TAB_REFRESH_LOG -- (OWNER , TABLE_NAME) -- VALUES (V_ROW.OWNER,V_ROW.TABLE_NAME); COMMIT; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM); END; / </source> === 테이블명 변경에 따른 MIG 레파지토리 일괄변경 프로시져 === <source lang=sql> CREATE OR REPLACE PROCEDURE MIGCOMMON.SP_TOBE_TAB_RENAME /* MIG_TOBE_TABLE 갱신 프로시져 BY CYKIM (2020/11/03) * -- 0. 사용법 * EXEC SP_TOBE_TAB_RENAME('오너명','OLD_TABLE명','NEW_TABLE명')); * * -- 1.오너, 테이블 RENAME * EXEC SP_TOBE_TAB_RENAME(IN_OWNER=>'오너명', IN_OLD_TABLE=>'OLD_TABLE명', IN_NEW_TABLE=>'NEW_TABLE명'); * */ ( IN_OWNER IN VARCHAR2 DEFAULT NULL, IN_OLD_TABLE IN VARCHAR2 DEFAULT NULL, IN_NEW_TABLE IN VARCHAR2 DEFAULT NULL ) --AUTHID CURRENT_USER IS PRAGMA AUTONOMOUS_TRANSACTION; -- CURSOR RENAME_OBJECT IS -- SELECT A.OWNER,A.TABLE_NAME -- FROM ALL_TABLES A -- WHERE A.OWNER = IN_OWNER -- AND A.TABLE_NAME=IN_OLD_TABLE -- ; V_1_SQL VARCHAR2(2000); V_2_SQL VARCHAR2(2000); V_FK1_SQL1 VARCHAR2(2000); V_FK1_SQL2 VARCHAR2(2000); V_FK1_SQL3 VARCHAR2(2000); V_FK2_SQL1 VARCHAR2(2000); V_FK2_SQL2 VARCHAR2(2000); V_FK2_SQL3 VARCHAR2(2000); -- V_OWNER VARCHAR2(100); -- V_TABLE_NAME VARCHAR2(100); V_MSG long; BEGIN IF IN_OWNER IS NULL THEN DBMS_OUTPUT.ENABLE(200); DBMS_OUTPUT.PUT_LINE('usage: '); DBMS_OUTPUT.PUT_LINE('SP_TOBE_TAB_RENAME(IN_OWNER => '''' , IN_OLD_TABLE => '''', IN_NEW_TABLE => ''''); '); RETURN; END IF; DBMS_OUTPUT.ENABLE(32767); BEGIN -- V_OWNER:= IN_OWNER; -- V_TABLE_NAME:= IN_OLD_TABLE; -- V_TABLE_NAME2:= IN_NEW_TABLE; DBMS_OUTPUT.ENABLE(32767); IF IN_NEW_TABLE IS NOT NULL THEN -- 1-1.F/K 비활성화 V_FK1_SQL1 := 'ALTER TABLE TB_MIG_MAP_TBL_SUBSET DISABLE CONSTRAINTS FK_MIG_MAP_TBL_SUBSET_01'; V_FK1_SQL2 := 'ALTER TABLE TB_MIG_MAP_TBL_USER DISABLE CONSTRAINTS FK_TB_MIG_MAP_TBL_USER_01'; V_FK1_SQL3 := 'ALTER TABLE TB_MIG_MAP_TBL_SCDUL DISABLE CONSTRAINTS FK_TB_MIG_MAP_TBL_SCDUL_01'; -- 1-2.F/K 활성화 V_FK2_SQL1 := 'ALTER TABLE TB_MIG_MAP_TBL_SUBSET ENABLE CONSTRAINTS FK_MIG_MAP_TBL_SUBSET_01'; V_FK2_SQL2 := 'ALTER TABLE TB_MIG_MAP_TBL_USER ENABLE CONSTRAINTS FK_TB_MIG_MAP_TBL_USER_01'; V_FK2_SQL3 := 'ALTER TABLE TB_MIG_MAP_TBL_SCDUL ENABLE CONSTRAINTS FK_TB_MIG_MAP_TBL_SCDUL_01'; -- DBMS_OUTPUT.PUT_LINE('V_FK1_SQL:'||V_FK1_SQL); EXECUTE IMMEDIATE V_FK1_SQL1; EXECUTE IMMEDIATE V_FK1_SQL2; EXECUTE IMMEDIATE V_FK1_SQL3; -- 매핑테이블 UPDATE 처리 UPDATE TB_MIG_MAP_TBL A SET TOBE_TBL_NM=IN_NEW_TABLE WHERE TOBE_TBL_NM=IN_OLD_TABLE; -- 매핑테이블 이력 UPDATE 처리 UPDATE TB_MIG_MAP_TBL_HST A SET TOBE_TBL_NM=IN_NEW_TABLE WHERE TOBE_TBL_NM=IN_OLD_TABLE; -- 매핑컬럼 UPDATE 처리 UPDATE TB_MIG_MAP_COL A SET A.TOBE_TBL_NM=IN_NEW_TABLE WHERE A.TOBE_TBL_NM=IN_OLD_TABLE; -- 매핑테이블 이력 UPDATE 처리 UPDATE TB_MIG_MAP_COL_HST A SET A.TOBE_TBL_NM=IN_NEW_TABLE WHERE A.TOBE_TBL_NM=IN_OLD_TABLE; -- 매핑테이블 상세 매핑정보 UPDATE 처리 UPDATE TB_MIG_MAP_TBL_SUBSET A SET TOBE_TBL_NM=IN_NEW_TABLE WHERE TOBE_TBL_NM=IN_OLD_TABLE; -- 매핑테이블 상세 매핑정보 이력 UPDATE 처리 UPDATE TB_MIG_MAP_TBL_SUBSET_HST A SET TOBE_TBL_NM=IN_NEW_TABLE WHERE TOBE_TBL_NM=IN_OLD_TABLE; -- 매핑 테이블 담당자 UPDATE UPDATE TB_MIG_MAP_TBL_USER A SET TOBE_TBL_NM=IN_NEW_TABLE WHERE TOBE_TBL_NM=IN_OLD_TABLE; -- 매핑 테이블 담당자 스케줄 UPDATE UPDATE TB_MIG_MAP_TBL_SCDUL A SET TOBE_TBL_NM=IN_NEW_TABLE WHERE TOBE_TBL_NM=IN_OLD_TABLE; -- F/K ENABLE -- DBMS_OUTPUT.PUT_LINE('INS_SQL:'||V_FK1_SQL); COMMIT; EXECUTE IMMEDIATE V_FK2_SQL1; EXECUTE IMMEDIATE V_FK2_SQL2; EXECUTE IMMEDIATE V_FK2_SQL3; END IF; EXCEPTION WHEN OTHERS THEN V_MSG := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500); -- DBMS_OUTPUT.PUT_LINE('ERR_SQL:'||V_SQL); COMMIT; END; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM); END; / </source> === 전환매핑 관리 테이블 === <source lang=sql> -- DROP TABLE MIGCOMMON."TB_매핑관리" CASCADE CONSTRAINTS; CREATE TABLE MIGCOMMON."TB_매핑관리" ( NO NUMBER(10) NOT NULL, TGT_OWNER VARCHAR2(200 BYTE), "TGT테이블명_영문" VARCHAR2(200 BYTE), "TGT엔터티명_한글" VARCHAR2(200 BYTE) NOT NULL, UNION_ALL VARCHAR2(200 BYTE), SRC_OWNER VARCHAR2(200 BYTE), "SRC테이블명_영문" VARCHAR2(200 BYTE), "SRC엔터티명_한글" VARCHAR2(200 BYTE), "ASIS매핑" VARCHAR2(200 BYTE), "엑셀업로드" VARCHAR2(200 BYTE), "ASIS재집계" VARCHAR2(200 BYTE), "중간임시테이블" VARCHAR2(200 BYTE), "초기적재" VARCHAR2(200 BYTE), "부연설명" VARCHAR2(200 BYTE), "전환방식_부연설명" VARCHAR2(200 BYTE), "클린징여부" VARCHAR2(200 BYTE), "클린징부연설명" VARCHAR2(200 BYTE), "사전전환여부" VARCHAR2(200 BYTE), "사전전환후_미변경" VARCHAR2(200 BYTE), "사전전환기준컬럼" VARCHAR2(200 BYTE), "사전전환기준컬럼포맷" VARCHAR2(200 BYTE), "선오픈대상여부" VARCHAR2(200 BYTE), "선오픈후_본전환시_제외" VARCHAR2(200 BYTE), ACCESS_PATH VARCHAR2(200 BYTE), JOIN_ON VARCHAR2(200 BYTE), "WHERE절" VARCHAR2(200 BYTE), SQL_WITH_CTE VARCHAR2(200 BYTE), "담당자" VARCHAR2(200 BYTE), "주제영역" VARCHAR2(200 BYTE), "작업상태" VARCHAR2(200 BYTE), "건수" VARCHAR2(200 BYTE), "사이즈" VARCHAR2(200 BYTE), "전환대상여부" VARCHAR2(200 BYTE), "수정자" VARCHAR2(200 BYTE), "비고내용" VARCHAR2(500 BYTE), "파티셔닝_컬럼" VARCHAR2(20 BYTE) ) TABLESPACE TS_MIGCOMMON_D01 PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) LOGGING NOCOMPRESS NOCACHE MONITORING; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리".NO IS '순번'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리".TGT_OWNER IS 'TGT_OWNER'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."TGT테이블명_영문" IS 'TGT테이블명_영문'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."TGT엔터티명_한글" IS 'TGT엔터티명_한글'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리".UNION_ALL IS 'UNION_ALL'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리".SRC_OWNER IS 'SRC_OWNER'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."SRC테이블명_영문" IS 'SRC테이블명_영문'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."SRC엔터티명_한글" IS 'SRC_엔터티명_한글'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."ASIS매핑" IS 'ASIS매핑'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."엑셀업로드" IS '엑셀업로드'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."ASIS재집계" IS 'ASIS재집계'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."중간임시테이블" IS '중간임시테이블'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."초기적재" IS '초기적재'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."부연설명" IS '부연설명'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."전환방식_부연설명" IS '전환방식_부연설명'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."클린징여부" IS '클린징여부'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."클린징부연설명" IS '클린징부연설명'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."사전전환여부" IS '사전전환여부'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."사전전환후_미변경" IS '사전전환후 미변경'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."사전전환기준컬럼" IS '사전전환기준컬럼'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."사전전환기준컬럼포맷" IS '사전전환기준컬럼포맷'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."선오픈대상여부" IS '선오픈대상여부'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."선오픈후_본전환시_제외" IS '선오픈후 본전환시제외'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리".ACCESS_PATH IS 'Access Path'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리".JOIN_ON IS 'JOIN_ON'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."WHERE절" IS 'WHERE'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리".SQL_WITH_CTE IS 'SQL WITH_CTE'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."담당자" IS '담당자'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."주제영역" IS '주제영역'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."작업상태" IS '작업상태'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."건수" IS '건수'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."사이즈" IS '사이즈'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."전환대상여부" IS '전환대상여부'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."수정자" IS '수정자'; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리"."비고내용" IS '비고내용'; CREATE UNIQUE INDEX MIGCOMMON."PK_매핑관리" ON MIGCOMMON."TB_매핑관리" (NO) LOGGING TABLESPACE TS_MIGCOMMON_D01 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ); CREATE OR REPLACE TRIGGER MIGCOMMON.TG_MIG_MAP_MGR_01 AFTER INSERT OR UPDATE OR DELETE ON MIGCOMMON."TB_매핑관리" FOR EACH ROW DECLARE VAR_HST_GUBUN VARCHAR2(100); BEGIN IF INSERTING THEN VAR_HST_GUBUN := '추가'; ELSIF UPDATING THEN VAR_HST_GUBUN := '수정'; ELSIF DELETING THEN VAR_HST_GUBUN := '삭제'; END IF; IF VAR_HST_GUBUN = '추가' THEN /* 제외하면 ERROR 발생 */ RETURN; END IF; INSERT INTO MIGCOMMON."TB_매핑관리_이력" ( MAP_TBL_MGR_HST_IDX , CHANGE_DT , HST_GUBUN ---------- ,"NO" ,"TGT_OWNER" ,"TGT테이블명_영문" ,"TGT엔터티명_한글" ,"UNION_ALL" ,"SRC_OWNER" ,"SRC테이블명_영문" ,"SRC_엔터티명_한글" ,"ASIS매핑" ,"엑셀업로드" ,"ASIS재집계" ,"중간임시테이블" ,"초기적재" ,"부연설명" ,"전환방식_부연설명" ,"클린징여부" ,"클린징부연설명" ,"사전전환여부" ,"사전전환후_미변경" ,"사전전환기준컬럼" ,"사전전환기준컬럼포맷" ,"선오픈대상여부" ,"선오픈후_본전환시_제외" ,"ACCESS_PATH" ,"JOIN_ON" ,"WHERE절" ,"SQL_WITH_CTE" ,"담당자" ,"주제영역" ,"작업상태" ,"건수" ,"사이즈" ,"전환대상여부" ,"수정자" ,"비고내용" ,"구분" ) VALUES ( MIGCOMMON.SQ_MIG_MAP_MGR_HST.NEXTVAL , SYSDATE , VAR_HST_GUBUN -- ------------------------ ,:OLD."NO" ,:OLD."TGT_OWNER" ,:OLD."TGT테이블명_영문" ,:OLD."TGT엔터티명_한글" ,:OLD."UNION_ALL" ,:OLD."SRC_OWNER" ,:OLD."SRC테이블명_영문" ,:"OLD.SRC_엔터티명_한글" ,:OLD."ASIS매핑" ,:OLD."엑셀업로드" ,:OLD."ASIS재집계" ,:OLD."중간임시테이블" ,:OLD."초기적재" ,:OLD."부연설명" ,:OLD."전환방식_부연설명" ,:OLD."클린징여부" ,:OLD."클린징부연설명" ,:OLD."사전전환여부" ,:OLD."사전전환후_미변경" ,:OLD."사전전환기준컬럼" ,:OLD."사전전환기준컬럼포맷" ,:OLD."선오픈대상여부" ,:OLD."선오픈후_본전환시_제외" ,:OLD."ACCESS_PATH" ,:OLD."JOIN_ON" ,:OLD."WHERE절" ,:OLD."SQL_WITH_CTE" ,:OLD."담당자" ,:OLD."주제영역" ,:OLD."작업상태" ,:OLD."건수" ,:OLD."사이즈" ,:OLD."전환대상여부" ,:OLD."수정자" ,:OLD."비고내용" ,:"OLD.구분" ); END; / ALTER TABLE MIGCOMMON."TB_매핑관리" ADD ( CONSTRAINT "PK_매핑관리" PRIMARY KEY (NO) USING INDEX MIGCOMMON."PK_매핑관리" ENABLE VALIDATE); </source> ==== 전환매핑 관리 이력 테이블 ==== <source lang=sql> -- DROP TABLE MIGCOMMON."TB_매핑관리_이력" CASCADE CONSTRAINTS; CREATE TABLE MIGCOMMON."TB_매핑관리_이력" ( NO NUMBER(10) NOT NULL, TGT_OWNER VARCHAR2(200 BYTE), "TGT테이블명_영문" VARCHAR2(200 BYTE), "TGT엔터티명_한글" VARCHAR2(200 BYTE) NOT NULL, UNION_ALL VARCHAR2(200 BYTE), SRC_OWNER VARCHAR2(200 BYTE), "SRC테이블명_영문" VARCHAR2(200 BYTE), "SRC_엔터티명_한글" VARCHAR2(200 BYTE), "ASIS매핑" VARCHAR2(200 BYTE), "엑셀업로드" VARCHAR2(200 BYTE), "ASIS재집계" VARCHAR2(200 BYTE), "중간임시테이블" VARCHAR2(200 BYTE), "초기적재" VARCHAR2(200 BYTE), "부연설명" VARCHAR2(200 BYTE), "전환방식_부연설명" VARCHAR2(200 BYTE), "클린징여부" VARCHAR2(200 BYTE), "클린징부연설명" VARCHAR2(200 BYTE), "사전전환여부" VARCHAR2(200 BYTE), "사전전환후_미변경" VARCHAR2(200 BYTE), "사전전환기준컬럼" VARCHAR2(200 BYTE), "사전전환기준컬럼포맷" VARCHAR2(200 BYTE), "선오픈대상여부" VARCHAR2(200 BYTE), "선오픈후_본전환시_제외" VARCHAR2(200 BYTE), ACCESS_PATH VARCHAR2(200 BYTE), JOIN_ON VARCHAR2(200 BYTE), "WHERE절" VARCHAR2(200 BYTE), SQL_WITH_CTE VARCHAR2(200 BYTE), "담당자" VARCHAR2(200 BYTE), "주제영역" VARCHAR2(200 BYTE), "작업상태" VARCHAR2(200 BYTE), "건수" VARCHAR2(200 BYTE), "사이즈" VARCHAR2(200 BYTE), "전환대상여부" VARCHAR2(200 BYTE), "수정자" VARCHAR2(200 BYTE), "비고내용" VARCHAR2(500 BYTE), "구분" VARCHAR2(100 BYTE), MAP_TBL_MGR_HST_IDX NUMBER, CHANGE_DT DATE, HST_GUBUN VARCHAR2(20 BYTE) ) TABLESPACE TS_MIGCOMMON_D01 PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) LOGGING NOCOMPRESS NOCACHE MONITORING; COMMENT ON COLUMN MIGCOMMON."TB_매핑관리_이력"."구분" IS '구분'; </source>
MS MIG 전환솔루션
문서로 돌아갑니다.