Oracle主键自增

Oracle主键自增

1、建表

CREATE TABLE TEST_TABLE
(
	ID INT NOT NULL,
	NAME VARCHAR2(40) NULL,
	AGE VARCHAR2(40) NULL
);

2、设置主键

ALTER TABLE TEST_TABLE ADD CONSTRAINT TEST_TABLE_PK PRIMARY KEY (ID);

3、新建序列

CREATE SEQUENCE TEST_TABLE_AUTOINC_SEQ
MINVALUE 1
NOMAXVALUE
INCREMENT BY 1
START WITH 1
NOCACHE;

4、新建触发器

CREATE OR REPLACE TRIGGER  TEST_TABLE_AUTOINC_TG
BEFORE INSERT ON TEST_TABLE FOR EACH ROW 
BEGIN
	SELECT TEST_TABLE_AUTOINC_SEQ.NEXTVAL INTO:NEW.ID FROM DUAL;
END;

5、测试

INSERT INTO TEST_TABLE (NAME,AGE)VALUES('测试1','18');
INSERT INTO TEST_TABLE (NAME,AGE)VALUES('测试2','19');
INSERT INTO TEST_TABLE (NAME,AGE)VALUES('测试3','20');

6、结果

SELECT * FROM TEST_TABLE;