오라클 시퀀스의 현재 값을 증가시키지 않고 검색하는 방법은 무엇입니까?

오라클 시퀀스의 현재 값을 증가시키지 않고 검색하는 방법은 무엇입니까?

---

증가하지 않는 시퀀스 값을 검색하는 SQL 명령이 있습니까?

감사.

편집 및 결론

Justin Cave가 말했듯이 시퀀스 번호를 "저장"하는 것은 유용하지 않으므로

select a_seq.nextval from dual;

시퀀스 값을 확인하기에 충분합니다.

나는 Ollie가 여전히 초기 질문에 대답했기 때문에 좋은 답변으로 유지합니다. 그러나 시퀀스를 수정하지 않으려는 경우 자신에게 물어보십시오.

---

SELECT last_number
  FROM all_sequences
 WHERE sequence_owner = '<sequence owner>'
   AND sequence_name = '<sequence_name>';

당신의 순서 다양한 메타 데이터를 얻을 수 있습니다 user_sequences, all_sequences하고 dba_sequences.

이 뷰는 여러 세션에서 작동합니다.

편집하다:

시퀀스가 기본 스키마에있는 경우 :

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

모든 메타 데이터를 원한다면 :

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

그것이 도움이되기를 바랍니다 ...

EDIT2 :

캐시 크기가 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 NOCACHE prior to the resetting and then back to its original value afterwards to make sure you've not cached a lot of values.

---

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.

---

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.

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.

---

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:

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.

---

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 TheTable is also very fast, probably because a PK is always indexed. Or select count(*) from TheTable. 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:

• 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

참고URL : https://stackoverflow.com/questions/10210273/how-to-retrieve-the-current-value-of-an-oracle-sequence-without-increment-it