A/B

【转载】在 Oracle 中设置自增列

2019/10/31 Oracle 转载

【转载】在 Oracle 中设置自增列

如果你经常使用 MySQL，你肯定对 AUTO_INCREMENT 非常熟悉，因为经常要用到它。

一、什么是自增列 ？

自增列是数据库中值随插入的每个行自动增加的一列。它最常用于主键或 ID 字段，这样每次增加一行时，不用指该字段的值，它就会自动增加，而且是唯一的。

当在 MySQL 中定义列时，我们可以指定一个名为 AUTO_INCREMENT 的参数。然后，每当将新值插入此表中时，放入此列的值比最后一个值加 1。

但很不幸，Oracle 没有 AUTO_INCREMENT 功能。 那要如何在Oracle中做到这一点呢？

二、在 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 属性就可以了。

转载信息

  • 作者：nodejh
  • 网址：http://nodejh.com/post/how-to-create-auto-increment-column-in-oracle/

Search

    Table of Contents