oracle通过触发器 更新序列下次增长值

之前一直有误区，说明下：
1.oracle无法直接修改 next下次的值。
2.设置自动增长的步长 可以是负数。

办法：
通过修改 步长为负数，获取一次序列，把序列弄回到1，然后把步长修改成正常的数字。

create or replace procedure RESET_PAGE_NUM_SEQ AS
  v_count NUMBER(10);
  v_sql   VARCHAR2(100);
begin

  --获取当前序列AM_BDC_PAGE_NUM_SEQ_TEST的下一个值  
  EXECUTE IMMEDIATE 'select AM_BDC_PAGE_NUM_SEQ_TEST.nextval curr_val from dual'
    INTO v_count;

  if v_count > 5000 then
  --变成负数
  v_count := (v_count - 1) * -1;

  --将每次增长的值改成负数
  v_sql := 'alter sequence AM_BDC_PAGE_NUM_SEQ_TEST increment by ' ||
           v_count;
  EXECUTE IMMEDIATE v_sql;

  ---获取调整后的下一个值，将下一次获取的值更新到1
  EXECUTE IMMEDIATE 'select AM_BDC_PAGE_NUM_SEQ_TEST.nextval curr_val from dual'
  INTO v_count;

  ----修改序列为原来的规则 
  EXECUTE IMMEDIATE 'alter sequence AM_BDC_PAGE_NUM_SEQ_TEST increment by 1';

  ---再次调用一次 
  EXECUTE IMMEDIATE 'select AM_BDC_PAGE_NUM_SEQ_TEST.nextval from dual'
    INTO v_count;
 end if;
EXCEPTION

  WHEN OTHERS THEN
    
  EXECUTE IMMEDIATE 'alter sequence AM_BDC_PAGE_NUM_SEQ_TEST increment by 1';
  EXECUTE IMMEDIATE 'select AM_BDC_PAGE_NUM_SEQ_TEST.nextval from dual';
  

end RESET_PAGE_NUM_SEQ;


begin
  sys.dbms_job.submit(job => :job,
                      what => 'RESET_PAGE_NUM_SEQ;',
                      next_date => to_date('31-03-2022 22:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'trunc(sysdate) + 22/24');
  commit;
end;
/