programing

SELECT 문에서 to_date 예외를 처리하여 해당 행을 무시하는 방법은 무엇입니까?

i4 2023. 7. 5. 20:05
반응형

SELECT 문에서 to_date 예외를 처리하여 해당 행을 무시하는 방법은 무엇입니까?

제가 작업 중인 수정 보고서에서 COMMAND로 사용하고자 하는 다음과 같은 질문이 있습니다.

SELECT * FROM myTable
WHERE to_date(myTable.sdate, 'MM/dd/yyyy') <= {?EndDate}

이것은 잘 작동하지만 (사용자 오류로 인해) 날짜가 항상 올바른 형식이 아닐 수도 있다는 것이 유일한 걱정입니다.to_date 함수가 실패하면 예외가 발생한다는 것을 알고 있습니다.내 SELECT 문의 해당 행을 무시하는 방식으로 이 예외를 처리할 수 있습니까?그렇지 않으면 전체 데이터베이스에서 하나의 날짜만 잘못 형식 지정하면 보고서가 손상되기 때문입니다.

오라클에서 isDate 기능을 제공하는지 알아봤는데, 당신이 예외를 처리해야 하는 것 같습니다.어떤 도움이라도 주시면 대단히 감사하겠습니다.감사합니다!!

Tony의 의견을 반영하듯, 프런트 엔드 쿼리 도구가 이러한 예외를 찾아 처리하도록 강요하는 것보다 날짜를 DATE 열에 저장하는 것이 훨씬 나을 것입니다.

그러나 잘못된 데이터 모델에 갇혀 있는 경우 이전 버전에서 가장 간단한 옵션은 변환을 수행하고 오류를 처리하는 함수를 만드는 것입니다.

CREATE OR REPLACE FUNCTION my_to_date( p_date_str IN VARCHAR2,
                              p_format_mask IN VARCHAR2 )
  RETURN DATE
IS
  l_date DATE;
BEGIN
  l_date := to_date( p_date_str, p_format_mask );
  RETURN l_date;
EXCEPTION
  WHEN others THEN
    RETURN null;
END my_to_date;

그러면 쿼리는 다음과 같습니다.

SELECT * 
  FROM myTable
 WHERE my_to_date(myTable.sdate, 'MM/dd/yyyy') <= {?EndDate}

물론, 당신은 아마도 기능 기반 인덱스를 원할 것입니다.MY_TO_DATE이 쿼리를 합리적으로 효율적으로 사용하기 위해 호출합니다.

은 12에 기능을 했습니다.2에서 오라클은 에 확장 기능을 추가했습니다.to_date그리고.cast가 발생하는 입니다.

SELECT * 
  FROM myTable
 WHERE to_date(myTable.sdate default null on conversion error, 'MM/dd/yyyy') <= {?EndDate}

당신은 또한 사용할 수 있습니다.validate_conversion유효한 날짜 또는 유효하지 않은 모든 행을 찾는 경우 기능합니다.

SELECT *
  FROM myTable 
 WHERE validate_conversion( myTable.sdate as date, 'MM/DD/YYYY' ) = 1

데이터가 일관되지 않고 문자열로 저장된 날짜가 유효하지 않을 경우 세 가지 옵션이 있습니다.

  1. 열에 날짜 데이터 유형이 저장되도록 DB 재팩터링
  2. 저장 프로시저에서 현재까지 문자열 예외 처리
  3. (복잡한) 레코드 선택 공식에서 현재까지 문자열 예외 처리

데이터가 일관되어야 하므로 첫 번째 옵션을 사용하는 것이 좋습니다.

두 번째 옵션은 보고서가 필요한 행만 가져오기 때문에 유연성과 속도를 제공합니다.

세 번째 옵션은 보고서가 테이블의 모든 레코드를 가져온 다음 보고서가 레코드를 필터링하도록 강제합니다.

저도 같은 문제가 있어요날짜와 수십 년 동안의 잘못된 데이터에 대한 막대 필드가 있는 오래된 레거시 데이터베이스제가 원하는 만큼, 데이터 유형도 변경할 수 없습니다.하지만 저는 날짜가 최신인지 확인하기 위해 이 솔루션을 고안했는데, 이것도 당신이 하고 있는 것처럼 보입니다.

select * from MyTable
where regexp_like(sdate, '[0-1][0-9].[0-3][0-9].[0-9][0-9][0-9][0-9]')
         -- make sure it's in the right format and ignore rows that are not
and substr(sdate,7,10) || substr(sdate,1,2) || substr(sdate,4,5) >= to_char({?EndDate}, 'YYYYMMDD')
         -- put the date in ISO format and do a string compare

이 접근 방식의 이점은 "2월 30일"과 같은 날짜에 질식하지 않는다는 것입니다.

Oracle 12c부터는 변환 예외를 포착하기 위한 함수를 정의할 필요가 없습니다.

오라클에서 소개한 제품은 다음과 같습니다.ON CONVERSION ERROR함수의 절입니다.

으로 이 문자열을 할 때 발생하는 는 기적으이일잘날된짜억때다제니합오변를류같다문습니환다을자할열오음과는류인반적본절못로은일▁basically(▁are▁().ORA-01843, ORA-01841, ORA-011861, ORA-01840null을 및 지정된 기본값 또는 null을 반환합니다.

사용 예

select to_date('2020-99-01','yyyy-mm-dd') from dual;
-- ORA-01843: not a valid month
select to_date('2020-99-01' default null on conversion error,'yyyy-mm-dd') from dual;
-- returns NULL
select to_date('2020-99-01' default '2020-01-01' on conversion error,'yyyy-mm-dd') from dual;
-- 01.01.2020 00:00:00

레거시 애플리케이션을 위한 솔루션

날짜 열이 VARCHAR2(10)로 저장된 테이블이 있다고 가정합니다.

select  * from tab;

DATE_CHAR 
----------
2021-01-01
2021-99-01

하는 방법 a 의위기사aVIRTUAL DATE를 됩니다. DATE 열은 DATE 열입니다.NULL변환 오류의 경우

alter table tab add (
  date_d DATE as (to_date(date_char default null on conversion error,'yyyy-mm-dd')) VIRTUAL
);

select * from tab;

DATE_CHAR  DATE_D             
---------- -------------------
2021-01-01 01.01.2021 00:00:00
2021-99-01

그VIRTUAL열의 형식이 다음과 같기 때문에 안전하게 사용할 수 있습니다.DATE필요한 경우 인덱스를 설정할 수 있습니다.

 select  * from tab where date_d = date'2021-01-01';

당신이 데이터베이스에 "접근할 수 없다"고 말했기 때문에, 나는 당신이 이것을 도와줄 어떤 기능도 만들 수 없고 오직 쿼리만 실행할 수 있다고 생각합니다.

이 경우 다음 코드는 다음과 같은 주의 사항과 함께 필요한 대부분의 정보를 제공합니다. 1) 평가하려는 저장된 날짜 형식은 'mm/dd/yyyy'입니다.그렇지 않은 경우에는 형식에 맞게 코드를 변경할 수 있습니다. 2) 데이터베이스에 잘못된 날짜(예: 2월 30일)가 포함되어 있지 않습니다.

먼저 테스트 테이블과 테스트 데이터를 작성했습니다.

create table test ( x number, sdate varchar2(20));
insert into test values (1, null);
insert into test values (2, '01/01/1999');
insert into test values (3, '1999/01/01');
insert into test values (4, '01-01-1999');
insert into test values (5, '01/01-1999');
insert into test values (6, '01-01/1999');
insert into test values (7, '12/31/1999');
insert into test values (8, '31/12/1999');
commit;

이제 질문은 다음과 같습니다.

WITH dates AS (
    SELECT x
         , sdate
         , substr(sdate,1,2) as mm
         , substr(sdate,4,2) as dd
         , substr(sdate,7,4) as yyyy
    FROM test
    WHERE ( substr(sdate,1,2) IS NOT NAN -- make sure the first 2 characters are digits
            AND to_number(substr(sdate,1,2))  between 1 and 12 -- and are between 0 and 12
            AND substr(sdate,3,1) = '/' -- make sure the next character is a '/'
            AND substr(sdate,4,2) IS NOT NAN -- make sure the next 2 are digits
            AND to_number(substr(sdate,4,2)) between 1 and 31 -- and are between 0 and 31
            AND substr(sdate,6,1) = '/' -- make sure the next character is a '/'
            AND substr(sdate,7,4) IS NOT NAN -- make sure the next 4 are digits
            AND to_number(substr(sdate,7,4)) between 1 and 9999 -- and are between 1 and 9999
          )
)
SELECT x, sdate
FROM dates
WHERE to_date(mm||'/'||dd||'/'||yyyy,'mm/dd/yyyy') <= to_date('08/01/1999','mm/dd/yyyy');

그리고 제 결과는:

X  SDATE
-  ----------
2  01/01/1999

WITH 문은 날짜 값이 적어도 올바른 형식인지 확인하기 위해 대부분의 검증을 수행합니다.제가 todate에 todate를 실행했을 때 유효하지 않은 월 오류가 계속 발생했기 때문에 todate 평가를 수행하기 위해 단위 월/일/년마다 중단해야 했습니다.

이것이 도움이 되길 바랍니다.

날짜에 처리기가 .잘못된 날짜에 대한 직접 예외 처리기가 없습니다.와 같은 에 제시됩니다. DD/MM/YYYYY는 아래와 같습니다.REGEXP_LIKE기능은 매력적으로 작동할 것입니다. to_date()또한 작동합니다. invalid_date가 발견되면 커서는 다음으로 이동합니다.OTHERS EXCEPTION하기의

DECLARE
   tmpnum      NUMBER; -- (1=true; 0 = false)
   ov_errmsg   LONG;
   tmpdate     DATE;
   lv_date     VARCHAR2 (15);
BEGIN
   lv_date := '6/2/2018'; -- this will fail in *regexp_like* itself
   lv_date := '06/22/2018'; -- this will fail in *to_date* and will be caught in *exception WHEN OTHERS* block
   lv_date := '07/03/2018'; -- this will succeed 

   BEGIN
      tmpnum := REGEXP_LIKE (lv_date, '[0-9]{2}/[0-9]{2}/[0-9]{4}');

      IF tmpnum = 0
      THEN                                              -- (1=true; 0 = false)
         ov_errmsg := '1. INVALID DATE FORMAT ';
         DBMS_OUTPUT.PUT_LINE (ov_errmsg);
         RETURN;
      END IF;

      tmpdate := TO_DATE (lv_date, 'DD/MM/RRRR');
      --tmpdate := TRUNC (NVL (to_date(lv_date,'DD/MM/RRRR'), SYSDATE));

      tmpnum := 1;
   EXCEPTION
      WHEN OTHERS
      THEN
         BEGIN
            tmpnum := 0;
            ov_errmsg := '2. INVALID DATE FORMAT ';
            DBMS_OUTPUT.PUT_LINE (ov_errmsg || SQLERRM);
            RETURN;
         END;
   -- continue with your other query blocks
   END;

   -- continue with your other query blocks
   DBMS_OUTPUT.PUT_LINE (tmpnum);
END;

언급URL : https://stackoverflow.com/questions/5966274/how-to-handle-to-date-exceptions-in-a-select-statment-to-ignore-those-rows

반응형

'programing' 카테고리의 다른 글

치명적 오류: mysql.h: mariadb를 설치할 때 해당 파일 또는 디렉토리가 없습니다.  (0) 2023.07.05
Spring-data-mongodb는 하나의 Mongo 인스턴스에서 여러 데이터베이스에 연결합니다.  (0) 2023.07.05
asp.net 의 컨트롤러에 단순 Ajax 호출하기 mvc  (0) 2023.07.05
Git 분기: 마스터 대 오리진/마스터 대 원격/오리진/마스터  (0) 2023.07.05
ORA-01654: 인덱스를 확장할 수 없습니다.  (0) 2023.07.05

'programing'의 다른글

  • 현재글SELECT 문에서 to_date 예외를 처리하여 해당 행을 무시하는 방법은 무엇입니까?

관련글

댓글

티스토리툴바