【DB笔试面试513】在Oracle中，如何定义序列？其作用是什么？有关序列需要注意些什么？

♣

题目部分

在Oracle中，如何定义序列？其作用是什么？有关序列需要注意些什么？

♣

答案部分

在很多数据库系统中，都存在一个自动增长的列，如果想要在Oracle中实现自动增长的功能，那么只能依靠序列完成。序列通常具有如下的特性：

（1）自动提供唯一的数值。

（2）共享对象。

（3）主要用于提供主键值。

（4）代替应用代码。

（5）将序列值装入内存可以提高访问效率。

序列的创建语法如下所示：

CREATE SEQUENCE SEQ_NAME
       [INCREMENT BY n]
       [START WITH n]
       [{MAXVALUE n | NOMAXVALUE}]
       [{MINVALUE n | NOMINVALUE}]
       [{CYCLE | NOCYCLE}]
       [{CACHE n | NOCACHE}]
 [{ORER | NOORDER}] 
;

在以上代码中，

l SEQ_NAME是序列的名字。

l INCREMENT BY n指定序列号之间的间隔，n是一个整数（如果该子句被省略，那么序列增量为1）。

l START WITH n指定要产生的第一个序列数（如果该子句被省略，那么序列从1开始）。

l MAXVALUE n指定序列能产生的最大值。

l NOMAXVALUE对于升序序列指定1027为最大值，对于降序序列指定-1为最大值（这是默认选项）。

l MINVALUE n指定最小序列值。

l NOMINVALUE对于升序序列指定1为最小值，对于降序序列指定-(1026)为最小值（这是默认选项）。

l CYCLE|NOCYCLE指定序列在达到它的最大或最小值之后，是否继续产生（NOCYCLE是默认选项）。

l CACHE n|NOCACHE指定Oracle服务器预先分配多少值保持到内存中（默认值为20）。如果INCREMENT BY值是负数，那么序列是降序。

l ORDER|NOORDER选项可用，ORDER可以保证序列值按顺序产生，该选项在RAC环境中可以起作用。

以下是一个创建序列的例子：

CREATE SEQUENCE LHR_SEQ 
START WITH 100 
INCREMENT BY 1;

上例创建了一个名称为LHR_SEQ的序列，从100开始，每次增长1，通过如下的SQL可以查到序列的下一个值是多少：

SYS@lhrdb> SELECT LHR_SEQ.NEXTVAL FROM DUAL;
   NEXTVAL
----------
       100

关于序列需要注意以下几点内容：

①　对于设置了CYCLE属性的SEQUENCE来说，当SEQUENCE的值达到最大值后会从1开始循环。

②　创建序列后，不能使用CURRVAL直接查询当前值，必须使用NEXTVAL先取值才能使用，否则报ORA-08002: sequence MY_SEQ.CURRVAL is not yet defined in this session，且第一次执行NEXTVAL后当前值并不增加。

可以通过查询数据字典视图USER_SEQUENCES获取序列的定义信息，NEXTVAL返回序列中下一个有效的值，任何用户都可以引用CURRVAL中存放的序列的当前值。

建立一张表以验证序列的操作。

CREATE TABLE TESTSEQ(
      NEXT    NUMBER,
      CURR    NUMBER 
);

CREATE SEQUENCE MYSEQ

START WITH 1

INCREMENT BY 1;

下面向表中添加数据，添加数据的时候需要手工使用序列。使用序列如下所示：

INSERT INTO TESTSEQ(NEXT,CURR) VALUES (MYSEQ.NEXTVAL,MYSEQ.CURRVAL);

INSERT INTO TESTSEQ(NEXT,CURR) VALUES (MYSEQ.NEXTVAL,MYSEQ.CURRVAL);

INSERT INTO TESTSEQ(NEXT,CURR) VALUES (MYSEQ.NEXTVAL,MYSEQ.CURRVAL);

INSERT INTO TESTSEQ(NEXT,CURR) VALUES (MYSEQ.NEXTVAL,MYSEQ.CURRVAL);

INSERT INTO TESTSEQ(NEXT,CURR) VALUES (MYSEQ.NEXTVAL,MYSEQ.CURRVAL);

SYS@lhrdb> SELECT * FROM TESTSEQ;

NEXT CURR

---------- ----------

1 1

2 2

3 3

4 4

5 5

另外，当序列值不符合实际需求时，可以执行ALTER SEQUENCE命令修改序列，需要注意的是，启动序列号不可以修改（START WITH N）。

修改序列的语法和序列的创建语法一样，只是将CREATE修改为ALTER即可，如下所示：

ALTER SEQUENCE SQE_NAME

[increment by n]

[{maxvalue n | nomaxvalue}]

[{minvalue n | nominvalue}]

[{cycle | nocycle}]

[{cache n | nocache}]

以下示例的作用是将序列LHR_SEQ的递增步长修改为10。

SYS@lhrdb> ALTER SEQUENCE LHR_SEQ

2 INCREMENT BY 10

3 MAXVALUE 9999

4 CACHE 10;

Sequence altered.

SYS@lhrdb> SELECT LHR_SEQ.NEXTVAL FROM DUAL;

NEXTVAL

----------

109

SYS@lhrdb> SELECT LHR_SEQ.NEXTVAL FROM DUAL;

NEXTVAL

----------

119

& 说明：

有关Oracle序列的更多内容介绍可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2136049/

有关序列等待事件的案例：http://blog.itpub.net/26736162/viewspace-2127465/

有关序列Cache值过小的案例：http://blog.itpub.net/26736162/viewspace-2123996/

真题1、CREATE SEQUENCE seq1 START WITH 100 INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE.The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:

SELECT seq1.nextval FROM dual.

What is displayed by the SELECT statement?

A、1B、10C、100D、an error

答案：A。

题目“has generated numbers up to the maximum limit of 200”表示序列SEQ1当前已经是最大值200，且创建属性是CYCLE的，所以，NEXTVAL的值从1开始。注意：即便是START WITH 100，使用CYCLE属性的序列还是从1开始的。

所以，本题的答案为A。

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。