Oracle使用序列+触发器实现表自增主键

1.建表

-- Create table 创建表 test
create table TEST
(
  testid NUMBER not null,
  name   NVARCHAR2(20) not null
)

--comment 注释
comment on column TEST.testid  is 'ID';
comment on column TEST.name  is '名称';

-- Create/Recreate primary, unique and foreign key constraints 加主键
alter table TEST add constraint PK_TEST primary key (TESTID)

2.创建索引

CREATE SEQUENCE test_sequence
MINVALUE 1
MAXVALUE 999999999
START WITH 1
INCREMENT BY 1
CACHE 20
ORDER; 

3.创建触发器

CREATE OR REPLACE TRIGGER test_tg
BEFORE INSERT ON test 
FOR EACH ROW
	DECLARE
		-- local variables here
	BEGIN
		--注意 这里的 INTO:new. 后面加需要自增的字段名称 这里对应 test表中的 testid 列

		SELECT test_sequence.nextval INTO:new.testid FROM dual;
		
	END test_tg;

4.测试

--插值
INSERT INTO TEST VALUES(NULL,'卡卡西');
INSERT INTO TEST VALUES(NULL,'阿斯玛');
INSERT INTO TEST VALUES(NULL,'夕阳红');
INSERT INTO TEST VALUES(NULL,'雷藏');
INSERT INTO TEST(NAME) VALUES('皮卡丘')

--查询
SELECT *FROM TEST;

结果图：