Oracle 12c的自增列Identity Columns
在Oracle的12c版本中，Oracle实现了类似MySQL中的auto_increment的自增列，下面我们看一起Oracle是怎么实现的。
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> create table t (userid number GENERATED ALWAYS AS IDENTITY,uname varchar2(200));
Table created.
SQL> select * from t;
no rows selected
SQL> insert into t valuse('aaa');
insert into t valuse('aaa')
*
ERROR at line 1:
ORA-00928: missing SELECT keyword
SQL> insert into t values(1,'aaa');
insert into t values(1,'aaa')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
SQL> insert into t values('aaa');
insert into t values('aaa')
*
ERROR at line 1:
ORA-00947: not enough values
SQL> insert into t(uname) values('aaa');
1 row created.
SQL> insert into t(uname) values('bbb');
1 row created.
SQL> insert into t(uname) values('ccc');
1 row created.
SQL> select * from t;
USERID UNAME
---------- ------------------------------
1 aaa
2 bbb
3 ccc
使用metadata包可以看到表的DDL
SQL> select dbms_metadata.get_ddl('TABLE','T') FROM DUAL;
CREATE TABLE "SYS"."T"
( "USERID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999
999999999999999999999 INCREMENT BY 1 START WITH 1 CA
CHE 20 NOORDER NOCYCLE NOT NULL ENABLE,
"UNAME" VARCHAR2(200)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 214748364
5
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CAC
HE DEFAULT)
TABLESPACE "SYSTEM"
仔细看其实这个自增列就是序列的语法，其实内部来讲就是一个序列。