declare
seq_name varchar2(100);
l_currval number; --序列的当前值
cursor seq_cur is
select ur.sequence_name from user_sequences ur WHERE sequence_name LIKE 'HMALL%' OR sequence_name LIKE 'SYS%' OR sequence_name LIKE 'ACT%';--将当前数据库所有序列的名称存放到游标中 此处写判断语句 否则会出现ORA-32793
begin
--dbms_output.enable(buffer_size=>null);
open seq_cur;
fetch seq_cur into seq_name;
loop
exit when not seq_cur%found;
--修改数据库序列的下限
execute immediate 'alter sequence '||seq_name||' minvalue -5000';
--需要变化的数量
execute immediate 'alter sequence '||seq_name||' increment by 10000';
--给序列取下一个数
execute immediate 'select '||seq_name||'.nextval from dual' into l_currval;
--将序列的递增情况,恢复为按1递增
execute immediate 'alter sequence '||seq_name||' increment by 1';
--将数据库序列的下限修改为1
execute immediate 'alter sequence '||seq_name||' minvalue 1';
--dbms_output.put_line(l_currval);
fetch seq_cur into seq_name;
end loop;
close seq_cur;
end;
Oracle批量修改序列值
最新推荐文章于 2024-07-15 19:21:03 发布