设置“开始于” Oracle 序列的动态值

Question

我正在尝试创建一个可以部署在多个数据库上的发布脚本，但数据可以在以后重新合并在一起。处理此问题的明显方法是在后续部署中将生产数据的序列号设置得足够高，以防止冲突。

问题在于提出一个发布脚本，该脚本将接受环境编号并适当地设置序列的“开始于”值。理想情况下，我想使用这样的东西：

ACCEPT EnvironNum PROMPT 'Enter the Environment Number:  '
--[more scripting]
CREATE SEQUENCE seq1 START WITH &EnvironNum*100000;
--[more scripting]

这不起作用，因为您无法计算 DDL 中的数字表达式。

另一种选择是通过 PL/SQL 使用动态 SQL 创建序列。

ACCEPT EnvironNum PROMPT 'Enter the Environment Number:  '
--[more scripting]
EXEC execute immediate 'CREATE SEQUENCE seq1 START WITH ' || &EnvironNum*100000;
--[more scripting]

但是，我宁愿避免这种解决方案，因为我通常会尝试避免在 PL/SQL 中发出 DDL。

最后，我提出的第三个选项是简单地接受 Start With 值作为替换变量，而不是环境号。

有人对如何解决这个问题有更好的想法吗？

Answer 1

您可以使用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.

Answer 2

如果您的数据库数量相当有限，则可以使用不同的值启动序列，然后定义增量，以便序列值不会发生冲突。这将消除起始值中的表达式。

因此，如果您有 10 个数据库：

create sequence seq1 start with &startval increment by 10;

数据库 1 的 startval 为 1，数据库 2 的 startval 为 2，依此类推

（这也消除了增量值增长到下一个数据库范围时序列重叠的问题）

Answer 3

我使用的一个技巧是从主脚本创建一个 sqlplus 脚本，然后执行它：

也许类似

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

这样应该创建一个已评估 &EnvironNum 的脚本文件（假设用户输入“275” ， 例如）：

CREATE SEQUENCE seq1 START WITH 275*100000;