首页 文章

如何从表中创建以max值开头的Oracle序列?

提问于
浏览 1780
42

尝试在Oracle中创建一个以特定表的最大值开头的序列 . 为什么这不起作用?

CREATE SEQUENCE transaction_sequence
  MINVALUE 0
  START WITH (SELECT MAX(trans_seq_no)
     FROM TRANSACTION_LOG) 
  INCREMENT BY 1
  CACHE 20;

7 回答

  • 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指定的值是默认值 . 您可以将它们关闭并获得相同的结果 .

  • 28

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

  • 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;
    
  • 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;
    
  • 12

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

  • 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没有使用序列 .

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

相关问题