Oracle列自增實現(3)-DEFAULT Values Using Sequences

標籤：

Oracle 12c中，可以使用序列的NEXTVAL and CURRVAL的值作為預設值，來實現列自增！

一、使用序列的 NEXTVAL and CURRVAL 的值作為預設值

建立序列

CREATE SEQUENCE t1_seq;

建表

CREATE TABLE t1 ( id NUMBER DEFAULT t1_seq.NEXTVAL, description VARCHAR2(30) );

插入資料

INSERT INTO t1 (description) VALUES (‘DESCRIPTION only‘);INSERT INTO t1 (id, description) VALUES (999, ‘ID=999 and DESCRIPTION‘);INSERT INTO t1 (id, description) VALUES (NULL, ‘ID=NULL and DESCRIPTION‘);

查詢結果

SELECT * FROM t1;

二、預設值明確為非空

建立兩個序列

CREATE SEQUENCE default_seq;CREATE SEQUENCE default_on_null_seq;

建表，col1和col2分別使用上面兩個序列的NEXTVAL作為預設值，其中col2 DEFAULT ON NULL

CREATE TABLE t2 (col1 NUMBER DEFAULT default_seq.NEXTVAL,col2 NUMBER DEFAULT ON NULL default_on_null_seq.NEXTVAL,description VARCHAR2(30));

插入資料

INSERT INTO t2 (description) VALUES (‘DESCRIPTION only‘);INSERT INTO t2 (col1, col2, description) VALUES (999, 999, ‘999,999,DESCRIPTION‘);INSERT INTO t2 (col1, col2, description) VALUES (NULL, NULL, ‘NULL,NULL,DESCRIPTION‘);

查詢資料，可以看到col2位NULL時候，被預設轉換使用了default_on_null_seq.NEXTVAL的

SELECT * FROM t2;

三、例子：主從表的簡單例子

CREATE SEQUENCE master_seq;

CREATE SEQUENCE detail_seq;

CREATE TABLE master (

id NUMBER DEFAULT master_seq.NEXTVAL,

description VARCHAR2(30)

);

CREATE TABLE detail (

id NUMBER DEFAULT detail_seq.NEXTVAL,

master_id NUMBER DEFAULT master_seq.CURRVAL,

description VARCHAR2(30)

);

INSERT INTO master (description) VALUES (‘Master 1‘);

INSERT INTO detail (description) VALUES (‘Detail 1‘);

INSERT INTO detail (description) VALUES (‘Detail 2‘);

INSERT INTO master (description) VALUES (‘Master 2‘);

INSERT INTO detail (description) VALUES (‘Detail 3‘);

INSERT INTO detail (description) VALUES (‘Detail 4‘);

SELECT * FROM master;

SELECT * FROM detail;

原文：

DEFAULT Values for Table Columns : Enhancements in Oracle Database 12c Release 1 (12.1)

Oracle列自增實現(3)-DEFAULT Values Using Sequences