【DB筆試面試513】在Oracle中，如何定義序列？其作用是什麼？有關序列需要注意些什麼？

  • 2019 年 10 月 8 日
  • 筆記

♣

題目部分

在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程式設計師面試筆試寶典》，作者：李華榮。

Tags:

VirMach 便宜 VPS