如何在不增加Oracle序列的情况下检索其当前值?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (59)

是否有SQL指令来检索不增加序列的值。

提问于
用户回答回答于
SELECT last_number
  FROM all_sequences
 WHERE sequence_owner = '<sequence owner>'
   AND sequence_name = '<sequence_name>';

你可以得到各种从序列的元数据user_sequencesall_sequencesdba_sequences

这些观点适用于各个会议。

如果该序列在你的默认模式中,则:

SELECT last_number
  FROM user_sequences
 WHERE sequence_name = '<sequence_name>';

如果你想要所有的元数据,那么:

SELECT *
  FROM user_sequences
 WHERE sequence_name = '<sequence_name>';

希望能帮助到你...

如果你的缓存大小不是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.

只要注意,如果其他人在这段时间内使用序列 - 他们(或你)可能会得到

ORA-08004: sequence SEQ.NEXTVAL goes below the sequences MINVALUE and cannot be instantiated

此外,你可能希望将缓存设置为NOCACHE重置之前的值,然后再恢复到原始值,以确保你没有缓存很多值。

用户回答回答于

select MY_SEQ_NAME.currval from DUAL;

请记住,它只适用select MY_SEQ_NAME.nextval from DUAL;于你在当前会话中运行。

扫码关注云+社区