Oracle 12c的自增列Identity Columns

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"

仔細看其實這個自增列就是序列的文法，其實內部來講就是一個序列。