简单三步轻松实现ORACLE字段自增

当我们在 ORACLE 数据库中插入数据时，很容易遇到需要字段自动递增的情况。下面是一个使用序列和触发器自动递增字段的说明。

一、创建序列

序列是一个虚拟的对象，只是生成一组数字。序列创建后，可以通过 SELECT 语句获取序列的当前值或下一个值。在 ORACLE 中创建序列要使用 CREATE SEQUENCE 语句，语法如下：

CREATE SEQUENCE sequence_name
   [INCREMENT BY integer]
   [START WITH integer]
   [MAXVALUE integer | NOMAXVALUE]
   [MINVALUE integer | NOMINVALUE]
   [CYCLE | NOCYCLE]
   [CACHE integer | NOCACHE];

其中，各参数的含义如下：
- sequence_name：序列的名称
- INCREMENT BY integer：序列的增量，表示每次取到下一个值时增加的数值，默认为1
- START WITH integer：序列的初始值，默认为1
- MAXVALUE integer | NOMAXVALUE：序列的最大值。如果超过这个值，序列就会抛出一个错误。如果省略了这个参数，默认为 1E28。如果设置为 NOMAXVALUE，表示序列没有最大值
- MINVALUE integer | NOMINVALUE：序列的最小值。如果低于这个值，序列就会抛出一个错误。如果省略了这个参数，默认为 1。如果设置为 NOMINVALUE，表示序列没有最小值
- CYCLE | NOCYCLE：表示序列达到最大值时是否重新循环。如果设置为 CYCLE，表示循环；否则，表示不循环
- CACHE integer | NOCACHE：序列缓存的大小。如果设置为 CACHE，表示缓存；否则，表示不缓存。默认值为 20

例如，在创建一个名为 test_sequence，初始值为 1，每次递增 1，最大值为 100 的序列，可以使用以下语句：

CREATE SEQUENCE test_sequence
INCREMENT BY 1
START WITH 1 
MAXVALUE 100;

二、创建触发器

触发器是一种在表上自动执行的存储过程。在 ORACLE 中，使用 CREATE TRIGGER 语句可以创建触发器。触发器可以在 INSERT、DELETE 或 UPDATE 语句执行之前或之后执行某些操作。

可以在 INSERT 语句执行之前创建一个触发器，在插入数据时自动获取序列的下一个值并将其插入到相应的字段中。以下是一个例子：

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT ON test_table 
FOR EACH ROW
BEGIN
    SELECT test_sequence.NEXTVAL INTO :NEW.test_id FROM DUAL;
END;

在这个例子中：
- CREATE OR REPLACE TRIGGER test_trigger：创建一个名为 test_trigger 的触发器，如果同名触发器已存在，则会先删除旧的触发器再创建新的触发器
- BEFORE INSERT ON test_table：指定触发器在插入数据之前执行
- FOR EACH ROW：指定该触发器会对每行数据执行
- SELECT test_sequence.NEXTVAL INTO :NEW.test_id FROM DUAL：获取 test_sequence 序列的下一个值并将其插入到 test_id 字段中

三、示例

示例一：创建一个带有自增列的表，然后插入一条数据

-- 创建一个带有自增列的表
CREATE TABLE test_table (
    test_id NUMBER PRIMARY KEY,
    test_name VARCHAR2(50)
);

-- 创建一个名为 test_sequence 的序列
CREATE SEQUENCE test_sequence
INCREMENT BY 1
START WITH 1;

-- 创建一个名为 test_trigger 的触发器
CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT ON test_table 
FOR EACH ROW
BEGIN
    SELECT test_sequence.NEXTVAL INTO :NEW.test_id FROM DUAL;
END;

-- 插入一条数据
INSERT INTO test_table (test_name) VALUES ('test');

执行以上代码后，查询 test_table 表的数据：

SELECT * FROM test_table;

将会输出：

TEST_ID | TEST_NAME
-------   ---------
      1 | test

示例二：在一个已存在的表中添加自增列

如果要在一个已存在的表中添加自增列，可以使用以下步骤：

-- 1.创建一个名为 test_sequence 的序列
CREATE SEQUENCE test_sequence
INCREMENT BY 1
START WITH 1;

-- 2.创建一个名为 test_trigger 的触发器，并将其绑定到表 test_table 上
CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT ON test_table 
FOR EACH ROW
BEGIN
    SELECT test_sequence.NEXTVAL INTO :NEW.test_id FROM DUAL;
END;

-- 3.创建一个名为 temp_test_table 的表，用于存放复制自 test_table 的数据
CREATE TABLE temp_test_table (
    test_id NUMBER PRIMARY KEY,
    test_name VARCHAR2(50)
);

-- 4.向 temp_test_table 表中插入 test_table 表中的数据
INSERT INTO temp_test_table (test_id, test_name)
SELECT NULL, test_name FROM test_table;

-- 5.删除 test_table 表，以便重新创建该表
DROP TABLE test_table;

-- 6.创建一个名为 test_table 的表，其中包含自增列
CREATE TABLE test_table (
    test_id NUMBER PRIMARY KEY,
    test_name VARCHAR2(50)
);

-- 7.将 temp_test_table 表中的数据复制到 test_table 表中
INSERT INTO test_table (test_id, test_name)
SELECT test_id, test_name FROM temp_test_table;

-- 8.删除 temp_test_table 表
DROP TABLE temp_test_table;

执行以上步骤后，原先没有自增列的表 test_table 现在将会包含一个自增列。