Where to Use Sequence Values You can use CURRVAL and NEXTVAL in: The SELECT list of a SELECT statement that is not contained in a subquery, snapshot, or view The SELECT list of a subquery in an INSERT statement The VALUES clause of an INSERT statement The SET clause of an UPDATE statement You cannot use CURRVAL and NEXTVAL: A subquery in a DELETE, SELECT, or UPDATE statement A view's query or snapshot's query A SELECT statement with the DISTINCT operator A SELECT statement with a GROUP BY clause or ORDER BY clause A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator The WHERE clause of a SELECT statement DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement The condition of a CHECK constraint Also, within a single SQL statement that uses CURVAL or NEXTVAL, all referenced LONG columns, updated tables, and locked tables must be located on the same database. How to Use Sequence Values When you create a sequence, you can define its initial value and the increment between its values. The first reference to NEXTVAL returns the sequence's initial value. Subsequent references to NEXTVAL increment the sequence value by the defined increment and return the new value. Any reference to CURRVAL always returns the sequence's current value, which is the value returned by the last reference to NEXTVAL. Note that before you use CURRVAL for a sequence in your session, you must first initialize the sequence with NEXTVAL. Within a single SQL statement, Oracle will increment the sequence only once. If a statement contains more than one reference to NEXTVAL for a sequence, Oracle increments the sequence once and returns the same value for all occurrences of NEXTVAL. If a statement contains references to both CURRVAL and NEXTVAL, Oracle increments the sequence and returns the same value for both CURRVAL and NEXTVAL regardless of their order within the statement. A sequence can be accessed by many users concurrently with no waiting or locking. For information on sequences, see "CREATE SEQUENCE". Example 1 This example selects the current value of the employee sequence: SELECT empseq.currval FROM DUAL;Example 2 This example increments the employee sequence and uses its value for a new employee inserted into the employee table: INSERT INTO emp VALUES (empseq.nextval, 'LEWIS', 'CLERK', 7902, SYSDATE, 1200, NULL, 20);Example 3 This example adds a new order with the next order number to the master order table. It then adds suborders with this number to the detail order table: INSERT INTO master_order(orderno, customer, orderdate) VALUES (orderseq.nextval, 'Al''s Auto Shop', SYSDATE);INSERT INTO detail_order (orderno, part, quantity) VALUES (orderseq.currval, 'SPARKPLUG', 4);INSERT INTO detail_order (orderno, part, quantity) VALUES (orderseq.currval, 'FUEL PUMP', 1);INSERT INTO detail_order (orderno, part, quantity) VALUES (orderseq.currval, 'TAILPIPE', 2);
select seq_ywxh.nextval from dual; select seq_ywxh.currval from dual;查看currval的时候，在此以前必须曾经执行过select seq.nextval操作 如果你只想看看当前值到哪儿而不想失去当前值， 可以查看序列状态：select last_number from user_sequences where sequence_name='YOUR_SEQ';
You can use CURRVAL and NEXTVAL in: The SELECT list of a SELECT statement that is not contained in a subquery, snapshot, or view The SELECT list of a subquery in an INSERT statement The VALUES clause of an INSERT statement The SET clause of an UPDATE statement You cannot use CURRVAL and NEXTVAL: A subquery in a DELETE, SELECT, or UPDATE statement A view's query or snapshot's query A SELECT statement with the DISTINCT operator A SELECT statement with a GROUP BY clause or ORDER BY clause A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator The WHERE clause of a SELECT statement DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement The condition of a CHECK constraint Also, within a single SQL statement that uses CURVAL or NEXTVAL, all referenced LONG columns, updated tables, and locked tables must be located on the same database. How to Use Sequence Values
When you create a sequence, you can define its initial value and the increment between its values. The first reference to NEXTVAL returns the sequence's initial value. Subsequent references to NEXTVAL increment the sequence value by the defined increment and return the new value. Any reference to CURRVAL always returns the sequence's current value, which is the value returned by the last reference to NEXTVAL. Note that before you use CURRVAL for a sequence in your session, you must first initialize the sequence with NEXTVAL. Within a single SQL statement, Oracle will increment the sequence only once. If a statement contains more than one reference to NEXTVAL for a sequence, Oracle increments the sequence once and returns the same value for all occurrences of NEXTVAL. If a statement contains references to both CURRVAL and NEXTVAL, Oracle increments the sequence and returns the same value for both CURRVAL and NEXTVAL regardless of their order within the statement. A sequence can be accessed by many users concurrently with no waiting or locking. For information on sequences, see "CREATE SEQUENCE". Example 1
This example selects the current value of the employee sequence: SELECT empseq.currval
FROM DUAL;Example 2
This example increments the employee sequence and uses its value for a new employee inserted into the employee table: INSERT INTO emp
VALUES (empseq.nextval, 'LEWIS', 'CLERK',
7902, SYSDATE, 1200, NULL, 20);Example 3
This example adds a new order with the next order number to the master order table. It then adds suborders with this number to the detail order table: INSERT INTO master_order(orderno, customer, orderdate)
VALUES (orderseq.nextval, 'Al''s Auto Shop', SYSDATE);INSERT INTO detail_order (orderno, part, quantity)
VALUES (orderseq.currval, 'SPARKPLUG', 4);INSERT INTO detail_order (orderno, part, quantity)
VALUES (orderseq.currval, 'FUEL PUMP', 1);INSERT INTO detail_order (orderno, part, quantity)
VALUES (orderseq.currval, 'TAILPIPE', 2);
select seq_ywxh.currval from dual;查看currval的时候，在此以前必须曾经执行过select seq.nextval操作
如果你只想看看当前值到哪儿而不想失去当前值，
可以查看序列状态：select last_number from user_sequences where sequence_name='YOUR_SEQ';