[Oracle] 시퀀스 변경

보통 시퀀스 값은 변경하지 않지만 부득이하게 변경이 필요할 경우 사용한다

CREATE OR REPLACE PROCEDURE MOD_SEQ(seqName IN VARCHAR2) IS
  v_val   NUMBER;
  v_cnt   NUMBER DEFAULT 0;
  loopCnt NUMBER;
BEGIN
  --SEQUENCE 값 초기화 시작
  EXECUTE IMMEDIATE 'SELECT NOTICE_' || seqName || '_SEQUENCE.NEXTVAL FROM DUAL' INTO v_val;

  EXECUTE IMMEDIATE 'ALTER SEQUENCE NOTICE_' || seqName || '_SEQUENCE INCREMENT BY -' || v_val || ' MINVALUE 0';

  EXECUTE IMMEDIATE 'SELECT NOTICE_' || seqName || '_SEQUENCE.NEXTVAL FROM DUAL' INTO v_val;

  EXECUTE IMMEDIATE 'ALTER SEQUENCE NOTICE_' || seqName || '_SEQUENCE INCREMENT BY 1 MINVALUE 0';
  --SEQUENCE 값 초기화 종료

  --증가시킬 값을 찾아와 loopCnt 에 할당 loopCnt DEFAULT 값 대체 가능
  SELECT
         TO_NUMBER(REPLACE(MAX( SUBSTR( NOTICE_ID, LENGTH( 'DD-'||seqName||'-' ) + 1 ) ), 0 ,''))
    INTO loopCnt
    FROM
         NOTICE
   WHERE
         SUBSTR( NOTICE_ID, 1, LENGTH( 'DD-'||seqName||'-' ) ) = 'DD-'||seqName||'-'
     AND LENGTH( NOTICE_ID ) - LENGTH( 'DD-'||seqName||'-' )   = 8
  ;

  --loopCnt 만큼 SEQUENCE 값을 증가시킴
  LOOP
  EXIT WHEN v_cnt = loopCnt;
          EXECUTE IMMEDIATE 'SELECT NOTICE_' || seqName || '_SEQUENCE.NEXTVAL FROM DUAL' INTO v_val;
    v_cnt := v_cnt + 1;
  END LOOP;

END MOD_SEQ;