使用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;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
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;
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
begin
r:
=
F_MODIFY_SEQUENCE(
'
SE_DATATYPE
'
,
'
T_DATATYPE
'
,
'
DDID
'
);
r:
=
F_MODIFY_SEQUENCE(
'
SE_DATAITEM
'
,
'
T_DATAITEM
'
,
'
ITEMID
'
);
end
P_MODIFY_SEQUENCES;
/