Oracle 시퀀스 Value 설정하기.(프로시저로 관리)

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

 

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

 

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

 

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

 

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에서 한번의 명령어로 시퀀스값을 마이그레이션 데이터 기준으로 설정이 가능하다.