动态设置 Oracle 序列的“开始于”值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2811968/
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
Set "Start With" value for Oracle sequence dynamically
提问by Allan
I'm trying to create a release script that can be deployed on multiple databases, but where the data can be merged back together at a later date. The obvious way to handle this is to set the sequence numbers for production data sufficiently high in subsequent deployments to prevent collisions.
我正在尝试创建一个可以部署在多个数据库上的发布脚本，但可以在以后将数据合并在一起。处理这个问题的显而易见的方法是在后续部署中将生产数据的序列号设置得足够高，以防止冲突。
The problem is in coming up with a release script that will accept the environment number and set the "Start With" value of the sequences appropriately. Ideally, I'd like to use something like this:
问题在于提出一个发布脚本，该脚本将接受环境编号并适当地设置序列的“开始于”值。理想情况下，我想使用这样的东西：
ACCEPT EnvironNum PROMPT 'Enter the Environment Number: '
--[more scripting]
CREATE SEQUENCE seq1 START WITH &EnvironNum*100000;
--[more scripting]
This doesn't work because you can't evaluate a numeric expression in DDL.
这不起作用，因为您无法在 DDL 中计算数字表达式。
Another option is to create the sequences using dynamic SQL via PL/SQL.
另一种选择是通过 PL/SQL 使用动态 SQL 创建序列。
ACCEPT EnvironNum PROMPT 'Enter the Environment Number: '
--[more scripting]
EXEC execute immediate 'CREATE SEQUENCE seq1 START WITH ' || &EnvironNum*100000;
--[more scripting]
However, I'd prefer to avoid this solution as I generally try to avoid issuing DDL in PL/SQL.
但是，我更愿意避免使用此解决方案，因为我通常会尽量避免在 PL/SQL 中发出 DDL。
Finally, the third option I've come up with is simply to accept the Start With value as a substitution variable, instead of the environment number.
最后，我想出的第三个选项是简单地接受 Start With 值作为替代变量，而不是环境编号。
Does anyone have a better thought on how to go about this?
有没有人对如何解决这个问题有更好的想法？
回答by Vincent Malgrat
you can use the COLUMN XX NEW_VALUE YYsyntax to perform calculation in SQL*Plus and store the result in a variable:
您可以使用COLUMN XX NEW_VALUE YY语法在 SQL*Plus 中执行计算并将结果存储在变量中：
SQL> col sequence_num new_value seq
SQL> select &EnvironNum * 1000000 sequence_num from dual;
Enter value for environnum: 2
old 1: select &EnvironNum * 1000000 sequence_num from dual
new 1: select 2 * 1000000 sequence_num from dual
SEQUENCE_NUM
------------
2000000
SQL> create sequence scott.seq1 start with &seq;
old 1: create sequence scott.seq1 start with &seq
new 1: create sequence scott.seq1 start with 2000000
Sequence created.
回答by dpbradley
If you have a reasonably limited number of databases you could start the sequences with a different values and then define an increment so that the sequence values do not collide. This would eliminate the expression in the start value.
如果您的数据库数量有限，您可以使用不同的值开始序列，然后定义一个增量，以便序列值不会冲突。这将消除起始值中的表达式。
So if you have 10 databases:
因此，如果您有 10 个数据库：
create sequence seq1 start with &startval increment by 10;
and startval is 1 for database 1, 2 for database 2, etc.
并且 startval 为数据库 1 为 1，数据库 2 为 2，等等。
(This also eliminates the problem of sequences overlapping if the increment values grow into the next database's range)
（如果增量值增长到下一个数据库的范围，这也消除了序列重叠的问题）
回答by FrustratedWithFormsDesigner
One trick I've used is to create an sqlplus script from the main script and then execute it:
我使用的一个技巧是从主脚本创建一个 sqlplus 脚本，然后执行它：
maybe something like
也许像
ACCEPT EnvironNum PROMPT 'Enter the Environment Number: '
spool seq_script.sql
begin
dbms_output.put_line('CREATE SEQUENCE seq1 START WITH '||&EnvironNum||'*100000;')
end;
spool off
@seq_script.sql
This should create a script file with &EnvironNumalready evaluated (assuming user inputed '275', for example):
这应该创建一个&EnvironNum已经评估过的脚本文件（假设用户输入了“275”，例如）：
CREATE SEQUENCE seq1 START WITH 275*100000;
