7 回答

• 4

  你可能想从 max(trans_seq_no) + 1. 开始

  看：

  SQL> create table my_numbers(my_number number not null primary key);
  
  Table created.
  
  SQL> insert into my_numbers(select rownum from user_objects);
  
  260 rows created.
  
  SQL> select max(my_number) from my_numbers;
  
  MAX(MY_NUMBER)
  --------------
             260
  
  SQL> create sequence my_number_sn start with 260;
  
  Sequence created.
  
  SQL> insert into my_numbers(my_number) values (my_number_sn.NEXTVAL);
  insert into my_numbers(my_number) values (my_number_sn.NEXTVAL)
  *
  ERROR at line 1:
  ORA-00001: unique constraint (NEIL.SYS_C00102439) violated
  

  当您使用数字创建序列时，您必须记住，第一次选择序列时，Oracle将返回您为其分配的初始值 .

  SQL> drop sequence my_number_sn;
  
  Sequence dropped.
  
  SQL> create sequence my_number_sn start with 261;
  
  Sequence created.
  
  SQL>  insert into my_numbers(my_number) values (my_number_sn.NEXTVAL);
  
  1 row created.
  

  如果你想做'无间隙'的事情，我强烈建议你

  1没有这样做，而＃2没有使用序列 .

  回复于 2024-04-25T15:50:30+08:00
• 36

  如果您可以使用PL / SQL，请尝试（编辑：将Neil的xlnt建议纳入下一个更高的值）：

  SELECT 'CREATE SEQUENCE transaction_sequence MINVALUE 0 START WITH '||MAX(trans_seq_no)+1||' INCREMENT BY 1 CACHE 20'
    INTO v_sql
    FROM transaction_log;
  
  EXECUTE IMMEDIATE v_sql;
  

  需要考虑的另一点：通过将CACHE参数设置为20，如果数据库出现故障，您可能会在序列中丢失多达19个值 . 数据库重新启动时CACHEd值将丢失 . 除非你经常按顺序进行，否则，你不关心差距，我会把它设置为1 .

  最后一项：您为CACHE和INCREMENT BY指定的值是默认值 . 您可以将它们关闭并获得相同的结果 .

  回复于 2024-04-25T15:50:30+08:00
• 0

  在这里，我的示例工作得很好：

  declare
   ex number;
  begin
    select MAX(MAX_FK_ID)  + 1 into ex from TABLE;
    If ex > 0 then
      begin
              execute immediate 'DROP SEQUENCE SQ_NAME';
        exception when others then
          null;
      end;
      execute immediate 'CREATE SEQUENCE SQ_NAME INCREMENT BY 1 START WITH ' || ex || ' NOCYCLE CACHE 20 NOORDER';
    end if;
  end;
  

  回复于 2024-04-25T15:50:30+08:00
• 28

  您不能在 CREATE SEQUENCE 语句中使用子选择 . 您必须事先选择该值 .

  回复于 2024-04-25T15:50:30+08:00
• 12

  承受中期，MAX值只会是 committed 值的最大值 . 它可能会返回1234，您可能需要考虑某人已插入1235但未提交 .

  回复于 2024-04-25T15:50:30+08:00
• 16

  基于Ivan Laharnar，代码更少，更简单：

  declare
      lastSeq number;
  begin
      SELECT MAX(ID) + 1 INTO lastSeq FROM <TABLE_NAME>;
      if lastSeq IS NULL then lastSeq := 1; end if;
      execute immediate 'CREATE SEQUENCE <SEQUENCE_NAME> INCREMENT BY 1 START WITH ' || lastSeq || ' MAXVALUE 999999999 MINVALUE 1 NOCACHE';
  end;
  

  回复于 2024-04-25T15:50:30+08:00
• 19

  DECLARE
      v_max NUMBER;
  BEGIN
      SELECT (NVL (MAX (<COLUMN_NAME>), 0) + 1) INTO v_max FROM <TABLE_NAME>;
      EXECUTE IMMEDIATE 'CREATE SEQUENCE <SEQUENCE_NAME> INCREMENT BY 1 START WITH ' || v_max || ' NOCYCLE CACHE 20 NOORDER';
  END;
  

  回复于 2024-04-25T15:50:30+08:00