一、问题描述
现在需要将数据库中的所有sequence的当前值增加1000。
编写一个存储过程,传入1000,将所有sequence的值增加1000;若传入-1000,将所有的sequence的值减少1000。
二、实现功能
先写一个临时测试的过程,
declare
seq_name varchar2(100);
l_currval number; --序列的当前值
cursor seq_cur is
select ur.sequence_name from user_sequences ur;--将当前数据库所有序列的名称存放到游标中
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 1000';
--给序列取下一个数
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;
经过测试,发现所有的序列值都增加了1000。
代码说明:
注意上面代码的第19行,在给序列取下一个值时,写的是
execute immediate 'select '||seq_name||'.nextval from dual' into l_currval;
那如果写成:
execute immediate 'select '||seq_name||'.nextval from dual'
不是更简单吗?经过实际测试,发现如果不把值赋给一个变量的话,该存储过程运行完,sequence的当前值不会发生变化。
(虽然我们在手动执行select 序列名.nextval from dual时会有效果)
原因我也不清楚,如果有知道其中缘由的,欢迎提供分享。
将以上过程改写成可以重复调用的存储过程,代码如下:
create or replace procedure updateAllSeqVal(varNum number) is
seq_name varchar2(100);
l_currval number; --序列的当前值
cursor seq_cur is
select ur.sequence_name from user_sequences ur;--将当前数据库所有序列的名称存放到游标中
begin
--dbms_output.enable(buffer_size=>null);
open seq_cur;
fetch seq_cur into seq_name;
loop
exit when not seq_cur%found;
--dbms_output.put_line(seq_name);
--修改数据库序列的下限
execute immediate 'alter sequence '||seq_name||' minvalue -5000';
--需要变化的数量
execute immediate 'alter sequence '||seq_name||' increment by '||varNum;
--给序列取下一个数
execute immediate 'select '||seq_name||'.nextval from dual' into l_currval;
--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;
调用过程,使当前连接数据库中的所有sequence的当前值都统一增加1000:
begin
updateAllSeqVal(1000);
end;
查看所有序列的当前情况(主要看last_number一列):
select * from user_sequences;