序列
序列像表、视图一样,被称为数据库对象,它可以产生1、2、3、4……等等顺序增加的有序数。当然,也可以是1,3,5,7……,也可以由大到小。只要是有序数列,都可以有序列产生。下面我们看一下序列的创建和使用。
二、序列的创建
序列的创建语法如下:
CREATE SEQUENCE 序列名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
INCREMENT BY n :序列增量,这里的n取值是一个整数。如果n为2,序列将已2为单位自增,比如1,3,5,7……。如果省略此子句,默认的自增量将是1。
START WITH n :序列的起始值。默认为1。
MAXVALUE n | NOMAXVALUE :MAXVALUE n是序列的最大值。NOMAXVALUE是系统自定最大值,通常升序的最大值ORACLE将会设为10的27次方,降序的最大值是-1。
MINVALUE n | NOMINVALUE : MINVALUE n序列的最小值。NOMINVALUE和上面的选项一样,是系统自定最小值。升序的最小值是1。降序的是负的10的26次方,即-(10的26次方)。
CYCLE | NOCYCLE :在序列到“头”后,也就是达到最大值、或最小值后,是否又回到序列的起始值。
CACHE n | NOCACHE :CACHE n 的作用是ORACLE事先生成n个序列数,保存在内存中,等用户需要时取用。NOCACHE不在内存中事先生成序列数,每次用户使用到序列的下一个数时,当场为用户生成。这样的速度不如CACHE n快。CACHE n是按序列的顺序,一次生成多个数,放在内存中,等待用户取用。NOCACHE是用一个生成一个。ORACLE的默认值,是CACHE 20。也就是在缓存中一次生成20个序列数供用户取用。
以上就是序列的创建语法,下面我们创建一个从5开始,每次增加1,最大值是 20的序列:
2 minvalue 3
3 maxvalue 20
4 start with 5
5 increment by 1
6 nocycle;
Sequence created.
有一个数据字典视图,可以显示用户创建的序列信息:
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER
------------------------------ ---------- ---------- ------------ -----------
SEQ_PREPAY 3 20 1 5
三、序列使用
序列已经创建好了,如何从序列中生成顺序的数呢?
序列名.NEXTVAL ,让序列自增,并取得自增后的值。
序列名.CURRVAL ,序列不自增,仅取得序列的当前值。
注意在序列刚刚建成后,在数据库仅存有序列的定义,还没有生成任何序列数,这时不能调用CURRVAL,例如,我的seq_prepay刚刚建成:
select seq_prepay.currval from dual
*
ERROR at line 1:
ORA-08002: sequence SEQ_PREPAY.CURRVAL is not yet defined in this session
这个错误的意思就是序列的值还没有生成。下面我调用NEXTVAL一次:
NEXTVAL
----------
5
序列已经有了第5个值,再调用CURRVAL也可以有结果了:
CURRVAL
----------
5
CURRVAL你无论调用多少次,不会引起序列的自增,我可以再显示一次CURRVAL,显示的结果还是5。但是,每调用一次NEXTVAL,序列都会自增一次,并返回自增结果,seq_prepay序列当前是5,我再次调用NEXTVAL,序列的值将变为6:
NEXTVAL
----------
6
每次自增1。这时再调用CURRVAL,值将变为6。
注释:
打开一个新会话后,若首先没有执行一次 select seq_prepay.nextval from dual前就先执行select seq_prepay.currval from dual,则就会出现错误ORA-08002: sequence SEQ_PREPAY.CURRVAL is not yet defined in this session,因为在该会话的内存上还没有存储该序列的当前值,而执行一次select seq_prepay.nextval from dual后,该会话的内存上则就将seq_prepay.nextval值赋值给该序列的当前值变量了。
seq_prepay序列是NOCYCLE,即当达到最大值后不绕回,下面我们多调用NEXTVAL几次,seq_prepay的最大值是20,当达到序值为20后,再次调用NEXTVAL将会报出如下错误:
select seq_prepay.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence SEQ_PREPAY.NEXTVAL exceeds MAXVALUE and cannot be instantiated
我们已经看到了序列的使用,那么,序列都可以用在什么地方呢?我们可以把序列的NEXTVAL或CURRVAL放在INSERT的VALUES中,把序列值插入进表,也可以放在UPDATE set后,用序列值更新表。序列也就是可以放在这些地方了。能够使用序列的地方并不多,在WHERE中,在创建表的DEFAULT中,等等,除了上面我们所提到的,其他地方都不能使用序列。
四、修改序列
Oracle中所有修改元数据的命令,都是以ALTER开头,序列也不例外。修改序列的命令是:
ALTER SEQUENCE 序列名 各选项 。
除了START WITH不能修改外,创建序列时,其他所有的选项都可以修改。例如,我将seq_prepay序列改为可绕回:
Sequence altered.
再调用NEXTVAL,已经绕回到最小值3,而不是起始值5了:
NEXTVAL
----------
3
五、序列的空隙
序列通常都是按顺序生成,比如上面的seq_prepay,按5、6、7,…… 的顺序,通常不会将谁跳过去。但有种情况下,可能会出现你本次调用NEXTVAL时,序列值是6,下一次再调用NEXTVAL时,却变为10了。7、8、9、都被跳了过去,这就是序列中的空隙。
空隙的原因,很大程度是上CACHE引起的。
假设刚刚将CACHE 设为4 ,序列当前值是5,下一次调用NEXTVAL时,返回值是6,但同时,ORACLE会自动的沿着序列的顺序,生成7、8、9 、10三个序列值。并将这4个值放进缓存中。5已经被调取了,下一次调用NEXTVAL时,将到缓存中把6取出,再下一次到缓存中取7。7之后再调用NEXTVAL,。。。一直调到10将再次生成4个值,存进缓存中。这样做的目的,是为了加快序列生成顺序数的效率,但这可能会生成空隙。比如说现在序列值是6,在缓存中有6、7、8、9四个序列值,这四个值已经生成了。如果此时停电了,内存中的数据被清空了。再次启动数据库后,调用NEXTVAL将返回10,因为7、8、9这3个值刚才已经生成过了。下面我们试一下:
步1:确认当前序列值是5
NEXTVAL
----------
5
步2:将CACHE设为4
gyj@OCM> alter sequence seq_prepay cache 4;
Sequence altered.
步3:再次调用NEXTVAL
NEXTVAL
----------
6
gyj@OCM> select sequence_name,min_value,max_value, increment_by,last_number from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER
------------------------------ ---------- ---------- ------------ -----------
SEQ_PREPAY 3 20 1 10
应该是10了。这就是CACHE在起的作用。也就是说序列的当前值已经是10了。缓存了6、7、8、9四个
步4:登录具有特殊权限的用户,以最突然的方式关闭数据库,再打开数据库。
Connected.
sys@OCM> shutdown abort;
ORACLE instance shut down.
sys@OCM> startup
ORACLE instance started.
Total System Global Area 1071333376 bytes
Fixed Size 1341312 bytes
Variable Size 528484480 bytes
Database Buffers 536870912 bytes
Redo Buffers 4636672 bytes
Database mounted.
Database opened.
步5:重新登录到GYJ用户:
调用NEXTVAL:
NEXTVAL
----------
10
除了这种空隙外,还有人为的空隙,如果有两个表,都从同一个序列中调用NEXTVAL,获得顺序值并插入到自己的列中。可以想像一下,这样做两个表中的序列值肯定会有空隙。
序列中的空隙并没什么,但是你应该知道序列中是有空隙的。如果你要求表中的某一列是顺序增长的数字,但不能有空隙,建议你就不要使用序列了。
六、删除序列
DROP SEQUENCE 序列名。命令很简单。
Sequence dropped.
注释小结:
1、为带有(绑定)变量的SQL语句创建一个视图,则在执行创建时提示要为该(绑定)变量输入值,
这个说明视图最终对应的SQL语句都是不含有(绑定)变量的。
2、
在创建视图时,对这种使用各种函数,或运算表达式的列,一定要起别名,如没有别名视图创建就会失败。
3、
定义视图的列名可以省略,来自SELECT定义中的列名,如果定义视图的列名不省略,那个列的个数与SELECT定义中的列的个数要一致(两者的顺序无所谓)。