oracle 使用 START WITH 从查询创建序列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3461267/
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
Create a Sequence with START WITH from Query
提问by Victor
How can I create a Sequence where my START WITH value comes from a query?
如何创建 START WITH 值来自查询的序列？
I'm trying this way:
CREATE SEQUENCE "Seq" INCREMENT BY 1 START WITH (SELECT MAX("ID") FROM "Table");
我正在尝试这种方式：
CREATE SEQUENCE "Seq" INCREMENT BY 1 START WITH (SELECT MAX("ID") FROM "Table");
But, I get the ORA-01722 error
但是，我收到了 ORA-01722 错误
回答by Rajesh Chamarthi
The START WITH CLAUSE accepts an integer. You can form the "Create sequence " statement dynamically and then execute it using execute immediate to achieve this.
START WITH CLAUSE 接受一个整数。您可以动态地形成“创建序列”语句，然后使用立即执行来执行它来实现此目的。
declare
l_new_seq INTEGER;
begin
select max(id) + 1
into l_new_seq
from test_table;
execute immediate 'Create sequence test_seq_2
start with ' || l_new_seq ||
' increment by 1';
end;
/
Check out these links.
查看这些链接。
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_6014.htm
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_6014.htm
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm
回答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;
