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;