문자(char, varchar)로 설계한 날짜데이타 검증하기

○ 날짜 컬럼을 문자(char, varchar)로 설계
  문자로 설계한 날짜 컬럼에서 날짜형식으로 변경하면 쿼리 에러가 발생 함.  (예 : 20120132, 20110a03 등)

 

○ 해결방법
  - 데이터 입력시점에 정확한 날짜 데이터만 들어가도록 함
  - 기존의 오류 데이터는 사용자정의 함수를 작성하여 데이터를 찾고, 변환 함

○ 테스트 테이블생성
   - create table tmp_test(tmp_date varchar(8));

 

○ 테스트 데이터 
insert into tmp_test VALUES('20120108');
insert into tmp_test VALUES('20120109');
insert into tmp_test VALUES('20120110');
insert into tmp_test VALUES('20120111');
insert into tmp_test VALUES('20120112');
insert into tmp_test VALUES('20120113');
insert into tmp_test VALUES('20120114');
insert into tmp_test VALUES('20120115');
insert into tmp_test VALUES('20120132');

○ 오류 확인(날짜 관련 함수 사용 불가), 어떤 데이타가 오류이지 확인불가
SELECT to_date(tmp_date,'yyyymmdd')+1  from tmp_test;

○ 사용자정의 함수 작성(isdate.java)
import java.sql.*;

public class isdate{
    public static String chkDate(String strDate, String strDateFormat) throws SQLException {

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try{
            Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
            conn = DriverManager.getConnection("jdbc:CUBRID:localhost:33000:demodb:::","","");
           
            String sql="SELECT to_date('" + strDate + "', '" + strDateFormat + "')" ;
           
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            rs.close();
            stmt.close();
            conn.close();
            return "YES";

        } catch ( SQLException e ) {
            if ( conn != null ) conn.close();
            return "NO";
        } catch ( Exception e ) {
            return "NO";
        } finally {
            if ( conn != null ) conn.close();
        }
    }
}

○ 자바 컴파일 및 등록
 - 자바컴파일 : Javac isdate.java
 - CUBRID에 등록 : loadjava demodb isdate.class

 

○ 사용자정의 펑션 생성
create function isdate( strDate varchar, strDateFormat varchar) return string
as language java
name 'isdate.chkDate(java.lang.String, java.lang.String) return java.lang.String';

 

○ 날짜검사 테스트
call isdate('20120131', 'YYYYMMDD') ;
call isdate('20120132', 'YYYYMMDD') ;

 

select isdate(tmp_date,'YYYYMMDD'),   decode(isdate(tmp_date,'YYYYMMDD'), 'YES', tmp_date, '') as err_chk,tmp_date 
from tmp_test ;