오라클 스크립트 생성 SQL

여러개의 반복되는 쿼리 수동으로 만들기 귀찮을 때... 

비싼 오라클에게 일을 맡기자.

예를 들어 AAA 테이블의 data 를 select 해서 AAA2 로 insert 하는 경우

insert into AAA2 select * from AAA

이렇게 하면 된다. 

하지만 AAA 의 데이터량이 많아서 운영중에 대량 insert 하기 부담스러운 상황일 경우..

마침 AAA 테이블의 key_date 컬럼이 있다고 치고 key_date 의 값에 따라 쪼개서 insert 를 할수 있다. 

i.e) insert into AAA2 select * from AAA 

where key_date between TO_DATE('20170915') +9/24 and TO_DATE('20170915') +10/24 

and key_date < TO_DATE('20170915') +10/24;

이런식으로 9월 15일 9시부터 10시 까지의 데이터를 insert 하고 10시 부터 11시 계속 1시간 단위로 

쪼개서 SQL 을 실행시킬수 있다. 

근데 만약, 시간을 많이 쪼개야 한다거나 테이블이 AAA 와 동일하게 BBB, CCC, DDD 도 있다면..

손으로 일일이 작성하기 귀찮고 오타가 날 확률도 높아진다.

아래처럼 해보자.

select 'INSERT INTO '||TAB||'2 SELECT * FROM '||TAB||' WHERE KEY_DATE BETWEEN TO_DATE('||''''||'20170914'||''''||

        ') +'||(17 + RN) ||'/24 and TO_DATE('||''''||'20170914'||''''||') +'||(18 + RN)||'/24 and KEY_DATE < TO_DATE('||''''||'20170914'||''''||') +'||(18 + RN)||'/24;'  stmt

from 

        (SELECT 'AAA' AS TAB FROM DUAL 

         UNION ALL 

         SELECT 'BBB' FROM DUAL

         UNION ALL

         SELECT 'CCC' FROM DUAL

         UNION ALL

         SELECT 'DDD' FROM DUAL

         )T,

         (SELECT ROWNUM RN

             FROM DBA_TABLES

           WHERE ROWNUM < 7

          )  

 ORDER BY TAB, RN 

========= 결과물 ===========================================================

INSERT INTO AAA SELECT * FROM AAA WHERE KEY_DATE BETWEEN TO_DATE('20170914') +18/24 and TO_DATE('20170914') +19/24 and KEY_DATE < TO_DATE('20170914') +19/24;

INSERT INTO AAA SELECT * FROM AAA WHERE KEY_DATE BETWEEN TO_DATE('20170914') +19/24 and TO_DATE('20170914') +20/24 and KEY_DATE < TO_DATE('20170914') +20/24;

INSERT INTO AAA SELECT * FROM AAA WHERE KEY_DATE BETWEEN TO_DATE('20170914') +20/24 and TO_DATE('20170914') +21/24 and KEY_DATE < TO_DATE('20170914') +21/24;

INSERT INTO AAA SELECT * FROM AAA WHERE KEY_DATE BETWEEN TO_DATE('20170914') +21/24 and TO_DATE('20170914') +22/24 and KEY_DATE < TO_DATE('20170914') +22/24;

INSERT INTO AAA SELECT * FROM AAA WHERE KEY_DATE BETWEEN TO_DATE('20170914') +22/24 and TO_DATE('20170914') +23/24 and KEY_DATE < TO_DATE('20170914') +23/24;

INSERT INTO AAA SELECT * FROM AAA WHERE KEY_DATE BETWEEN TO_DATE('20170914') +23/24 and TO_DATE('20170914') +24/24 and KEY_DATE < TO_DATE('20170914') +24/24;

INSERT INTO BBB SELECT * FROM BBB WHERE KEY_DATE BETWEEN TO_DATE('20170914') +18/24 and TO_DATE('20170914') +19/24 and KEY_DATE < TO_DATE('20170914') +19/24;

INSERT INTO BBB SELECT * FROM BBB WHERE KEY_DATE BETWEEN TO_DATE('20170914') +19/24 and TO_DATE('20170914') +20/24 and KEY_DATE < TO_DATE('20170914') +20/24;

INSERT INTO BBB SELECT * FROM BBB WHERE KEY_DATE BETWEEN TO_DATE('20170914') +20/24 and TO_DATE('20170914') +21/24 and KEY_DATE < TO_DATE('20170914') +21/24;

INSERT INTO BBB SELECT * FROM BBB WHERE KEY_DATE BETWEEN TO_DATE('20170914') +21/24 and TO_DATE('20170914') +22/24 and KEY_DATE < TO_DATE('20170914') +22/24;

INSERT INTO BBB SELECT * FROM BBB WHERE KEY_DATE BETWEEN TO_DATE('20170914') +22/24 and TO_DATE('20170914') +23/24 and KEY_DATE < TO_DATE('20170914') +23/24;

INSERT INTO BBB SELECT * FROM BBB WHERE KEY_DATE BETWEEN TO_DATE('20170914') +23/24 and TO_DATE('20170914') +24/24 and KEY_DATE < TO_DATE('20170914') +24/24;

INSERT INTO CCC SELECT * FROM CCC WHERE KEY_DATE BETWEEN TO_DATE('20170914') +18/24 and TO_DATE('20170914') +19/24 and KEY_DATE < TO_DATE('20170914') +19/24;

INSERT INTO CCC SELECT * FROM CCC WHERE KEY_DATE BETWEEN TO_DATE('20170914') +19/24 and TO_DATE('20170914') +20/24 and KEY_DATE < TO_DATE('20170914') +20/24;

INSERT INTO CCC SELECT * FROM CCC WHERE KEY_DATE BETWEEN TO_DATE('20170914') +20/24 and TO_DATE('20170914') +21/24 and KEY_DATE < TO_DATE('20170914') +21/24;

INSERT INTO CCC SELECT * FROM CCC WHERE KEY_DATE BETWEEN TO_DATE('20170914') +21/24 and TO_DATE('20170914') +22/24 and KEY_DATE < TO_DATE('20170914') +22/24;

INSERT INTO CCC SELECT * FROM CCC WHERE KEY_DATE BETWEEN TO_DATE('20170914') +22/24 and TO_DATE('20170914') +23/24 and KEY_DATE < TO_DATE('20170914') +23/24;

INSERT INTO CCC SELECT * FROM CCC WHERE KEY_DATE BETWEEN TO_DATE('20170914') +23/24 and TO_DATE('20170914') +24/24 and KEY_DATE < TO_DATE('20170914') +24/24;

INSERT INTO DDD SELECT * FROM DDD WHERE KEY_DATE BETWEEN TO_DATE('20170914') +18/24 and TO_DATE('20170914') +19/24 and KEY_DATE < TO_DATE('20170914') +19/24;

INSERT INTO DDD SELECT * FROM DDD WHERE KEY_DATE BETWEEN TO_DATE('20170914') +19/24 and TO_DATE('20170914') +20/24 and KEY_DATE < TO_DATE('20170914') +20/24;

INSERT INTO DDD SELECT * FROM DDD WHERE KEY_DATE BETWEEN TO_DATE('20170914') +20/24 and TO_DATE('20170914') +21/24 and KEY_DATE < TO_DATE('20170914') +21/24;

INSERT INTO DDD SELECT * FROM DDD WHERE KEY_DATE BETWEEN TO_DATE('20170914') +21/24 and TO_DATE('20170914') +22/24 and KEY_DATE < TO_DATE('20170914') +22/24;

INSERT INTO DDD SELECT * FROM DDD WHERE KEY_DATE BETWEEN TO_DATE('20170914') +22/24 and TO_DATE('20170914') +23/24 and KEY_DATE < TO_DATE('20170914') +23/24;

INSERT INTO DDD SELECT * FROM DDD WHERE KEY_DATE BETWEEN TO_DATE('20170914') +23/24 and TO_DATE('20170914') +24/24 and KEY_DATE < TO_DATE('20170914') +24/24;

자신의 상황에 따라 응용이 가능하다.