MS MIG 전환검증룰 입력 프로시져 편집하기
전환 검증룰 입력 프로시져
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