oracle 自增数列,oracle列自增实现

1.Sequence+Trigger实现Oracle列自增

CREATE SEQUENCE 序列名

[INCREMENT BY n]

[START WITH n]

[{MAXVALUE/MINVALUE n|NOMAXVALUE}]

[{CYCLE|NOCYCLE}]

[{CACHE n|NOCACHE}];

创建测试表

sys@newtestCDB> CREATE TABLE TEST(

2 ID NUMBER (10) NOT NULL,

3 DESCRIPTION VARCHAR2 (50) NOT NULL,

4 CONSTRAINT test_pk PRIMARY KEY (ID)

5 );

Table created.

Elapsed: 00:00:00.14

sys@newtestCDB> CREATE SEQUENCE test_seq;

Sequence created.

Elapsed: 00:00:00.02

Elapsed: 00:00:00.38

sys@newtestCDB> CREATE OR REPLACE TRIGGER test_before_insert

2 BEFORE INSERT ON test

3 FOR EACH ROW

4 BEGIN

5 SELECT test_seq.NEXTVAL INTO :new.id FROM dual;

6 END;

7 /

Trigger created.

Elapsed: 00:00:00.12

sys@newtestCDB> INSERT INTO TEST (DESCRIPTION) VALUES ('不指定ID');

1 row created.

Elapsed: 00:00:00.08

sys@newtestCDB> INSERT INTO TEST (ID, DESCRIPTION) VALUES (TEST_USER.DEPT_SEQ.NEXTVAL, '指定ID');

INSERT INTO TEST (ID, DESCRIPTION) VALUES (TEST_USER.DEPT_SEQ.NEXTVAL, '指定ID')

*

ERROR at line 1:

ORA-02289: sequence does not exist

Elapsed: 00:00:00.01

sys@newtestCDB> INSERT INTO TEST (ID, DESCRIPTION) VALUES (TEST_SEQ.NEXTVAL, '指定ID');

1 row created.

Elapsed: 00:00:00.01

sys@newtestCDB> SELECT * FROM TEST;

ID DESCRIPTION

1 不指定ID

3 指定ID

Elapsed: 00:00:00.03

2.DEFAULT Values Using Sequences

sys@newtestCDB> INSERT INTO TEST (description) VALUES ('DESCRIPTION only');

1 row created.

Elapsed: 00:00:00.01

sys@newtestCDB> INSERT INTO TEST (id, description) VALUES (999, 'ID=999 and DESCRIPTION');

1 row created.

Elapsed: 00:00:00.00

sys@newtestCDB> INSERT INTO TEST (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');

1 row created.

Elapsed: 00:00:00.01

sys@newtestCDB> SELECT * FROM test;

ID DESCRIPTION

4 DESCRIPTION only

999 ID=999 and DESCRIPTION

ID=NULL and DESCRIPTION

Elapsed: 00:00:00.02

sys@newtestCDB> CREATE TABLE TEST(

2 ID NUMBER (10) DEFAULT ON NULL test_seq.NEXTVAL,

3 DESCRIPTION VARCHAR2 (50) NOT NULL

4 );

Table created.

Elapsed: 00:00:00.02

sys@newtestCDB> INSERT INTO TEST (description) VALUES ('DESCRIPTION only');

1 row created.

Elapsed: 00:00:00.01

sys@newtestCDB> INSERT INTO TEST (id, description) VALUES (999, 'ID=999 and DESCRIPTION');

1 row created.

Elapsed: 00:00:00.01

sys@newtestCDB> INSERT INTO TEST (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');

1 row created.

Elapsed: 00:00:00.01

sys@newtestCDB> SELECT * FROM test;

ID DESCRIPTION

5 DESCRIPTION only

999 ID=999 and DESCRIPTION

6 ID=NULL and DESCRIPTION

Elapsed: 00:00:00.02

3.看上一篇里面