如何创建从表中最大值开始的 Oracle 序列?
尝试在 Oracle 中创建一个以特定表中的最大值开始的序列。 为什么这不起作用?
CREATE SEQUENCE transaction_sequence
MINVALUE 0
START WITH (SELECT MAX(trans_seq_no)
FROM TRANSACTION_LOG)
INCREMENT BY 1
CACHE 20;
Trying to create a sequence in Oracle that starts with the max value from a specific table. Why does this not work?
CREATE SEQUENCE transaction_sequence
MINVALUE 0
START WITH (SELECT MAX(trans_seq_no)
FROM TRANSACTION_LOG)
INCREMENT BY 1
CACHE 20;
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(8)
如果您可以使用 PL/SQL,请尝试(编辑:合并 Neil 的 xlnt 建议,从下一个更高的值开始):
要考虑的另一点:通过将 CACHE 参数设置为 20,您将面临在序列中丢失最多 19 个值的风险如果数据库出现故障。 数据库重新启动时缓存的值会丢失。 除非您经常访问该序列,或者您不太关心间隙,否则我会将其设置为 1。
最后一点:您为 CACHE 和 INCRMENT BY 指定的值是默认值。 您可以将它们保留并获得相同的结果。
If you can use PL/SQL, try (EDIT: Incorporates Neil's xlnt suggestion to start at next higher value):
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.
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.
这里我有我的例子,效果很好:
Here I have my example which works just fine:
您可能希望从
max(trans_seq_no) + 1 开始。
注意:
当您创建带有数字的序列时,您必须记住,第一次针对该序列进行选择时,Oracle 将返回初始值您分配给它的值。
如果您尝试做“无间隙”的事情,我强烈建议您
1 不要这样做,并且 #2 不要使用序列。
you might want to start with
max(trans_seq_no) + 1.
watch:
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.
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.
您不能在
CREATE SEQUENCE
语句中使用子选择。 您必须事先选择该值。You can't use a subselect inside a
CREATE SEQUENCE
statement. You'll have to select the value beforehand.在中间,MAX 值将只是承诺值的最大值。 它可能会返回 1234,并且您可能需要考虑有人已经插入了 1235 但未提交。
Bear in mid, the MAX value will only be the maximum of committed values. It might return 1234, and you may need to consider that someone has already inserted 1235 but not committed.
基于 Ivan Laharnar,代码更少且更简单:
Based on Ivan Laharnar with less code and simplier:
使用动态sql
use dynamic sql