Oracle 12c identity

標籤：

在12c之前，如果想實現column的自動成長，通常是通過sequence來實現，然而這個問題在12c中得到瞭解決，Oracle延遲了identity column功能，該功能完美的解決了之前的問題，下面是我的簡單測試，供參考！

SQL> alter pluggable database all open;Pluggable database altered.SQL> select name,open_mode from V$pdbs;NAME                           OPEN_MODE------------------------------ ----------PDB$SEED                       READ ONLYPDBORCL                        READ WRITESQL> conn roger/[email protected]Connected.SQL> show con_nameCON_NAME------------------------------PDBORCLSQL> create table test(id number generated by default as identity ,  2  name varchar2(20));Table created.SQL> insert into test(name) values(‘roger‘);1 row created.SQL> insert into test(name) values(‘killdb.com‘);1 row created.SQL> commit;Commit complete.SQL> select * from test;        ID NAME---------- --------------------         1 roger         2 killdb.comSQL>SQL> insert into test(id,name) values(null,‘killdb.com‘);insert into test(id,name) values(null,‘killdb.com‘)                                 *ERROR at line 1:ORA-01400: cannot insert NULL into ("ROGER"."TEST"."ID")

可以看到id列預設可以進行自動成長，在12c之前，Oracle只能通過sequence來實現這個功能。 另外我們還可以看到，這種情況下，是不能插入null值的。

SQL> alter table test modify (id default null); alter table test modify (id default null)                          *ERROR at line 1:ORA-30674: identity column cannot have a default value

那麼對於identity 的column，真的不能插入null值嗎？ 其實是可以的，不過你得這樣做：

SQL> create table test1 (id number generated by default on NULL as identity ,name varchar2(20));Table created.SQL>SQL> insert into test1 values(1,‘killdb.com‘);1 row created.SQL> insert into test1 values(2,‘baidu.com‘);1 row created.SQL> insert into test1 values(null,‘google.com‘);1 row created.SQL> commit;Commit complete.SQL> select * from test1;        ID NAME---------- --------------------         1 killdb.com         2 baidu.com         1 google.com

不過比較奇怪的是，大家看到了，插入的第3條資料的id列為null的情況下，oracle自己實際的值為1. 這個1是怎麼來的呢 ？後面會告訴你答案。

SQL> update test1 set id=100 where id=2;1 row updated.SQL> commit;Commit complete.SQL> select * from test1;        ID NAME---------- --------------------         1 killdb.com       100 baidu.com         1 google.com

這種情況下，可以進行正常的dml，因為identity column預設是為null的，下面繼續一個測試。

SQL> create table test2 (id number generated always as identity  ,name varchar2(20));Table created.SQL> insert into test2(name) values(‘killdb.com‘);1 row created.SQL> insert into test2(name) values(‘baidu.com‘);1 row created.SQL> insert into test2(name) values(‘google.com‘);1 row created.SQL> commit;Commit complete.SQL> select * from test2;        ID NAME---------- --------------------         1 killdb.com         2 baidu.com         3 google.comSQL>  update test2 set id=4  where id=2; update test2 set id=4  where id=2   *ERROR at line 1:ORA-32796: cannot update a generated always identity columnSQL> update test2 set id=1 where id=2;update test2 set id=1 where id=2  *ERROR at line 1:ORA-32796: cannot update a generated always identity columnSQL> delete from test2 where id=2;1 row deleted.SQL> commit;Commit complete.SQL> select * from test2;        ID NAME---------- --------------------         1 killdb.com         3 google.com

我們可以看到，居然不能進行update，這裡想想也應該知道，Oracle是不會允許你進行update的。

SQL>SQL> select TABLE_NAME,  2         COLUMN_NAME,  3         DEFAULT_ON_NULL,  4         IDENTITY_COLUMN,  5         DATA_DEFAULT  6    from user_tab_columns  7    where IDENTITY_COLUMN=‘YES‘;TABLE_NAME           COLUMN_NAME          DEF IDE DATA_DEFAULT-------------------- -------------------- --- --- ------------------------------------------------------------TEST                 ID                   NO  YES "ROGER"."ISEQ$$_91820".nextvalTEST1                ID                   YES YES "ROGER"."ISEQ$$_91822".nextvalTEST2                ID                   NO  YES "ROGER"."ISEQ$$_91824".nextvalSQL> SQL> set pagesize 200 long 9999SQL> select dbms_metadata.get_ddl(‘TABLE‘,‘TEST‘) from dual;DBMS_METADATA.GET_DDL(‘TABLE‘,‘TEST‘)--------------------------------------------------------------------------------  CREATE TABLE "ROGER"."TEST"   (    "ID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE,        "NAME" VARCHAR2(20)   ) SEGMENT CREATION IMMEDIATE  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "USERS"SQL> select dbms_metadata.get_ddl(‘TABLE‘,‘TEST1‘) from dual;DBMS_METADATA.GET_DDL(‘TABLE‘,‘TEST1‘)--------------------------------------------------------------------------------  CREATE TABLE "ROGER"."TEST1"   (    "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE,        "NAME" VARCHAR2(20)   ) SEGMENT CREATION IMMEDIATE  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "USERS"

最後通過表的定義，我們可以看到，ID列預設被定義為了sequence，從屬性來看跟我們之前版本中建立sequence的屬性差不多。而且我們看到預設情況下start with 為1。這個也就是為什麼前面插入null的情況下id=1的原因。

Oracle 12c identity