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;
/
MS MIG 전환검증룰 입력 프로시져

DB CAFE
