如何验证 Oracle 序列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4596220/
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 verify Oracle sequences
提问by Jon Strayer
We have a customer who does {something}, and after that some of our sequences are returning numbers that have already been used. While the long term answer would be for them to stop doing {something}, I need a simple way to check the sequences against the tables they are used in.
我们有一个客户做了{某事}，之后我们的一些序列返回已经使用过的数字。虽然长期的答案是让他们停止做{某事}，但我需要一种简单的方法来根据他们使用的表检查序列。
I can query user_sequencesto get the last_numberfor each sequence and I can get the max(id_number)for each table. But when I try to do both in the same query I get nulls back.
我可以查询user_sequences以获取last_number每个序列的 ，也可以获取max(id_number)每个表的 。但是，当我尝试在同一个查询中同时执行这两项操作时，我会返回空值。
My broken SQL is:
我损坏的 SQL 是：
select max(last_number) , max(id_number) from user_sequences,
squiggly.ACCOUNT_CODE_DEFINITION where sequence_name = 'ACCOUNT_CODE_DEFINITION_SEQ'
and sequence_owner = 'SQUIGGLY' ;
回答by Vincent Malgrat
you can get the MAX from both tables with this query:
您可以使用此查询从两个表中获取 MAX：
SELECT (SELECT last_number
FROM all_sequences
WHERE sequence_name = 'ACCOUNT_CODE_DEFINITION_SEQ'
AND sequence_owner = 'SQUIGGLY') max_sequence,
(SELECT MAX(id_number)
FROM squiggly.ACCOUNT_CODE_DEFINITION) max_id_number
FROM dual
回答by rishwinger
I would suggest never trust "last_number" of user_sequences because if Cache is enabled while creating the sequence , then the last_number is likely to contain a value greater than current value of the sequence.
我建议永远不要相信 user_sequences 的“last_number”，因为如果在创建序列时启用了缓存，那么 last_number 可能包含一个大于序列当前值的值。
Well ,i used below steps
好吧，我使用了以下步骤
1) select <seq_name>.nextval from dual;
2) select <seq_name>.currval from dual;
Since u can't execute currval alone for the first time , so i executed nextval first.
由于你第一次不能单独执行 currval，所以我先执行了 nextval。
SQL> create sequence seq;
Sequence created.
SQL> select last_number from user_sequences;
LAST_NUMBER
-----------
1
SQL> select seq.nextval from dual;
NEXTVAL
----------
1
SQL> select seq.currval from dual;
CURRVAL
----------
1
SQL> select last_number from user_sequences;
LAST_NUMBER
-----------
21
SQL> select seq.currval from dual;
CURRVAL
----------
1
回答by ssedano
seq.nextval will work but also increment the sequence. If you already called nextval in the current session you can call seq.currval.
seq.nextval 将起作用，但也会增加序列。如果您已经在当前会话中调用了 nextval，则可以调用 seq.currval。
If you call currval before you call nextval it will throw an exception.
如果在调用 nextval 之前调用 currval，它将引发异常。
I would do:select last_number from user_sequences where sequence_name = 'seq'
我会做：select last_number from user_sequences where sequence_name = 'seq'
回答by ykatchou
try to use seq.nextval :)
尝试使用 seq.nextval :)
Look here : http://www.techonthenet.com/oracle/sequences.php
看这里：http: //www.techonthenet.com/oracle/sequences.php
It's not a good pratice to use the Max (in case of the last row have beed deleted !).
使用 Max 不是一个好习惯（以防最后一行被删除！）。
