初识Oracle中的sequence

注：学习环境：Oracle 10g R2 10.2.0.1 64bit
今天才刚刚开始学习oracle中的sequence,以前在MS SQL Server中都是直接在创建表的时候指定
栏位类型为identity即可。呵呵，早就听说oracle中使用序列来产生唯一码。今天学习。发现果然有多点不同

创建sequence的语句为：
create sequence seq1 increment by 1 start with 1 nomaxvalue/maxvalue xx nocycle/cycle cache 20/nocache

实验过程如下：
1：不要任何参数创建sequence
harvey@SALES>create sequence seq1;

Sequence created.

harvey@SALES>select seq1.nextval from dual;

   NEXTVAL
----------
         1

harvey@SALES>select seq1.nextval from dual;

   NEXTVAL
----------
         2
2：指定递增因子创建sequence
harvey@SALES>create sequence seq2 increment by 1;

Sequence created.

harvey@SALES>select seq2.nextval from dual;

   NEXTVAL
----------
         1

harvey@SALES>select seq2.nextval from dual;

   NEXTVAL
----------
         2
3：指定开始基数创建sequence

harvey@SALES>create sequence seq3 start with 1;

Sequence created.

harvey@SALES>select seq3.nextval from dual;

   NEXTVAL
----------
         1

harvey@SALES>select seq3.nextval from dual;

   NEXTVAL
----------
         2
4：指定最大值创建sequence----注意：此处实验超过最大值会提示错误
harvey@SALES>create sequence seq4 maxvalue 10;

Sequence created.

harvey@SALES>select seq4.nextval from dual;

   NEXTVAL
----------
         1
.....................
......................
harvey@SALES>select seq4.nextval from dual;

   NEXTVAL
----------
        10

harvey@SALES>select seq4.nextval from dual;
select seq4.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence SEQ4.NEXTVAL exceeds MAXVALUE and cannot be instantiated

5：指定最大值并指定循环----------也提示错误：cache缓存中存储的seq数量必须小于一个循环
harvey@SALES>create sequence seq5 maxvalue 10 cycle;
create sequence seq5 maxvalue 10 cycle
*
ERROR at line 1:
ORA-04013: number to CACHE must be less than one cycle

harvey@SALES>create sequence seq5 maxvalue 5 cycle nocache;

Sequence created.

harvey@SALES>select seq5.nextval from dual;

   NEXTVAL
----------
         1

harvey@SALES>select seq5.nextval from dual;

   NEXTVAL
----------
         5

harvey@SALES>select seq5.nextval from dual;

   NEXTVAL
----------
         1
至此发现了有关于sequence的一些参数的默认值。同时查询了一下oracle 10g 的concept文档进行了验证。得到结果如下：
1：start参数。默认从1 开始
2：increment 参数默认为1
3：对于参数nomaxvalue/maxvalue abc   默认取值为：nomaxvalue
注：如果序列的数值达到最大值。则继续调用seq.nextval 则oracle会提示错误：
ORA-08004: sequence SEQ4.NEXTVAL exceeds MAXVALUE and cannot be instantiated 
4：对于参数nocycle/cycle   默认取值为：nocycle，既：序列的取值不循环，
注：如果指定为循环。则必须指定sequence的最大值maxvalue。否则提示错误：
ORA-04015: ascending sequences that CYCLE must specify MAXVALUE
5：对于参数cache abc /nocache   默认取值为：cache 20,即默认一次缓存20个序列号到SGA中。如果20个值全用完，则会产生下一组20个号码到SGA中供用户使用
注：如果指定seq需要循环。并且seq具有缓存特性时，那么seq的一次缓存的序列号必须小于一个循环内包含的个数否则会提示错误  ORA-04013: number to CACHE must be less than one cycle

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/180324/viewspace-661543/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/180324/viewspace-661543/