批量修改Oracle序列值的存储过程

转载自http://blog.csdn.net/00081168/archive/2007/08/10/1736242.aspx

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;
    currvalue 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;
    dbms_output.put_line('表' || talename || '的' || keyf || '最大值为:' ||
                         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;
    
      --循环   
      loop
        --查询当前序列的下一个值   
        f_sql := 'select ' || sequencename || '.nextval from dual';
        execute immediate f_sql
          into lastvalue;
      
        --当序列的下一个值>= 表中现有主键的最大值时退出循环   
        exit when lastvalue >= next_num - 1;
        --如果序列的下一个值小于表中现有主键的最大值时继续获取序列的下一个值   
        f_sql := 'select ' || sequencename || '.nextval from dual';
        execute immediate f_sql
          into lastvalue;
      
      end loop;
      --修改后的sequencename.currval仍为修改前的值,但sequencename.nextval值为中主键的最大值+1   
      f_sql := 'alter sequence ' || sequencename ||
               ' increment by 1 cache 20';
      execute immediate f_sql;
      dbms_output.put_line('序列' || sequencename || '的下一个值为' || lastvalue);
      dbms_output.put_line('');
    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');
  r := f_modify_sequence('SEQ_FEE',
                         'T_BSFEE',
                         'to_number(substr(FEE_NO,-12))');
end p_modify_sequences;



set serveroutput on;
exec p_modify_sequences;