在使用Oracle序列的currval和nextval时的限制

创建一个序列

create sequence test_seq

minvalue 1

maxvalue 10000000

start with 1

increment by 1

cache 20;

在delete,select,update语句的子查询中不能使用sequence的值

SQL>  delete from test_jy where test_id select  test_jy.*,test_seq.currval from test_jy where test_id=1

2  intersect

3  select  test_jy.*,test_seq.currval from test_jy where test_id=2;

select  test_jy.*,test_seq.currval from test_jy where test_id=1

intersect

select  test_jy.*,test_seq.currval from test_jy where test_id=2

ORA-02287: 此处不允许序号

SQL> select  test_jy.*,test_seq.currval from test_jy where test_id=1

2  minus

3  select  test_jy.*,test_seq.currval from test_jy where test_id=2;

select  test_jy.*,test_seq.currval from test_jy where test_id=1

minus

select  test_jy.*,test_seq.currval from test_jy where test_id=2

ORA-02287: 此处不允许序号

在select语句中的where子句中

SQL> select  test_jy.* from test_jy where test_id