sql - 如何创建从表中的最大值开始的 Oracle 序列？

Question

尝试在 Oracle 中创建一个序列，该序列以特定表中的最大值开始。为什么这不起作用？

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

Answer 1

您可能想从 max(trans_seq_no) + 1. 开始
watch :

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 不使用序列。