다른 명령
MS MIG 전환검증룰 입력 프로시져 편집하기
_TOC_
전환 검증룰 입력 프로시져
CREATE OR REPLACE PROCEDURE MIGCOMMON.SP_TB_MIG_MAP_VERIFY_BIZ_DATE ( IN_V_TOBE_OWNER IN VARCHAR2 , IN_V_TOBE_TBL_NM IN VARCHAR2 , IN_V_TOBE_COL_NM IN VARCHAR2 , IN_V_PARAM IN VARCHAR2 DEFAULT 'YYYYMMDD' , IN_V_EXEC IN NUMBER DEFAULT 0 -- 실행여부 ) IS -- ============================================================================ -- 2020.12.22 김치영 일자 도메인 검증 규칙 추가 'YMD' BIZRULE INSERT -- 0. 사용법 -- EXEC SP_TB_MIG_MAP_VERIFY_BIZ_DATE('오너명','TABLE명','도메인(YMD)'[,'0'])); -- 2021.01.19 최혜영 (SELECT FN_ISDATE(..) FROM DUAL)로 변경 -- ============================================================================ -- V_CHK_CNT NUMBER := 0; -- V_SQL VARCHAR2(2000); V_MSG LONG; --해당 테이블의 컬럼 매핑정보를 SELECT CURSOR C1 IS SELECT DISTINCT A.TOBE_OWNER , A.TOBE_TBL_NM , 'SELECT /*+ PARALLEL(16) */ COUNT(1) FROM '||CHR(13)||CHR(10) ||' '||A.TOBE_OWNER||'.'||A.TOBE_TBL_NM||' WHERE (SELECT FN_ISDATE('||A.TOBE_COL_NM||','''||IN_V_PARAM||''') FROM DUAL) = 1 AND ' ||A.TOBE_COL_NM||' IS NOT NULL' ||' AND ROWNUM <= 1' AS SQL_BIZRULE , A.TOBE_ATTR_NM||'('||A.TOBE_COL_NM||') 검증_전환팀 최초입력' AS BIZRULE_DESC FROM UV_MIG_TOBE_COL_MAP_DEF A LEFT JOIN UV_MIG_MAP_TBL X ON X.TOBE_OWNER = A.TOBE_OWNER AND X.TOBE_TBL_NM = A.TOBE_TBL_NM AND X.TRANS_FLAG = 'Y' WHERE 1=1 AND A.TOBE_OWNER = IN_V_TOBE_OWNER AND A.TOBE_TBL_NM = IN_V_TOBE_TBL_NM AND A.TOBE_COL_NM LIKE '%_'||IN_V_TOBE_COL_NM||'' ; V_CHK_CNT NUMBER DEFAULT 0; V_TOBE_OWNER VARCHAR2(100) DEFAULT NULL; V_TOBE_TBL_NM VARCHAR2(100) DEFAULT NULL; V_SQL_BIZRULE VARCHAR2(400) DEFAULT NULL; V_BIZRULE_DESC VARCHAR2(400) DEFAULT NULL; V_RULE_REG_USER_NO NUMBER DEFAULT 3; BEGIN FOR V_ROW IN C1 LOOP BEGIN V_CHK_CNT := 0; V_TOBE_OWNER := V_ROW.TOBE_OWNER; V_TOBE_TBL_NM := V_ROW.TOBE_TBL_NM; V_SQL_BIZRULE := V_ROW.SQL_BIZRULE; V_BIZRULE_DESC:= V_ROW.BIZRULE_DESC; V_RULE_REG_USER_NO:= 3; -- 김치영 3, 최혜영 6 IF IN_V_EXEC <> 1 THEN BEGIN DBMS_OUTPUT.ENABLE; -- 다음 순번 채번 SELECT MAX(MIG_VERIFY_BIZRULE_NO)+1 INTO V_CHK_CNT FROM TB_MIG_VERIFY_BIZRULE; IF V_CHK_CNT > 0 THEN INSERT INTO TB_MIG_VERIFY_BIZRULE ( MIG_VERIFY_BIZRULE_NO , TOBE_OWNER , TOBE_TBL_NM , SQL_BIZRULE , BIZRULE_DESC , ADD_DESC , USE_FLG , RULE_OPTION_CD , VERIFY_TIMING_CD , RULE_REG_USER_NO ) VALUES ( V_CHK_CNT , V_ROW.TOBE_OWNER , V_ROW.TOBE_TBL_NM , V_ROW.SQL_BIZRULE , V_ROW.BIZRULE_DESC , V_ROW.BIZRULE_DESC , 'Y' , '0' , '2' , V_RULE_REG_USER_NO ) ; END IF; -- ----------------------------------------------------------------------------- -- COMMIT -- ----------------------------------------------------------------------------- COMMIT; -- ----------------------------------------------------------------------------- -- 99. ERROR LOG -- ----------------------------------------------------------------------------- EXCEPTION WHEN OTHERS THEN ROLLBACK; -- DBMS_OUTPUT.PUT_LINE(SQLERRM || 'INSERT ERROR'); -- Database Error 저장 -- SP_ERRORLOG ('SP_TB_ARAA001','TB_ARAA001','INSERT',SQLERRM,''); -- MIGCOMMON.PKG_MIG_LOG.SP_LOG_PROGRAM_EXEC_DESC(V_PROGRAM_EXEC_DESC); -- MIGCOMMON.PKG_MIG_LOG.SP_LOG_END('END', SQLCODE, SQLERRM); -- RAISE; END; ELSE V_TOBE_OWNER := V_ROW.TOBE_OWNER; V_TOBE_TBL_NM := V_ROW.TOBE_TBL_NM; V_SQL_BIZRULE := V_ROW.SQL_BIZRULE; V_BIZRULE_DESC:= V_ROW.BIZRULE_DESC; V_RULE_REG_USER_NO:= 3; -- 김치영 3, 최혜영 6 -- EXEC SP_TOBE_TAB_RENAME('오너명','TABLE명','YMD'[,'0'])); V_MSG := 'OUTPUT : [ SQL ] => '||V_SQL_BIZRULE; dbms_output.enable(2000); dbms_output.put_line('USAGE: EXEC SP_TB_MIG_MAP_VERIFY_BIZ_DATE(''OWNER'',''TABLE'',''도메인(YMD,YM)''[,''YYYYMMDD''][,''0''])); '); dbms_output.put_line(V_MSG); END IF; END; END LOOP; dbms_output.enable(2000); dbms_output.put_line('................END STRART2'); V_MSG := 'OUTPUT : [ SQL ] => '||V_SQL_BIZRULE; dbms_output.put_line(V_MSG); END; /
데이터 전환 테스트
- TEST
MS MIG Tools
- MS is not Microsoft