SQL 如何从表中创建一个以最大值开始的 Oracle 序列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/798766/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create an Oracle sequence starting with max value from a table?
提问by Amar Patel
Trying to create a sequence in Oracle that starts with the max value from a specific table. Why does this not work?
尝试在 Oracle 中创建一个序列，该序列以特定表中的最大值开始。为什么这不起作用？
CREATE SEQUENCE transaction_sequence
MINVALUE 0
START WITH (SELECT MAX(trans_seq_no)
FROM TRANSACTION_LOG)
INCREMENT BY 1
CACHE 20;
采纳答案by Neil Kodner
you might want to start with max(trans_seq_no) + 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
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.
当您创建一个带有数字的序列时，您必须记住第一次对序列进行选择时，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.
If you're trying to do the 'gapless' thing, I strongly advise you to
如果您正在尝试做“无间隙”的事情，我强烈建议您
1 not do it, and #2 not use a sequence for it.
1 不做，#2 不使用序列。
回答by DCookie
If you can use PL/SQL, try (EDIT: Incorporates Neil's xlnt suggestion to start at next higher value):
如果您可以使用 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;
Another point to consider: By setting the CACHE parameter to 20, you run the risk of losing up to 19 values in your sequence if the database goes down. CACHEd values are lost on database restarts. Unless you're hitting the sequence very often, or, you don't care that much about gaps, I'd set it to 1.
要考虑的另一点：通过将 CACHE 参数设置为 20，如果数据库出现故障，您将面临丢失序列中多达 19 个值的风险。CACHEd 值在数据库重新启动时丢失。除非您经常点击序列，或者您不太在意间隙，否则我会将其设置为 1。
One final nit: the values you specified for CACHE and INCREMENT BY are the defaults. You can leave them off and get the same result.
最后一点：您为 CACHE 和 INCREMENT BY 指定的值是默认值。您可以不使用它们并获得相同的结果。
回答by Ivan Laharnar mink.si
Here I have my example which works just fine:
这里我有我的例子，它工作得很好：
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;
回答by Chad Birch
You can't use a subselect inside a CREATE SEQUENCEstatement. You'll have to select the value beforehand.
您不能在CREATE SEQUENCE语句中使用子选择。您必须事先选择该值。
回答by Gary Myers
Bear in mid, the MAX value will only be the maximum of committedvalues. It might return 1234, and you may need to consider that someone has already inserted 1235 but not committed.
在中间，MAX 值将只是承诺值的最大值。它可能会返回 1234，您可能需要考虑有人已经插入了 1235 但没有提交。
回答by dmonti
Based on Ivan Laharnar with less code and simplier:
基于 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;
回答by evandertino
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;
回答by Samir Ghoneim
use dynamic sql
使用动态sql
BEGIN
DECLARE
maxId NUMBER;
BEGIN
SELECT MAX(id)+1
INTO maxId
FROM table_name;
execute immediate('CREATE SEQUENCE sequane_name MINVALUE '||maxId||' START WITH '||maxId||' INCREMENT BY 1 NOCACHE NOCYCLE');
END;
END;
