SQL 如何在不递增的情况下检索 oracle 序列的当前值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10210273/
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
How to retrieve the current value of an oracle sequence without increment it?
提问by frno
Is there an SQL instruction to retrieve the value of a sequence that does not increment it.
是否有一条 SQL 指令来检索不增加序列的值。
Thanks.
谢谢。
EDIT AND CONCLUSION
编辑和结论
As stated by Justin Cave It's not useful to try to "save" sequence number so
正如 Justin Cave 所说，尝试“保存”序列号是没有用的，所以
select a_seq.nextval from dual;
is good enough to check a sequence value.
足以检查序列值。
I still keep Ollie answer as the good one because it answered the initial question. but ask yourself about the necessity of not modifying the sequence if you ever want to do it.
我仍然认为 Ollie 的答案是好的，因为它回答了最初的问题。但是问问自己，如果您想这样做，是否有必要不修改序列。
回答by Ollie
SELECT last_number
FROM all_sequences
WHERE sequence_owner = '<sequence owner>'
AND sequence_name = '<sequence_name>';
You can get a variety of sequence metadata from user_sequences, all_sequencesand dba_sequences.
您可以从user_sequences、all_sequences和获取各种序列元数据dba_sequences。
These views work across sessions.
这些视图跨会话工作。
EDIT:
编辑：
If the sequence is in your default schema then:
如果序列在您的默认架构中，则：
SELECT last_number
FROM user_sequences
WHERE sequence_name = '<sequence_name>';
If you want all the metadata then:
如果您想要所有元数据，则：
SELECT *
FROM user_sequences
WHERE sequence_name = '<sequence_name>';
Hope it helps...
希望能帮助到你...
EDIT2:
编辑2：
A long winded way of doing it more reliably if your cache size is not 1 would be:
如果您的缓存大小不是 1，那么更可靠的一种冗长的方法是：
SELECT increment_by I
FROM user_sequences
WHERE sequence_name = 'SEQ';
I
-------
1
SELECT seq.nextval S
FROM dual;
S
-------
1234
-- Set the sequence to decrement by
-- the same as its original increment
ALTER SEQUENCE seq
INCREMENT BY -1;
Sequence altered.
SELECT seq.nextval S
FROM dual;
S
-------
1233
-- Reset the sequence to its original increment
ALTER SEQUENCE seq
INCREMENT BY 1;
Sequence altered.
Just beware that if others are using the sequence during this time - they (or you) may get
请注意，如果其他人在此期间使用该序列 - 他们（或您）可能会
ORA-08004: sequence SEQ.NEXTVAL goes below the sequences MINVALUE and cannot be instantiated
Also, you might want to set the cache to NOCACHEprior to the resetting and then back to its original value afterwards to make sure you've not cached a lot of values.
此外，您可能希望NOCACHE在重置之前将缓存设置为 ，然后在重置之后返回其原始值，以确保您没有缓存大量值。
回答by RonK
select MY_SEQ_NAME.currval from DUAL;
select MY_SEQ_NAME.currval from DUAL;
Keep in mind that it only works if you ran select MY_SEQ_NAME.nextval from DUAL;in the current sessions.
请记住，它仅在您select MY_SEQ_NAME.nextval from DUAL;在当前会话中运行时才有效。
回答by georgejo
My original reply was factually incorrect and I'm glad it was removed. The code below will work under the following conditions a) you know that nobody else modified the sequence b) the sequence was modified by your session. In my case, I encountered a similar issue where I was calling a procedure which modified a value and I'm confident the assumption is true.
我原来的回复实际上是不正确的，我很高兴它被删除了。下面的代码将在以下条件下工作 a) 您知道没有其他人修改了序列 b) 您的会话修改了序列。在我的例子中，我遇到了一个类似的问题，我正在调用一个修改值的过程，我相信这个假设是正确的。
SELECT mysequence.CURRVAL INTO v_myvariable FROM DUAL;
Sadly, if you didn't modify the sequence in your session, I believe others are correct in stating that the NEXTVAL is the only way to go.
可悲的是，如果您没有修改会话中的序列，我相信其他人说 NEXTVAL 是唯一的出路是正确的。
回答by Ainsworth
This is not an answer, really and I would have entered it as a comment had the question not been locked. This answers the question:
这不是一个答案，真的，如果问题没有被锁定，我会输入它作为评论。这回答了这个问题：
Why would you want it?
你为什么要它？
Assume you have a table with the sequence as the primary key and the sequence is generated by an insert trigger. If you wanted to have the sequence available for subsequent updates to the record, you need to have a way to extract that value.
假设您有一个以序列作为主键的表，并且序列是由插入触发器生成的。如果您希望序列可用于记录的后续更新，您需要有一种方法来提取该值。
In order to make sure you get the right one, you might want to wrap the INSERT and RonK's query in a transaction.
为了确保您得到正确的查询，您可能希望将 INSERT 和 RonK 的查询包装在一个事务中。
RonK's Query:
RonK 的查询：
select MY_SEQ_NAME.currval from DUAL;
In the above scenario, RonK's caveat does not apply since the insert and update would happen in the same session.
在上述场景中，RonK 的警告不适用，因为插入和更新将发生在同一个会话中。
回答by Roland
I also tried to use CURRVAL, in my case to find out if some process inserted new rows to some table with that sequence as Primary Key. My assumption was that CURRVAL would be the fastest method. But a) CurrVal does not work, it will just get the old value because you are in another Oracle session, until you do a NEXTVAL in your own session. And b) a select max(PK) from TheTableis also very fast, probably because a PK is always indexed. Or select count(*) from TheTable. I am still experimenting, but both SELECTs seem fast.
我还尝试使用 CURRVAL，在我的例子中是为了找出是否某个进程以该序列作为主键将新行插入到某个表中。我的假设是 CURRVAL 将是最快的方法。但是 a) CurrVal 不起作用，它只会获取旧值，因为您在另一个 Oracle 会话中，直到您在自己的会话中执行 NEXTVAL。并且 b) aselect max(PK) from TheTable也非常快，可能是因为 PK 总是被索引的。或者select count(*) from TheTable。我仍在试验中，但两个 SELECT 似乎都很快。
I don't mind a gap in a sequence, but in my case I was thinking of polling a lot, and I would hate the idea of very large gaps. Especially if a simple SELECT would be just as fast.
我不介意序列中的间隙，但就我而言，我正在考虑进行很多轮询，并且我讨厌非常大的间隙的想法。特别是如果一个简单的 SELECT 也一样快。
Conclusion:
结论：
- CURRVAL is pretty useless, as it does not detect NEXTVAL from another session, it only returns what you already knew from your previous NEXTVAL
- SELECT MAX(...) FROM ... is a good solution, simple and fast, assuming your sequence is linked to that table
- CURRVAL 非常没用，因为它不会从另一个会话中检测到 NEXTVAL，它只返回您从之前的 NEXTVAL 中已经知道的信息
- SELECT MAX(...) FROM ... 是一个很好的解决方案，简单快捷，假设您的序列链接到该表
