Oracle中Sequence使用的限制

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Oracle中Sequence使用的限制，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3098字，纯文字阅读大概需要5分钟。

内容图文

在使用Oracle序列的currval和nextval时的限制,在delete,select,update语句的子查询中不能使用sequence的值

在使用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_seq.currval from dual);

delete from test_jy where test_id <(select test_seq.currval from dual)

ORA-02287: 此处不允许序号

SQL> select * from test_jy where test_id <(select test_seq.currval from dual);

select * from test_jy where test_id <(select test_seq.currval from dual)

ORA-02287: 此处不允许序号

SQL> update test_jy set test_id=0 where test_id <(select test_seq.currval from dual);

update test_jy set test_id=0 where test_id <(select test_seq.currval from dual)

ORA-02287: 此处不允许序号

在查询视图或物化视图时

SQL> select a.* from test_v a where a.userid<test_seq.currval ;

select a.* from test_v a where a.userid<test_seq.currval

ORA-02287: 此处不允许序号

带有distinct操作符的select语句不能使用

SQL> select distinct a.*,test_seq.currval from test_v a ;

select distinct a.*,test_seq.currval from test_v a

ORA-02287: 此处不允许序号

有group by,order by操作的select语句不能使用

SQL> select test_jy.*,test_seq.currval from test_jy group by test_jy.test_id;

select test_jy.*,test_seq.currval from test_jy group by test_jy.test_id

ORA-02287: 此处不允许序号

SQL> select test_jy.*,test_seq.currval from test_jy order by test_jy.test_id;

select test_jy.*,test_seq.currval from test_jy order by test_jy.test_id

ORA-02287: 此处不允许序号

有UNION, INTERSECT, MINUS操作符的语句不能使用

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

2 union

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

union

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 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<test_seq.currval;

select test_jy.* from test_jy where test_id<test_seq.currval

ORA-02287: 此处不允许序号

在create table或alter table语句的中default值是不能使用sequence

SQL> alter table test_jy modify test_id number(20) default test_seq.currval;

alter table test_jy modify test_id number(20) default test_seq.currval

ORA-00984: 列在此处不允许

还有就在check约束中不能使用

，

内容总结

以上是互联网集市为您收集整理的Oracle中Sequence使用的限制全部内容，希望文章能够帮你解决Oracle中Sequence使用的限制所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---