如何使用循环更改 Oracle 序列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5817545/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to change the Oracle Sequence using loop?
提问by user729147
Hope someone can help. When I tried to insert something into a table it give me error saying the primary key is already existed. So I need to reset my sequence so that it is always max(id)+1.
希望有人能帮忙。当我尝试在表中插入一些东西时，它给我错误说主键已经存在。所以我需要重置我的序列，使其始终为 max(id)+1。
The table is called 'People' with 2 columns (ID, Name). The sequence is called SEQ.
该表称为“人员”，有 2 列（ID、名称）。该序列称为 SEQ。
I am thinking of doing a loop. To run select SEQ.nextval from dual for n times. this n= max(id)-SEQ.currval
我正在考虑做一个循环。运行 select SEQ.nextval from dual n 次。这 n= max(id)-SEQ.currval
Wwill this work? and how Can I put it into the syntax?
这会起作用吗？以及如何将其放入语法中？
Thanks a lot.
非常感谢。
回答by Frank Schmitt
declare
l_MaxVal pls_integer;
l_Currval pls_integer default - 1;
begin
select max(id)
into l_MaxVal
from people;
while l_Currval < l_Maxval
loop
select my_seq.nextval
into l_Currval
from dual;
end loop;
end;
回答by Harrison
If this is a one off, you can use the alter sequence alter sequence sequenceName increment by val ; whereas val is +1 to the maximum then call get nextVal, then set the increment back to 1.
如果这是一次性的，您可以使用 alter sequence alter sequence sequenceName increment by val ；而 val 是 +1 到最大值然后调用 get nextVal，然后将增量设置回 1。
I threw the below together to show you how it can be done without looping.
我将下面的内容放在一起，向您展示如何在不循环的情况下完成。
create sequence changeValue start with 18 increment by 1 nocache ;
select changeValue.nextval from dual ;
/
NEXTVAL
----------------------
18
set serveroutput on
declare
maxVal number := 24 ;
curVal number ;
diffVal number ;
incrementVal number ;
procedure alterSequence(seqName in varchar2, incVal in number) as
s varchar2(500);
begin
s := 'alter sequence ' || seqName || ' increment by ' || incVal ;
dbms_output.put_line(s);
execute immediate s;
end alterSequence;
begin
--(done in 11gr2 so if in earlier version select into)
curVal := changeValue.currval ;
dbms_output.put_line('curValue=>' || curVal );
diffVal := maxVal - curVal ;
dbms_output.put_line('diffVal=>' || diffVal );
alterSequence ( 'changeValue' , diffVal + 1 );
incrementVal := changeValue.nextval ;
dbms_output.put_line('incrementVal=>' || incrementVal );
alterSequence ( 'changeValue' , 1 );
curVal := changeValue.currval ;
dbms_output.put_line('curValue=>' || curVal );
end ;
/
curValue=>18
diffVal=>6
alter sequence changeValue increment by 7
incrementVal=>25
alter sequence changeValue increment by 1
curValue=>25
or better yet, as @Dave suggests, just drop and recreate the sequence with the acceptable Start Withvalue.
或者更好的是，正如@Dave 建议的那样，只需删除并使用可接受的Start With值重新创建序列。
回答by Anderson Acosta
With this one you can synchronize the sequence whatever it is forward or behind the max of the ID.
有了这个，您可以同步序列，无论它是在 ID 最大值之前还是之后。
Just need to change the parameters in the final of the code.
只需要更改代码最后的参数即可。
declare
procedure SYNC_SEQUENCE
( P_IN_SEQ in varchar2
, P_IN_TABLE in varchar2
, P_IN_ID in varchar2
)
is
LV_MAXVAL number := 0;
LV_CURRVAL number := -1;
LV_AUX NUMBER;
begin
execute immediate
'select max('||P_IN_ID||')
from '||P_IN_TABLE into LV_MAXVAL;
execute immediate
'select '||P_IN_SEQ||'.nextval
from dual ' into LV_CURRVAL;
if LV_MAXVAL < LV_CURRVAL then
LV_AUX := (LV_CURRVAL - LV_MAXVAL);
execute immediate
'ALTER SEQUENCE '||P_IN_SEQ||' INCREMENT BY -'||LV_AUX;
execute immediate
'SELECT '||P_IN_SEQ||'.NEXTVAL FROM dual' INTO LV_AUX;
execute immediate
'ALTER SEQUENCE '||P_IN_SEQ||' INCREMENT BY 1';
end if;
while LV_CURRVAL < LV_MAXVAL
loop
execute immediate
'select '||P_IN_SEQ||'.nextval
from dual ' into LV_CURRVAL;
end loop;
end SYNC_SEQUENCE;
begin
SYNC_SEQUENCE('MY_SEQUENCIE_NAME','MY_TABLE_NAME','MY_FIELD_ID_NAME');
end;
/
