blocks|key|233402|text|SELECT+last_number
++FROM+all_sequences
+WHERE+sequence_owner+=+'<sequence+owner>'
+++AND+sequence_name+=+'<sequence_name>';|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|233403|您可以从user_sequences、all_sequences和dba_sequences获取各种序列元数据。|unstyled|offset|length|style|CODE|233404|这些视图可以跨会话工作。|233405|编辑：|BOLD|233406|如果序列在您的默认模式中，则：|233407|SELECT+last_number
++FROM+user_sequences
+WHERE+sequence_name+=+'<sequence_name>';|233408|如果你想要所有的元数据，那么：|233409|SELECT+*
++FROM+user_sequences
+WHERE+sequence_name+=+'<sequence_name>';|233410|希望这能帮上忙。|233411|EDIT2:|233412|如果缓存大小不是1，一种更可靠的方法是：|233413|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.|233414|只需注意，如果其他人在这段时间内使用序列-他们(或您)可能会得到|233415|ORA-08004:+sequence+SEQ.NEXTVAL+goes+below+the+sequences+MINVALUE+and+cannot+be+instantiated|233416|此外，您可能希望在重置之前将缓存设置为NOCACHE，然后将其设置回原始值，以确保您没有缓存大量的值。|233417|entityMap^0|0|4|E|J|D|X|D|0|0|0|3|0|0|0|0|0|0|0|6|0|0|0|0|0|J|7|0^^$0|@$1|2|3|4|5|6|7|1D|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|1E|8|@$G|1F|H|1G|I|J]|$G|1H|H|1I|I|J]|$G|1J|H|1K|I|J]]|9|@]|A|$]]|$1|K|3|L|5|F|7|1L|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|1M|8|@$G|1N|H|1O|I|O]]|9|@]|A|$]]|$1|P|3|Q|5|F|7|1P|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|1Q|8|@]|9|@]|A|$B|C]]|$1|T|3|U|5|F|7|1R|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|1S|8|@]|9|@]|A|$B|C]]|$1|X|3|Y|5|F|7|1T|8|@]|9|@]|A|$]]|$1|Z|3|10|5|F|7|1U|8|@$G|1V|H|1W|I|O]]|9|@]|A|$]]|$1|11|3|12|5|F|7|1X|8|@]|9|@]|A|$]]|$1|13|3|14|5|6|7|1Y|8|@]|9|@]|A|$B|C]]|$1|15|3|16|5|F|7|1Z|8|@]|9|@]|A|$]]|$1|17|3|18|5|6|7|20|8|@]|9|@]|A|$B|C]]|$1|19|3|1A|5|F|7|21|8|@$G|22|H|23|I|J]]|9|@]|A|$]]|$1|1B|3|-4|5|F|7|24|8|@]|9|@]|A|$]]]|1C|$]]

<pre><code>SELECT last_number
 FROM all_sequences
 WHERE sequence_owner = '&lt;sequence owner&gt;'
 AND sequence_name = '&lt;sequence_name&gt;';
</code></pre>

You can get a variety of sequence metadata from <code>user_sequences</code>, <code>all_sequences</code> and <code>dba_sequences</code>.

These views work across sessions.

EDIT:

If the sequence is in your default schema then:

<pre><code>SELECT last_number
 FROM user_sequences
 WHERE sequence_name = '&lt;sequence_name&gt;';
</code></pre>

If you want all the metadata then:

<pre><code>SELECT *
 FROM user_sequences
 WHERE sequence_name = '&lt;sequence_name&gt;';
</code></pre>

Hope it helps...

EDIT2:

A long winded way of doing it more reliably if your cache size is not 1 would be:

<pre><code>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.
</code></pre>

Just beware that if others are using the sequence during this time - they (or you) may get 

<pre><code>ORA-08004: sequence SEQ.NEXTVAL goes below the sequences MINVALUE and cannot be instantiated
</code></pre>

Also, you might want to set the cache to <code>NOCACHE</code> prior to the resetting and then back to its original value afterwards to make sure you've not cached a lot of values.

blocks|key|230981|text|select+MY_SEQ_NAME.currval+from+DUAL;|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|230982|请记住，只有在当前会话中运行select+MY_SEQ_NAME.nextval+from+DUAL;时，它才会起作用。|230983|entityMap^0|0|11|0|E|11|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|P|8|@]|D|@]|E|$]]]|I|$]]

<code>select MY_SEQ_NAME.currval from DUAL;</code>

Keep in mind that it only works if you ran <code>select MY_SEQ_NAME.nextval from DUAL;</code> in the current sessions.

blocks|key|231979|text|我最初的回复是不正确的，我很高兴它被删除了。下面的代码将在以下情况下工作:+a)您知道没有其他人修改序列；b)您的会话修改了序列。在我的例子中，我遇到了一个类似的问题，我调用了一个修改了一个值的过程，我确信这个假设是正确的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|231980|SELECT+mysequence.CURRVAL+INTO+v_myvariable+FROM+DUAL;|code-block|syntax|javascript|231981|遗憾的是，如果您没有在会话中修改序列，我相信其他人的说法是正确的，即NEXTVAL是唯一的方法。|231982|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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.

<pre><code>SELECT mysequence.CURRVAL INTO v_myvariable FROM DUAL;
</code></pre>

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.

blocks|key|231009|text|这不是一个答案，真的，如果问题没有被锁定，我会把它作为评论输入的。这回答了这个问题：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|231010|你为什么想要它？|231011|假设您有一个表，其中序列作为主键，并且序列是由插入触发器生成的。如果您希望序列可用于记录的后续更新，则需要有一种方法来提取该值。|231012|为了确保获得正确的查询，您可能希望将INSERT和RonK的查询包装在一个事务中。|231013|RonK的查询：|231014|select+MY_SEQ_NAME.currval+from+DUAL;|code-block|syntax|javascript|231015|在上面的场景中，RonK的警告不适用，因为插入和更新将在同一个会话中发生。|231016|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|U|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|V|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|W|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|X|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

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.

RonK's Query:

<pre><code>select MY_SEQ_NAME.currval from DUAL;
</code></pre>

In the above scenario, RonK's caveat does not apply since the insert and update would happen in the same session.

blocks|key|233499|text|我还尝试使用CURRVAL，在我的例子中，用来查看是否有某个进程将新行插入到某个表中，并将该序列作为主键。我的假设是CURRVAL是最快的方法。但是a)+CurrVal不起作用，它只会获取旧值，因为您在另一个Oracle会话中，直到您在自己的会话中执行NEXTVAL。b)+select+max(PK)+from+TheTable也很快，可能是因为PK总是被索引的。或者select+count(*)+from+TheTable。我还在尝试，但这两个选择似乎都很快。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|233500|我不介意序列中的空位，但在我的例子中，我考虑了很多轮询，我讨厌非常大空位的想法。特别是如果一个简单的SELECT也同样快的话。|233501|结论：|233502|233503|CURRVAL非常无用，因为它不会从另一个会话中检测到NEXTVAL，它只返回您从之前的NEXTVAL|unordered-list-item|233504|SELECT+MAX(...)中已经知道的内容。从..。是一个很好的解决方案，简单而快速，假设您的序列链接到表|233505|233506|entityMap^0|3T|S|55|T|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|X|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|Y|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]|$1|K|3|L|5|M|7|10|8|@]|D|@]|E|$]]|$1|N|3|O|5|M|7|11|8|@]|D|@]|E|$]]|$1|P|3|-4|5|6|7|12|8|@]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|13|8|@]|D|@]|E|$]]]|R|$]]

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 <code>select max(PK) from TheTable</code> is also very fast, probably because a PK is always indexed. Or <code>select count(*) from TheTable</code>. I am still experimenting, but both SELECTs seem fast.

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.

Conclusion:

<ul>
<li>CURRVAL is pretty useless, as it does not detect NEXTVAL from another session, it only returns what you already knew from your previous NEXTVAL</li>
<li>SELECT MAX(...) FROM ... is a good solution, simple and fast, assuming your sequence is linked to that table</li>
</ul>

blocks|key|232048|text|下面是常用的用法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|232049|select+field_SQ.nextval+from+dual;++--+it+will+increase+the+value+by+1+for+each+run
select+field_SQ.currval+from+DUAL;|code-block|syntax|javascript|232050|然而，下面的代码能够将序列更改为您所期望的。1可以是整数(负数或正数)|offset|length|style|CODE|232051|alter+sequence+field_SQ+increment+by+1+minvalue+0|232052|entityMap^0|0|0|M|1|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

The follows is often used:
<pre><code>select field_SQ.nextval from dual; -- it will increase the value by 1 for each run
select field_SQ.currval from DUAL;
</code></pre>
However the following is able to change the sequence to what you expected. The <code>1</code> can be an integer (negative or positive)
<pre><code>alter sequence field_SQ increment by 1 minvalue 0
</code></pre>

blocks|key|233543|text|如果你的用例是一些后端代码插入一条记录，那么相同的代码想要检索最后的插入id，而不指望任何底层的数据访问库预设函数来做到这一点，那么，正如其他人所提到的，你应该只需要使用SEQ_MY_NAME.NEXTVAL为你想要的列(通常是主键)来构造你的SQL查询，然后从后端运行语句SELECT+SEQ_MY_NAME.CURRVAL+FROM+dual。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|233544|记住，CURRVAL只有在NEXTVAL被优先调用的情况下才是可调用的，这在上面的策略中都是自然完成的……|233545|entityMap^0|2D|J|3T|10|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|P|8|@]|D|@]|E|$]]]|I|$]]

If your use case is that some backend code inserts a record, then the same code wants to retrieve the last insert id, without counting on any underlying data access library preset function to do this, then, as mentioned by others, you should just craft your SQL query using <code>SEQ_MY_NAME.NEXTVAL</code> for the column you want (usually the primary key), then just run statement <code>SELECT SEQ_MY_NAME.CURRVAL FROM dual</code> from the backend.
Remember, CURRVAL is only callable if NEXTVAL has been priorly invoked, which is all naturally done in the strategy above...

Is there an SQL instruction to retrieve the value of a sequence that does not increment it.

Thanks.

EDIT AND CONCLUSION 

As stated by Justin Cave It's not useful to try to "save" sequence number so

<pre><code>select a_seq.nextval from dual;
</code></pre>

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.

How to retrieve the current value of an oracle sequence without increment it?

是否有SQL指令来检索不递增的序列的值。谢谢。编辑和结论正如Justin Cave所说，尝试“保存”序列号是没有用的，因此select a_seq.nextval from dual;足够好来检查序列值。我仍然保留Ollie answer作为好答案，因为它回答了最初的问题。但是问问你自己，如果你想要修改序列，那么不修改...

问如何在不增加的情况下检索oracle序列的当前值？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在不增加的情况下检索oracle序列的当前值？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在不增加的情况下检索oracle序列的当前值？
EN