如何从表的最大值开始创建Oracle序列? [英] How to create an Oracle sequence starting with max value from a table?

问题描述

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

Trying to create a sequence in Oracle that starts with the max value from a specific table. Why does this not work?

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

推荐答案

您可能要以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将返回您为其分配的初始值.

When you create a sequence with a number, you have to remember that the first time you select against the sequence, Oracle will return the initial value that you assigned it.

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.

如果您尝试做无间隙"的事情，我强烈建议您

If you're trying to do the 'gapless' thing, I strongly advise you to

这篇关于如何从表的最大值开始创建Oracle序列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！