[Oracle] 시퀀스 초기화 프로시저 생성하기

오라클 초기화 프로시저 생성

---

CREATE OR REPLACE PROCEDURE SCOTT.reset_sequence (sequencename IN VARCHAR2) as curr_val INTEGER;

BEGIN
 EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' MINVALUE 0';
  EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual' INTO curr_val;
  EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment by -'||curr_val;
  EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual' INTO curr_val;
  EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment by 1';
END reset_sequence;

---

 

 

프로시저 실행

---

EXEC reset_sequence('시퀀스명');

---