使用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
-- 更新各序列值
str_sql varchar2 ( 4000 );
r boolean;
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 ;
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;
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( ' SE_DATATYPE ' , ' T_DATATYPE ' , ' DDID ' );
r: = F_MODIFY_SEQUENCE( ' SE_DATAITEM ' , ' T_DATAITEM ' , ' ITEMID ' );
end P_MODIFY_SEQUENCES;
/
-- 更新各序列值
str_sql varchar2 ( 4000 );
r boolean;
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 ;
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;
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( ' SE_DATATYPE ' , ' T_DATATYPE ' , ' DDID ' );
r: = F_MODIFY_SEQUENCE( ' SE_DATAITEM ' , ' T_DATAITEM ' , ' ITEMID ' );
end P_MODIFY_SEQUENCES;
/