create or replace procedure p_reset_seq( p_seq_name in varchar2 )
is
l_val number;
begin

execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val;

execute immediate 'alter sequence ' || p_seq_name || ' increment by -' || l_val || ' minvalue 0 maxvalue 9999 nocycle nocache'; --把 cycle 和 cache 关闭，否则会报错 execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val; execute immediate 'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0 maxvalue 9999 cycle cache 20'; --重新打开 cycle 和 cache end;