Oracle中Sequence使用的限制

在使用Oracle序列的currval和nextval时的限制创建一个序列create sequence test_seqminvalue 1maxvalue 10000000start with 1increment by 1cache 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.currvalORA-02287: 此处不允许序号带有distinct操作符的select语句不能使用SQL> select distinct a.*,test_seq.currval from test_v a ;select distinct a.*,test_seq.currval from test_v aORA-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_idORA-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_idORA-02287: 此处不允许序号有UNION, INTERSECT, MINUS操作符的语句不能使用SQL> select test_jy.*,test_seq.currval from test_jy where test_id=12 union3 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=1unionselect test_jy.*,test_seq.currval from test_jy where test_id=2ORA-02287: 此处不允许序号SQL> select test_jy.*,test_seq.currval from test_jy where test_id=12 intersect3 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=1intersectselect test_jy.*,test_seq.currval from test_jy where test_id=2ORA-02287: 此处不允许序号SQL> select test_jy.*,test_seq.currval from test_jy where test_id=12 minus3 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=1minusselect test_jy.*,test_seq.currval from test_jy where test_id=2ORA-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.currvalORA-02287: 此处不允许序号在create table或alter table语句的中default值是不能使用sequenceSQL> 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.currvalORA-00984: 列在此处不允许还有就在check约束中不能使用Oracle启动实例时使用参数文件的顺序Data Gurad物理备份方式下重命名数据文件相关资讯 Oracle使用 Oracle Sequence

Oracle 使用小计（03/19/2015 09:07:06）
Oracle 批量更新sequence的存储（09/11/2014 06:17:44）
Oracle和MySQL分别生成sequence序（05/14/2014 21:24:53）

Oracle中quote的使用（12/22/2014 18:23:31）
Oracle ErrorStack 使用和阅读详解（07/01/2014 21:14:16）
Oracle创建自增字段sequence （03/12/2013 09:20:16）

本文评论查看全部评论（0）