如何使用循环更改 Oracle 序列？

Question

希望有人能帮忙。当我尝试将某些内容插入表中时，出现错误，提示主键已存在。所以我需要重置我的序列，使其始终为 max(id)+1。

该表称为“人员”，有 2 列（ID、姓名）。该序列称为SEQ。

我正在考虑做一个循环。要从 Dual 中选择 SEQ.nextval 运行 n 次。这个 n= max(id)-SEQ.currval

这行得通吗？以及如何将其放入语法中？

多谢。

Answer 1

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;

Answer 2

如果这是一次性的，您可以使用更改序列
改变序列sequenceName增量val；
而 val 为最大值+1
然后调用 get nextVal，然后将增量设置回 1。

我将下面的内容放在一起，向您展示如何在不循环的情况下完成它。

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

---

或者更好的是，正如 @Dave 建议的那样，只需删除并使用可接受的 开始 值重新创建序列即可。

Answer 3

有了这个，您就可以同步序列，无论它是在 ID 最大值之前还是之后。

只需要更改代码最后的参数即可。

    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;
    /