oracle的自增長

標籤：har   指定   logs   primary   nts   esc   table   com   each   

mysql的自增長非常容易，一個auto_incritrem就搞定，可是oracle就不行了

下面是oracle的自增長

#建立一個表
CREATE TABLE T_TEST_DEPARTMENTS (ID NUMBER (10) NOT NULL,DESCRIPTION VARCHAR2 (50) NOT NULL);#建立一個表CREATE TABLE T_TEST_DEPARTMENTS2 (ID NUMBER (10) NOT NULL,DESCRIPTION VARCHAR2 (50) NOT NULL);#設定唯一的欄位ALTER TABLE T_TEST_DEPARTMENTS ADD (CONSTRAINT) dept_pk PRIMARY KEY (ID);ALTER TABLE T_TEST_DEPARTMENTS2 ADD (CONSTRAINT dept_pk PRIMARY KEY (ID));#建立序列
CREATE SEQUENCE dept_seq;CREATE SEQUENCE dept_seq2;#觸發器版本1:非空時，才使用序列的NEXTVAL值CREATE OR REPLACE TRIGGER dept_before_insert BEFORE INSERT ON T_TEST_DEPARTMENTS FOR EACH ROWWHEN (NEW.ID IS NULL)BEGINSELECT dept_seq.NEXTVAL INTO :NEW.ID FROM dual ;END ;觸發器版本2: 無視插入資料時，指定的IDCREATE OR REPLACE TRIGGER dept_before_insert2BEFORE INSERT ON T_TEST_DEPARTMENTS2 FOR EACH ROWBEGINSELECT dept_seq2.NEXTVAL INTO :new.id FROM dual;END;#測試資料INSERT INTO T_TEST_DEPARTMENTS (ID, DESCRIPTION) VALUES (19, ‘指定ID‘);INSERT INTO T_TEST_DEPARTMENTS (DESCRIPTION) VALUES (‘不指定ID‘);INSERT INTO T_TEST_DEPARTMENTS2 (ID, DESCRIPTION) VALUES (19, ‘指定ID‘);INSERT INTO T_TEST_DEPARTMENTS2 (DESCRIPTION) VALUES (‘不指定ID‘);

 

參考轉載自：http://www.cnblogs.com/xqzt/p/4452292.html

 

oracle的自增長