Developement/DataBase
sequence reset
highheat
2008. 12. 22. 14:19
출처 : http://www.orafaq.com/forum/t/4003/2/
CREATE OR REPLACE PROCEDURE reset_seq_to_1(p_seq_name IN VARCHAR2) AS
v_seq NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT ' || p_seq_name || '.NEXTVAL FROM DUAL'
INTO v_seq;
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_seq_name || ' INCREMENT BY -' || v_seq || ' MINVALUE 0';
EXECUTE IMMEDIATE 'SELECT ' || p_seq_name || '.NEXTVAL FROM DUAL'
INTO v_seq;
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_seq_name || ' INCREMENT BY 1 MINVALUE 0';
END reset_seq_to_1;