【oracle学习笔记】序列(sequence)学习

显示全部楼层 · 2013-10-28 09:15:00

oracle学习之序列(sequence)学习
一、创建序列
SQL> create sequence lz
2increment by 1
3start with 1
4nomaxvalue
5nocycle
6cache 10;
Sequence created.
取得序列值：
SQL> SELECT lz.NEXTVAL FROM dual;
NEXTVAL
----------
1
使序列值增加到3
SQL> SELECT lz.CURRVAL FROM dual;
CURRVAL
----------
1
SQL> SELECT lz.NEXTVAL FROM dual;
NEXTVAL
----------
2
SQL> SELECT lz.NEXTVAL FROM dual;
NEXTVAL
----------
3
固定序列值并查询
SQL> SELECT lz.CURRVAL FROM dual;
CURRVAL
----------
3
SQL> SELECT lz.CURRVAL FROM dual;
CURRVAL
----------
3
SQL> SELECT lz.CURRVAL FROM dual;
CURRVAL
----------
3
SQL>
二、序列值不连续的问题
1、查询现在的序列值
SQL> SELECT lz.CURRVAL FROM dual;
CURRVAL
----------
3
2、重启数据库
SQL> startup force;
ORACLE instance started.
Total System Global Area285212672 bytes
Fixed Size
1267068 bytes
Variable Size
104860292 bytes
Database Buffers
176160768 bytes
Redo Buffers
2924544 bytes
Database mounted.
Database opened.
SQL> SELECT lz.NEXTVAL FROM dual;
NEXTVAL
----------
11
SQL>
查到的是11，因为在创建序列lz的语句中有 cache 10，表示每次缓存10个序列值在内存中，这样就会加速应用程序对序列的访问。都是数据库重启的时候，缓存的序列值丢失了，所以，每次重启数据库后查询到的序列值都是不连续的。使用order参数，可以使序列值按照请求的先后增加
SQL> create sequence lz
2increment by 1
3start with 1
4nomaxvalue
5order
6nocycle
7cache 20;
SQL> startup force;
ORACLE instance started.
Total System Global Area285212672 bytes
Fixed Size
1267068 bytes
Variable Size
104860292 bytes
Database Buffers
176160768 bytes
Redo Buffers
2924544 bytes
Database mounted.
Database opened.
SQL> SELECT lz.NEXTVAL FROM dual;
NEXTVAL
----------
21
SQL> startup force;
ORACLE instance started.
Total System Global Area285212672 bytes
Fixed Size
1267068 bytes
Variable Size
104860292 bytes
Database Buffers
176160768 bytes
Redo Buffers
2924544 bytes
Database mounted.
Database opened.
SQL> SELECT lz.NEXTVAL FROM dual;
NEXTVAL
----------
41
三、删除序列值，
SQL> drop sequence lz;
Sequence dropped.
如果需要重置序列值为1，就需要删除重建序列，使用 start with 1 命令！