[Oracle] oracle에서 값 존재 검증 (With절 사용)

데이터를 검증 하는 방법은 여러가지 있지만 , 이 포스트에서 소개할 검증 방법은 수열 데이터 중 특정 데이터가 있는지 검증 하는 방법입니다.

---

예시)
예를들어 A라는 컬럼에 1~100 사이의 숫자가 있어야 한다고 치자. 이때 1부터 100까지 모든 데이터가 있는지 확인하려고 order by를 사용하여 데이터를 뽑아 검사하기 힘들다

또는 A라는 컬럼에 날짜 데이터타입으로 202001 ~ 202401까지 전부 있는지 확인하려면 어떻게 해야할까?

---

이 때는 With절을 사용하여 join하면 해결할 수 있다.

1. 숫자 찾기 예제

TEST_A 라는 테이블에 

과 같이 데이터가 들어가있다.

이때 1부터 10까지 데이터가 전부 존재하는지 검증하는 쿼리를 작성하면

WITH test_NUMBER AS (
	SELECT LEVEL AS value
	FROM DUAL 
	CONNECT BY LEVEL <= 10
)
SELECT b.value, a.name
FROM test_NUMBER b LEFT OUTER JOIN test_a a 
on b.value = a.name
ORDER BY b.value;

 

이렇게 with절과 connect by를 통해 1~10 데이터를 만들어 가상의 뷰를 만들어 join을 하면 쉽게 빈 데이터를 찾을 수 있다.

2. 날짜 찾기 예제

TEST_B라는 테이블에 

과 같이 데이터가 들어가있다.

202407 ~ 202512 까지 데이터가 전부 존재하는지 검증하는 쿼리를 작성하면

WITH test_date AS (
	SELECT TO_CHAR(ADD_MONTHS(TO_DATE('202407', 'YYYYMM'), LEVEL-1), 'YYYYMM') AS VALUE
	FROM DUAL 
	CONNECT BY LEVEL <= MONTHS_BETWEEN(to_date('202512', 'yyyymm') , to_date('202407', 'yyyymm')) + 1
)
SELECT b.value, a.dt
FROM test_date b LEFT OUTER JOIN test_b a 
on b.value = a.dt
ORDER BY b.value;

MONTHS_BETWEEN과 ADD_MONTHS를 활용해 검증 할 수 있다.

이렇게 수열로 이어진 값을 검증하는 방법에 대해 알아보았다.