oracle列自增實作

1.Sequence+Trigger實作Oracle列自增

CREATE SEQUENCE 序列名

[INCREMENT BY n]

[START WITH n]

[{MAXVALUE/MINVALUE n|NOMAXVALUE}]

[{CYCLE|NOCYCLE}]

[{CACHE n|NOCACHE}];

建立測試表

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

Sequence created.

Elapsed: 00:00:00.02

Elapsed: 00:00:00.38

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

1 row created.

Elapsed: 00:00:00.08

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

Elapsed: 00:00:00.03

2.DEFAULT Values Using Sequences

Elapsed: 00:00:00.00

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

3 DESCRIPTION VARCHAR2 (50) NOT NULL

4 );

3.看上一篇裡面

     本文轉自whshurk 51CTO部落格，原文連結：http://blog.51cto.com/shurk/2061598，如需轉載請自行聯系原作者