sql-如何从表的最大值开始创建Oracle序列?
尝试在Oracle中创建一个从特定表的最大值开始的序列。 为什么这不起作用?
CREATE SEQUENCE transaction_sequence
MINVALUE 0
START WITH (SELECT MAX(trans_seq_no)
FROM TRANSACTION_LOG)
INCREMENT BY 1
CACHE 20;
8个解决方案
36 votes
如果可以使用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指定的值是默认值。 您可以不使用它们,而获得相同的结果。
DCookie answered 2020-07-29T18:19:35Z
30 votes
在这里,我有一个很好的例子:
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;
Ivan Laharnar mink.si answered 2020-07-29T18:19:55Z
19 votes
您可能要开始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不使用序列。
Neil Kodner answered 2020-07-29T18:19:06Z
16 votes
您不能在CREATE SEQUENCE语句内使用子选择。 您必须预先选择值。
Chad Birch answered 2020-07-29T18:20:15Z
12 votes
在中间,最大值将仅是承诺值的最大值。 它可能返回1234,并且您可能需要考虑某个人已经插入1235但尚未提交。
Gary Myers answered 2020-07-29T18:20:36Z
4 votes
基于Ivan Laharnar,具有更少的代码和更简单的代码:
declare
lastSeq number;
begin
SELECT MAX(ID) + 1 INTO lastSeq FROM ;
if lastSeq IS NULL then lastSeq := 1; end if;
execute immediate 'CREATE SEQUENCE INCREMENT BY 1 START WITH ' || lastSeq || ' MAXVALUE 999999999 MINVALUE 1 NOCACHE';
end;
dmonti answered 2020-07-29T18:20:56Z
0 votes
DECLARE
v_max NUMBER;
BEGIN
SELECT (NVL (MAX (), 0) + 1) INTO v_max FROM ;
EXECUTE IMMEDIATE 'CREATE SEQUENCE INCREMENT BY 1 START WITH ' || v_max || ' NOCYCLE CACHE 20 NOORDER';
END;
evandertino answered 2020-07-29T18:21:12Z
0 votes
使用动态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;
Samir Ghoneim answered 2020-07-29T18:21:32Z