将序列重置为特定值 [英] Reset sequence to a specific value
问题描述
我们正在创建现有数据库的空白"/最小副本,并希望将其中一个序列重置为一个值.将数字放在下面的作品中,但是当导出序列中的数字更大时,我想使其可重用,以免丢失&重新创建.
We're creating a 'blank'/minimal copy of an existing database and want to reset one of the sequences to a value. Putting the number in the below works, but I want to make it reusable when sequence in the export has a higher number, trying to avoid dropping & recreating.
您可以执行与子选择和计算等效的操作来获取值,还是需要将其设置为变量1st?
Can you do the equivalent of a subselect and calculation to get the value or does this need to be set as a variable 1st?
alter sequence users.SQ_USER_ID INCREMENT BY (99999 - select users.SQ_USER_ID.nextval from dual) nocache;
select users.SQ_USER_ID.nextval from dual;
alter sequence users.SQ_USER_ID INCREMENT BY 1 cache 20;
目标是在nextval处以99999结尾.
the aim being to end with the sequence at nextval as 99999.
推荐答案
您可以使用负增量将序列重置为较低的值-此脚本(只是您的PL/SQL块版本)将与序列一起使用值大于9999时没有问题):
You can use a negative increment to reset a sequence to a lower value - this script (it's just a PL/SQL block version of yours) will work with sequence values larger than 9999 without problems):
declare
currval pls_integer;
diff pls_integer;
begin
select SQ_USER_ID.nextval into currval from dual;
dbms_output.put_line('value before alter: ' || currval);
diff := 99999 - currval;
dbms_output.put_line('diff: ' || diff);
execute immediate ' alter sequence SQ_USER_ID INCREMENT BY ' || diff || 'nocache';
select SQ_USER_ID.nextval into currval from dual;
dbms_output.put_line('value after alter: ' || currval);
execute immediate 'alter sequence SQ_USER_ID INCREMENT BY 1 cache 20';
end;
这篇关于将序列重置为特定值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!