oracle的自增长
时间：2021-07-01 10:21:17
帮助过：26人阅读
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
oracle的自增长
标签：har 指定 logs primary nts esc table com each
