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
不积跬步,无以至千里,不积小流,无以成江海!
实践则生,空谈则死!