本文介绍了一个Oracle PL/SQL过程，用于重置序列的起始值并调整其增量，同时关闭和重新开启序列的循环和缓存特性。
create or replace procedure p_reset_seq( p_seq_name in varchar2 )
is
l_val number;
begin
execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
execute immediate 'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0 maxvalue 9999 cycle cache 20'; --重新打开 cycle 和 cache
end;
is
l_val number;
begin
execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
execute immediate 'alter sequence ' || p_seq_name || ' increment by -' || l_val || ' minvalue 0 maxvalue 9999 nocycle nocache'; --把 cycle 和 cache 关闭，否则会报错execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val;
execute immediate 'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0 maxvalue 9999 cycle cache 20'; --重新打开 cycle 和 cache
end;
扫一扫
3444
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
