꿈꾸는 별나라

-- STEP 1 테스트 테이블 생성
CREATE TABLE TEST_A (
TEST_CN VARCHAR2(100)
);
-- STEP 2 테스트 데이터 생성
INSERT INTO TEST_A ( TEST_CN ) VALUES ('AAA');
-- STEP 3 테이블 및 데이터 생성 확인
SELECT * FROM  TEST_A;
-- STEP 4 프로시저 생성 
CREATE OR REPLACE PROCEDURE TEST_AA (
IN_TABEL_NM IN VARCHAR2
)
IS
  V_STR VARCHAR2(500);
  V_RESULT  VARCHAR2(500);
BEGIN 

FOR  REC  IN  (
  SELECT OBJECT_NAME
   FROM   SYS.ALL_OBJECTS
   WHERE  OBJECT_TYPE = 'TABLE'
     AND  OBJECT_NAME = IN_TABEL_NM
   )LOOP
    
    V_STR := 'SELECT TEST_CN FROM '|| REC.OBJECT_NAME ;
EXECUTE IMMEDIATE V_STR INTO V_RESULT;

DBMS_OUTPUT.PUT_LINE('TABLE 명 : ' || REC.OBJECT_NAME || ', ' || V_RESULT );
    
    V_STR := 'DELETE FROM ' || REC.OBJECT_NAME;
    EXECUTE IMMEDIATE V_STR;


END LOOP;
END;

-- STEP 5 프로시저 실행.
   EXEC TEST_AA('TEST_A');

-- STEP 6 테스트 데이터 삭제 확인.
   SELECT * FROM  TEST_A;

-- STEP 7 테스트 테이블 삭제    
   DROP TABLE TEST_A;

프로젝트 진행중에 테스트 데이터를 마이그레이션 하는 동안

시퀀스는 초기화 시켜주지 않아서 매번 수동으로 시퀀스 값을 설정했었다.

한 두번도 아니고 일일이 수정하기에는 너무 손이 많이 가는 작업이라서

시퀀스를 새롭게 들어온 데이터에 맞춰서 새로 설정 해주는 방법을 찾아서 기록에 남기고자 한다.

1.  먼저 시퀀스를 설정할 프로시저를 하나 생성하고 매개변수로 3가지를 받는다.

 1-1 시퀀스명 

 1-2 시퀀스 대상 테이블명

 1-3 시퀀스 대상 컬럼명 

원리는 이렇다. 

현재의 데이터를 기준으로 시퀀스 값을 읽어 들이고 

그에 맞춰서 시퀀스 값을 변경해준다. 

CREATE OR REPLACE PROCEDURE P_REBUILD_SP_SEQUANCE
(
  I_SEQ_NAME IN VARCHAR2 /* 시퀀스 이름 */
  ,I_TB_NAME IN VARCHAR2 /* 시퀀스 대상 테이블 */
  ,I_TB_KEY IN VARCHAR2  /* 시퀀스 대상 컬럼 */
)
IS
  I_SEQNO1 NUMBER;
  I_SEQNO2 NUMBER;

BEGIN 
  /* 시퀀스 증가값을 1로 설정 */

   EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || I_SEQ_NAME || ' INCREMENT BY 1';  

   /* 현재 시퀀스 값 */   

   EXECUTE IMMEDIATE 'SELECT ' || I_SEQ_NAME || ' .NEXTVAL FROM DUAL' INTO I_SEQNO1;  

   /* 업무 테이블 시퀀스값 확인 */  

   EXECUTE IMMEDIATE 'SELECT NVL(MAX(TO_NUMBER('||I_TB_KEY||')),1) +10 FROM '||I_TB_NAME INTO I_SEQNO2;

   
   dbms_output.put_line(I_SEQNO2 - I_SEQNO1);

   /* 업무 테이블 시퀀스 값과 현재 시퀀스 설정값을 비교하여 시퀀스 증가값을 설정 */
   EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || I_SEQ_NAME || ' INCREMENT BY ' || (I_SEQNO2 - I_SEQNO1);
   /* 시퀀스 실행 */

   EXECUTE IMMEDIATE 'SELECT  ' || I_SEQ_NAME || ' .NEXTVAL FROM DUAL' INTO I_SEQNO1;
   dbms_output.put_line(I_SEQNO1);

   /* 시퀀스 증가값을 다시 1로 변경 */
   EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || I_SEQ_NAME || ' INCREMENT BY 1';
END;

이렇게 시퀀스를 새롭게 설정할 프로시저를  만들고

시퀀스를 가지고 있는 테이블 목록을 가지고 있는

프로시저를 하나 더 생성하였다.

CREATE OR REPLACE PROCEDURE P_RESET_SP_SEQUANCE
AS
BEGIN
/* 매개변수의 순서를 시퀀스명 , 테이블명 , 컬럼명 순임 */
P_REBUILD_SP_SEQUANCE('DOC_MASTER_SEQ','DOC_MASTER_INFO','DOC_ID'); /* 문서 정보 */ 
P_REBUILD_SP_SEQUANCE('DOC_ATTACH_FILE_SEQ','DOC_ATTACH_FILE_INFO','FILE_ID'); /* 파일 정보 */ 
P_REBUILD_SP_SEQUANCE('DOC_PRINT_HIST_SEQ','DOC_PRINT_HIST','PRINT_ID'); /* 인쇄 히스토리 정보 */ 
P_REBUILD_SP_SEQUANCE('DOC_PRINT_RSN_SEQ','DOC_PRINT_RSN','REQ_ID'); /* 인쇄 재요청 테이블 */ 
P_REBUILD_SP_SEQUANCE('DOC_STAMP_SEQ','DOC_STAMP_HIST','STAMP_ID'); /* 인쇄 시 직인 사용 히스토리 */

END; 

이렇게 해두면 새롭게 데이터마이그레이션이 진행되어도 

EXCUTE P_RESET_SP_SEQUANCE; 

SQL에서 한번의 명령어로 시퀀스값을 마이그레이션 데이터 기준으로 설정이 가능하다. 

//현재 날짜가 몇주차인지.
1.SELECT (DAY(convert(varchar(10),getdate(),120)) + (DATEPART(dw, DATEADD (MONTH, DATEDIFF (MONTH, 0,convert(varchar(10),getdate(),120)), 0)) -1)-1)/7 + 1

2.SELECT DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, convert(varchar(10),getdate(),120)), 0),convert(varchar(10),getdate(),120)) +1;

EX) 달력상 2010-09-13일은 두번째주가 아니라 세번째주로 갖고온다.

      특정 날짜형식 'YYYY-MM-DD' 을 넣고 싶으면 convert(varchar(10),getdate(),120) 이 부분만    값 다르게 넣으면 됨.

결과

3

참고로 오라클에서 2010-09-13 일을 TO_CHAR(SYSDATE,'W') 이런식으로 갖고오면 13일을 두번째 주로 인식함.

//현재 날짜가 무슨 요일인지

select datepart(dw,convert(varchar(10),getdate(),120))

결과

0:일요일 1:월요일....

select to_char(sysdate, 'day') from dual
--결과 : 화요일
;
select to_char(sysdate, 'dy') from dual
--결과 : 화
;
select to_char(sysdate, 'd') from dual
--결과 : 3 (1:일, 2:월, 3:화, 4:수, 5:목, 6:금, 7:토)
;

DISTINCT 와 GROUP BY의 차이