ORACLE: 数据库23AI创建表 例子

测试环境：ORACLE LINUX 8.10  + DATABASE 23AI Free ; 

1、创建 序列号

create sequence "DEPT_SEQ"
start with 1
increment by 1
cache 10
minvalue 1
maxvalue 9900
nocycle;

2、创建表并指定主键自动增序列号

CREATE TABLE "DEPT"
( "DEPTNO" NUMBER DEFAULT DEPT_SEQ.NEXTVAL,
"DNAME" VARCHAR2(50 CHAR),
"LOC" VARCHAR2(150 CHAR),
CONSTRAINT "DEPT_PK" PRIMARY KEY ("DEPTNO")
USING INDEX ENABLE
) ;

3、触发器：当主键DEPTNO为空时，触发器再次触发生成序列号

CREATE OR REPLACE EDITIONABLE TRIGGER "DEPT_TRG1"
BEFORE INSERT ON DEPT
FOR EACH ROW
BEGIN
IF :NEW.DEPTNO IS NULL THEN
SELECT DEPT_SEQ.NEXTVAL INTO :NEW.DEPTNO FROM SYS.DUAL;
END IF;
END;

 4、 插入例子数据：

INSERT INTO DEPT(DEPTNO,DNAME,LOC) VALUES(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT(DEPTNO,DNAME,LOC) VALUES(20,'RESEARCH','DALLAS');
INSERT INTO DEPT(DEPTNO,DNAME,LOC) VALUES(30,'SALES','CHICAGO');
INSERT INTO DEPT(DEPTNO,DNAME,LOC) VALUES(40,'OPERATIONS','BOSTON');