oracle的自增长

mysql的自增长非常容易,一个 AUTO_INCREMENT

就搞定,可是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 ROW WHEN (NEW.ID IS NULL) BEGIN SELECT dept_seq.NEXTVAL INTO :NEW.ID FROM dual ; END ; 触发器版本2: 无视插入数据时,指定的ID CREATE OR REPLACE TRIGGER dept_before_insert2 BEFORE INSERT ON T_TEST_DEPARTMENTS2 FOR EACH ROW BEGIN SELECT 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

 

posted @ 2017-01-19 22:24  仁波切  阅读(509)  评论(0编辑  收藏  举报