Uses and Restrictions of NEXTVAL and CURRVAL

Uses and Restrictions of NEXTVAL and CURRVAL
CURRVAL and NEXTVAL can be used in the following places:

VALUES clause of INSERT statements

The SELECT list of a SELECT statement

The SET clause of an UPDATE statement

CURRVAL and NEXTVAL cannot be used in these places:

A subquery

A view query or materialized view query

A SELECT statement with the DISTINCT operator

A SELECT statement with a GROUP BY 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

需要往测试环境装载大量的仿真数据，每个INSERT有几百万，为了让数据看起来像真的，做了复杂的子查询。结果就发现SEQUENCE不能用了。用起始值+ROWNUM的方法，结束后再调整SEQUENCE的值，

---

这种方法在数据迁移中经常用到。

在数据迁移时，经常会为sequence的字段预留一些空间，在迁入历史数据时，用起始值+rownum的方法实现

 

create sequence s;
select s.nextval from dual;
select s.currval  + s.nextval  + s.nextval  from dual;

select s.currval  , s.nextval , s.nextval  from dual;

 

If any of these locations contains more than one reference to NEXTVAL, then Oracle
increments the sequence once and returns the same value for all occurrences of
NEXTVAL.
If any of these locations contains references to both CURRVAL and NEXTVAL, then
Oracle increments the sequence and returns the same value for both CURRVAL and
NEXTVAL.

放在一起，只引用一次，值是nextvalue
所以值是1和6，见oracle9i SQL Reference Release 2 (9.2) 2-82