如何在不删除oracle中的序列的情况下更改序列顺序

Question

我的当前序列号是203，我需要跳到1203而不丢弃序列。

Answer 1

首先改变序列增量，假设您说203到1203，我加1000，根据需要进行调整。

ALTER SEQUENCE yourSequence INCREMENT BY 1000;

然后请求一个值

SELECT yourSequence.NextVal FROM dual;

然后将其改回递增1(假设最初是1(

ALTER SEQUENCE yourSequence INCREMENT BY 1;

如果序列正在使用中，你真的不想这样做，因为它可能会跳数千。

Answer 2

您可以在您想要的DB模式上创建这样一个过程，如下所示：

SQL>Create or Replace Procedure Pr_Set_Sequence( i_seq_name varchar2, i_val pls_integer ) is
v_val     pls_integer;
begin
for c in (
Select u.sequence_name seq
From User_Sequences u
Where u.sequence_name = upper(i_seq_name)
) 
loop  
execute immediate 'select '||i_seq_name||'.nextval from dual' INTO v_val;
execute immediate 'alter sequence '||i_seq_name||' increment by ' ||
to_char(-v_val+i_val) || ' minvalue 0';
execute immediate 'select '||i_seq_name||'.nextval from dual' INTO v_val;
execute immediate 'alter sequence '||i_seq_name||' increment by 1 minvalue 0';
end loop;  
end;

并使用所需值调用(在我的情况下，创建一个名为my_seq的新值(：

SQL> create sequence my_seq;
Sequence created
SQL> select my_seq.nextval from dual;
NEXTVAL
----------
1
SQL> begin
2     pr_set_sequence('my_seq',1203);
3  end;
4  /
PL/SQL procedure successfully completed
SQL> select my_seq.currval from dual;
NEXTVAL
----------
1203

Answer 3

只需执行WHILE循环，从序列中进行选择，直到达到所需的值。速度相当快。

declare
v_sequence_value       number;
begin
while v_sequence_value <= 1203 loop
select my_sequence.nextval into v_sequence_value from dual;
end loop;
end;
/