ORACLE ( CONNECT BY / 사이드로 LPAD, RPAD 함수 까지)

CONNECT BY 구성

 

WHERE              데이터를 가져온 뒤 마지막으로 조건절에 맞게 정리

START WITH      어떤 데이터로 계층구조를 지정하는지 지정

                            - 가장 처음에 데이터를 거르는 플랜을 타고, 컬럼에 인덱스가 있어야 한다.

CONNECT BY    각 행들의 연결 관계를 설정

                            - 결과에는 LEVEL이라는 컬럼이 있으며, 계층의 깊이를 의미한다.

 

 

 

CONNECT BY 사용법

 

SELECT LEVEL AS NO
 FROM DUAL
CONNECT BY LEVEL <=10

 - 결과
no
1
2
3
4
5
6
7
8
9
10

LEVEL 을 10까지 표현

SELECT '2020년 '||LPAD(LEVEL, 2, 0)||'월' AS NO
FROM DUAL
CONNECT BY LEVEL <= 12

no
2020년 01월
2020년 02월
2020년 03월
2020년 04월
2020년 05월
2020년 06월
2020년 07월
2020년 08월
2020년 09월
2020년 10월
2020년 11월
2020년 12월

LPAD(왼쪽을 채움), RPAD(오른쪽을 채움) 함수

 LPAD("값", "총 문자길이", "채움문자")

 

SELECT LEVEL, LPAD(LEVEL, 5), LPAD(LEVEL, 5, '0')
FROM DUAL
CONNECT BY LEVEL <= 10

 

                     level                                       lpad(level,5)                                                  lpad(level,5,'0')

1	1	00001
2	2	00002
3	3	00003
4	4	00004
5	5	00005
6	6	00006
7	7	00007
8	8	00008
9	9	00009
10	10	00010

"총 문자길이" - Length("값") = 채움문자수

 SELECT TO_DATE('20200701', 'YYYYMMDD') + (LEVEL-1) AS DT
   FROM DUAL
CONNECT BY LEVEL <= (TO_DATE('20200717', 'YYYYMMDD') - TO_DATE('20200701', 'YYYYMMDD')) + 1

 - 결과
 
 dt
2020-07-01 00:00:00
2020-07-02 00:00:00
2020-07-03 00:00:00
2020-07-04 00:00:00
2020-07-05 00:00:00
2020-07-06 00:00:00
2020-07-07 00:00:00
2020-07-08 00:00:00
2020-07-09 00:00:00
2020-07-10 00:00:00
2020-07-11 00:00:00
2020-07-12 00:00:00
2020-07-13 00:00:00
2020-07-14 00:00:00
2020-07-15 00:00:00
2020-07-16 00:00:00
2020-07-17 00:00:00

 

WITH WD AS
(
    SELECT TO_DATE('20200701', 'YYYYMMDD') AS FROM_DT   -- 시작일자
         , TO_DATE('20200717', 'YYYYMMDD') AS TO_DT     -- 종료일자    
      FROM DUAL
)
SELECT DT, DT2
  FROM ( 
     SELECT TO_CHAR(FROM_DT + (LEVEL-1), 'YYYYMMDD') AS DT
          , TO_CHAR(FROM_DT + (LEVEL-1), 'YYYY-MM-DD') AS DT2
       FROM WD
    CONNECT BY LEVEL <= (TO_DT - FROM_DT) + 1
  )
  
   - 결과
   
   dt			dt2
20200701	2020-07-01
20200702	2020-07-02
20200703	2020-07-03
20200704	2020-07-04
20200705	2020-07-05
20200706	2020-07-06
20200707	2020-07-07
20200708	2020-07-08
20200709	2020-07-09
20200710	2020-07-10
20200711	2020-07-11
20200712	2020-07-12
20200713	2020-07-13
20200714	2020-07-14
20200715	2020-07-15
20200716	2020-07-16
20200717	2020-07-17