在Oracle中設置自增列

3月 13, 2018

在Oracle中設置自增列

https://github.com/nodejh/nodejh.github.io/issues/33

二、在Oracle 11g 中設置自增字段

1. 創建表

首先創建一張用於測試的表：

CREATE TABLE "TEST" (
    ID NUMBER(11) PRIMARY KEY,
    NAME VARCHAR2(50BYTE) NOT NULL
);

2. 創建序列

然後創建一個名為TEST_ID_SEQ的序列（序列名稱自己隨意設定）：

CREATE SEQUENCE TEST_ID_SEQ
INCREMENT BY 1
START WITH 100
MAXVALUE 999999999
NOCYCLE
NOCACHE;

如果要刪除序列，可以使用下面的SQL 命令：

DROP SEQUENCE TEST_ID_SEQ;

對SEQUENCE的一些說明：

INCREMENT BY 用於指定序列增量（默認值：1），如果指定的是正整數，則序列號自動遞增，如果指定的是負數，則自動遞減。
START WITH 用於指定序列生成器生成的第一個序列號，當序列號順序遞增時默認值為序列號的最小值，當序列號順序遞減時默認值為序列號的最大值。
MAXVALUE用於指定序列生成器可以生成的組大序列號（必須大於或等於START WITH，並且必須大於MINVALUE），默認為NOMAXVALUE。
MINVALUE用於指定序列生成器可以生成的最小序列號（必須小於或等於START WITH，並且必須小於MAXVALUE），默認值為NOMINVALUE。
CYCLE用於指定在達到序列的最大值或最小值之後是否繼續生成序列號，默認為NOCYCLE。
CACHE 用於指定在內存中可以預分配的序列號個數（默認值：20）。

到這一步其實就已經可以實現字段自增，只要插入的時候，將ID的值設置為序列的下一個值TEST_ID_SEQ.NEXTVAL就可以了：

SQL> INSERT INTO "TEST" ("ID", "NAME") VALUES (TEST_ID_SEQ.NEXTVAL, 'name1');
SQL> INSERT INTO "TEST" ("ID", "NAME") VALUES (TEST_ID_SEQ.NEXTVAL, 'name2');
SQL> INSERT INTO "TEST" ("ID", "NAME") VALUES (TEST_ID_SEQ.NEXTVAL, 'name3');
SQL> SELECT * FROM "TEST";

ID   NAME
---  ------
100 name1
101 name2
102 name3

為了簡化插入操作，我們還可以創建一個觸發器，當將數據插入到"TEST" 表的時候，自動將最新的ID 插入進去。

3. 創建觸發器

CREATE OR REPLACE TRIGGER TEST_ID_SEQ_TRG
BEFORE INSERT ON "TEST"
FOR EACH ROW
WHEN (NEW."ID" IS NULL)
BEGIN
  SELECT TEST_ID_SEQ.NEXTVAL
  INTO :NEW."ID"
  FROM DUAL;
END;

這樣的話，每次寫插入語句，只需要將ID字段的值設置為NULL它就會自動遞增了：

SQL> INSERT INTO "TEST" ("ID", "NAME") VALUES (NULL, 'name4');
SQL> INSERT INTO "TEST" ("ID", "NAME") VALUES (NULL, 'name5');
SQL> INSERT INTO "TEST" ("ID", "NAME") VALUES (NULL, 'name6');
SQL> SELECT * FROM "TEST";

ID   NAME
---  ------
100 name1
101 name2
102 name3
103 name4
104 name5
105 name6

4. 一些值得注意的地方

4.1 插入指定ID

如果某條插入語句指定了ID的值如：

SQL> INSERT INTO "TEST" ("ID", "NAME") VALUES (1000, 'name1001');
SQL> SELECT * FROM "TEST";

ID   NAME
---  ------
100 name1
101 name2
102 name3
103 name4
104 name5
1000 name1001

那麼下次ID還是會在原來的基礎上繼續增加：

SQL> INSERT INTO "TEST" ("ID", "NAME") VALUES (NULL, 'name1001');
SQL> SELECT * FROM "TEST";

ID   NAME
---  ------
100 name1
101 name2
102 name3
103 name4
104 name5
1000 name1001

但當序列的值到了1000的時候，如果ID允許重複，就會有兩行記錄ID都為1000。

但如果ID設置為了主鍵，如本文的例子ID NUMBER(11) PRIMARY KEY，則插入就會報錯：

Error : ORA-00001: unique constraint (SOFTWARE.SYS_C0014995) violated

4.2 字段加引號

在SQL 語句中，字段最好都加上引號，不然可能會報錯：

Error : ORA-00900: invalid SQL statement

或：

ORA-24344: Success with Compilation Error

4.3 SQUENCE

第一次NEXTVAL返回的是初始值；隨後的NEXTVAL會自動增加INCREMENT BY對應的值，然後返回增加後的值。
CURRVAL總是返回當前SEQUENCE的值，但是在第一次NEXTVAL初始化之後才能使用CURRVAL，否則會出錯。
一次NEXTVAL會增加一次SEQUENCE的值，所以如果在同一個語句裡面使用多個NEXTVAL，其值就是不一樣的。
如果指定CACHE值，Oracle就可以預先在內存裡面放置一些SEQUENCE，這樣存取的快些。CACHE裡面的取完後，Oracle自動再取一組到CACHE。
但使用CACHE或許會跳號，比如數據庫突然不正常關閉（shutdown abort)，CACHE中的SEQUENCE就會丟失。所以可以在CREATE SEQUENCE的時候用NOCACHE防止這種情況。

4.4 性能

在數據庫操作中，觸發器的使用耗費系統資源相對較大。如果對於表容量相對較小的表格我們可以忽略觸發器帶來的性能影響。

考慮到大表操作的性能問題，需要盡可能的減少觸發器的使用。對於以上操作，就可以拋棄觸發器的使用，直接手動調用序列函數即可，但這樣可能在程序維護上稍微帶來一些不便。

三、在Oracle 12c 中設置自增字段

在Oracle 12c中設置自增字段就簡單多了，因為ORacle 12c提供了IDENTITY屬性：

CREATE TABLE "TEST" (
    ID NUMBER(11) GENERATED BY DEFAULT ON NULL AS IDENTITY,
    NAME VARCHAR2(50BYTE) NOT NULL
);

這樣就搞定了！和MySQL 一樣簡單！

🤣

四、總結

所以如上所屬，在Oracle 中設置自增字段，需要根據不同的版本使用不同的方法：

在Oracle 11g 中，需要先創建序列（SQUENCE）再創建一個觸發器（TRIGGER）。
在Oracle 12c中，只需要使用IDENTITY屬性就可以了。

搜尋此網誌

ZUZU