转载自：http://blog.csdn.net/00081168/archive/2007/08/10/1736242.aspx
使用Oracle序列来生成表主键值的时候 ，可能会碰到会根据主键值来修改相应序列的值。
下面存储过程P_MODIFY_SEQUENCES完成这一目的。
r:=F_MODIFY_SEQUENCE('SE_DATATYPE','T_DATATYPE','DDID');
r:=F_MODIFY_SEQUENCE('SE_DATAITEM','T_DATAITEM','ITEMID');
把这个修改成要修改的序列名及对应表名和主键名，支持多序列。
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;
/