17,377
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure P_TOOLS_RESET_SEQUENCES
(
pi_sequence_name in varchar2
)
as
vn_number number;
vr_sequence user_sequences%rowtype;
begin
-- Modify the last number
select * into vr_sequence from user_sequences t where t.sequence_name=pi_sequence_name;
if vr_sequence.max_value-vr_sequence.last_number>0 then
execute immediate 'alter sequence '||pi_sequence_name||' increment by '||(vr_sequence.max_value-vr_sequence.last_number)|| ' nocache';
execute immediate 'select '||pi_sequence_name||'.nextval from dual' into vn_number;
execute immediate 'alter sequence '||pi_sequence_name||' increment by 1 nocache';
execute immediate 'select '||pi_sequence_name||'.nextval from dual' into vn_number;
end if;
exception
when others then
null;
end P_TOOLS_RESET_SEQUENCES;