修改序列

create or replace procedure p_modify_sequences is 
  --更新各序列值的动态sql语句  
  str_sql varchar2(4000);  
  --是否修改成功,未发生异常返回true  
  r boolean;  
 
  --修改相应序列的nextval  
  --sequencename:序列名  
  --talename:由序列生成主键的表名  
  --keyf:由序列生成的主键名  
  function f_modify_sequence(sequencename varchar2,  
                             talename     varchar2,  
                             keyf         varchar2) return boolean is 
    lastvalue integer;  
    f_sql     varchar2(4000);  
    next_num  number;  
    max_num   number;  
  begin 
    --查询表中主键的最大值  
    f_sql := 'select max(' || keyf || ') from ' || talename;  
    execute immediate f_sql  
      into max_num;  
    if (max_num is not null) then 
      next_num := max_num + 1;  
      --修改序列的自增量为1  
      f_sql    := 'alter sequence ' || sequencename ||  
                  ' increment by 1 nocache';  
      execute immediate f_sql;  
      --f_sql:='select '||sequenceName||'.nextval from dual';  
      --execute immediate f_sql;  
      --f_sql := 'alter sequence ' || sequencename ||  
      --         ' increment by 1 nocache';  
      --execute immediate f_sql;  
      
      --循环  
      loop  
        --查询当前序列的下一个值  
        f_sql := 'select ' || sequencename || '.nextval from dual';  
        execute immediate f_sql  
          into lastvalue;  
        --select SE_DATATYPE.nextval into LastValue from dual;    
        --当序列的下一个值>= 表中现有主键的最大值时退出循环  
        exit when lastvalue >= next_num - 1;  
        --如果序列的下一个值小于表中现有主键的最大值时继续获取序列的下一个值  
        f_sql := 'select ' || sequencename || '.nextval from dual';  
        execute immediate f_sql  
          into lastvalue;  
        --select SE_DATATYPE.nextval into LastValue from dual;  
      end loop;
      --修改后的sequencename.currval仍为修改前的值,但sequencename.nextval值为中主键的最大值+1
      f_sql := 'alter sequence ' || sequencename ||  
               ' increment by 1 cache 20';  
      execute immediate f_sql;  
    end if;  
    commit;  
    return true;  
  exception  
    when others then 
      return false;  
  end f_modify_sequence;  
 
begin 
  r := f_modify_sequence('SEQ_MENUCODE', 'T_BOSSMENU', 'MENUCODE');  
end p_modify_sequences;

 

--更改步长
Alter sequence SEQ_FEE increment by 10;
select SEQ_FEE.nextval from dual;
--还原步长
Alter sequence mysq increment by 1;
select 'SF'||LPAD(SEQ_FEE.NEXTVAL,12,'0') AS VAL FROM DUAL;