오라클 날짜 포맷 체크 함수

1. 날짜 컬럼의 타입이 DATE가 아닌 CHAR 또는 VARCHAR2 인 경우 어플리케이션 버그로 인해 정상적인
날짜값이 아닌 문자나 공백 등이 입력될 수 있어 향후 관리나 통계추출에 영향을 미칠 수 있습니다.

관련하여 기 입력된 날짜값이 정상적인 데이터인지 아니면 비정상적인 데이터인지 검증을 해야 하는데
아래와 같이 날짜체크 함수를 이용하면 쉽게 검증이 가능합니다.



 # 함수 소스

CREATE OR REPLACE FUNCTION FN_CHECK_DATE(str_date VARCHAR2) RETURN VARCHAR2
AS

RTN  VARCHAR(5) := 'TRUE';
DUMMY  DATE;
strTEMP VARCHAR(8);

BEGIN

 strTEMP := TRIM(str_date);

  
 IF strTEMP IS NOT NULL THEN

   SELECT TO_DATE(strTEMP)
   INTO DUMMY
   FROM DUAL;
        
     RETURN RTN;
       
 ELSE
   
     RTN := 'NULL';
     RETURN RTN;
                    
 END IF;

   
 EXCEPTION 
 WHEN OTHERS THEN RTN := 'FALSE';             
 RETURN RTN;
   
END;

# 샘플 예제

select FN_CHECK_DATE('20110923') COL1,
        FN_CHECK_DATE('20110000') COL2,
        FN_CHECK_DATE('문자') COL3,
        FN_CHECK_DATE('문자') COL4,
        FN_CHECK_DATE(NULL) COL5,               
        FN_CHECK_DATE('') COL6,
        FN_CHECK_DATE(' ') COL7
from dual; 


Col1   Col2      Col3     Col4     Col5    Col6   Col7
------------------------------------------------
TRUE  FALSE  FALSE  FALSE  NULL  NULL  NULL


 



2. 날짜 컬럼의 타입을 DATE 로 지정하는게 가장 쉬운 해결책이지만,
이미 CHAR 또는 VARCHAR2 를 방대하게 사용하고 있어 스키마 수정이 여의치 않을때 아래와 같은 방법을 추천합니다.

날짜체크 함수를 통해 기 입력된 데이터의 검증 및 보정을 하고 스키마 단에서 다음과 같이 제약조건을
추가하면 향후에 어플리케이션 또는 작업자 실수에 의해 비정상적인 날짜값이 입력되는 것을 사전에 방지할 수 있습니다.

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 CHECK
(컬럼명=to_char(to_date(컬럼명,'YYYYMMDD'),'YYYYMMDD'));