OCP-1Z0-051 第44题 SEQUENCE的缺省值

一、原题

View the Exhibit and examine the structure of the ORD table.

Evaluate the following SQL statements that are executed in a user session in the specified order:
CREATE SEQUENCE ord_seq;
SELECT ord_seq.nextval
FROM dual;
INSERT INTO ord
VALUES (ord_seq.CURRVAL, '25-jan-2007',101);
UPDATE ord
SET  ord_no= ord_seq.NEXTVAL
WHERE cust_id =101;
What would be the outcome of the above statements?
A. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST_ID 101.
B. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified.
C. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified.
D. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20.

答案：A

二、题目翻译

查看ORD表结构
按顺序执行下面的SQL语句:
上面语句执行后的结果是什么？
A. 所有语句执行成功，对于CUST_ID为101的ORD_NO列的值为2。
B. CREATE SEQUENCE命令不能执行，因为没有指定最大值和最小值。
C. CREATE SEQUENCE命令不能执行，因为起始值和步长值没有指定。
D. 所有语句执行成功，对于CUST_ID为101的ORD_NO列的值为20，因为CACHE默认为20。

三、题目解析

CREATE SEQUENCE时可以省略所有参数，默认起始值为1，步长为1，无上限。
关于D选项，CACHE关键字：
如果建立序列时不指定CACHE和NOCACHE，默认值为CACHE 20，即一次性从序列里取20个数放入内存，如果内存崩溃，则这20个数就会丢失，再取值时从第21个数开始取值，CACHE设置的最小值为2。