ORACLE 12C Identity Columns實現ORACLE自增長列

在ORACLE 12C以前的版本中,如果要實現列自增長,需要通過序列+觸發器實現,到了12C ORACLE 引進了Identity Columns新特性,從而實現了列自增長功能,和mysql,sql server類似功能.
使用文法
ORACLE 12C IDENTITY
GENERATED ALWAYS AS IDENTITY方式測試
C:＼Users＼ffcheng>sqlplus chf/xifenfei@pdb
 
SQL*Plus: Release 12.1.0.2.0 Production on 星期二 3月 10 14:34:46 2015
 
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
 
上次成功登入時間: 星期五 12月 19 2014 21:00:26 +08:00
 
串連到:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
 
XFF_PDB@CHF> select * from v$version;
 
BANNER                                                 CON_ID
-------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.          0
1.0.2.0 - 64bit Production
 
PL/SQL Release 12.1.0.2.0 - Production                      0
CORE    12.1.0.2.0      Production                          0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Produ          0
ction
 
NLSRTL Version 12.1.0.2.0 - Production                      0
 
XFF_PDB@CHF>  create table t_xifenfei (id number GENERATED ALWAYS AS IDENTITY,na
me varchar2(100));
 
表已建立。
 
XFF_PDB@CHF> select object_name,object_type from user_objects;
 
OBJECT_NAME     OBJECT_TYPE
--------------- -----------------------
ISEQ$$_91982    SEQUENCE
T_XIFENFEI      TABLE
 
XFF_PDB@CHF> set long 10000
XFF_PDB@CHF> select dbms_metadata.get_ddl('TABLE','T_XIFENFEI') FROM DUAL;
 
DBMS_METADATA.GET_DDL('TABLE','T_XIFENFEI')
--------------------------------------------------------------------------------
  CREATE TABLE "CHF"."T_XIFENFEI"
   (    "ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE
   9999999999999999999999999999 INCREMENT BY 1 START WITH 1
   CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE,
        "NAME" VARCHAR2(100)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "INMEMORY"
 
XFF_PDB@CHF> INSERT INTO T_XIFENFEI VALUES(1,'WWW.XIFNEFEI.COM');
INSERT INTO T_XIFENFEI VALUES(1,'WWW.XIFNEFEI.COM')
*
第 1 行出現錯誤:
ORA-32795: 無法插入到“始終產生”身份列
 
 
XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.XIFNEFEI.COM');
 
已建立 1 行。
 
XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.orasos.COM');
 
已建立 1 行。
 
XFF_PDB@CHF> commit;
 
提交完成。
 
XFF_PDB@CHF> col name for a30
XFF_PDB@CHF> select * from t_xifenfei;
 
        ID NAME
---------- ------------------------------
         1 WWW.XIFNEFEI.COM
         2 WWW.orasos.COM
 
XFF_PDB@CHF> update t_xifenfei set id=3 where id=2;
update t_xifenfei set id=3 where id=2
                      *
第 1 行出現錯誤:
ORA-32796: 無法更新“始終產生”身份列
 
XFF_PDB@CHF> delete from t_xifenfei where id=1;
 
已刪除 1 行。
 
XFF_PDB@CHF> commit;
 
提交完成。
 
XFF_PDB@CHF> select ISEQ$$_91982.nextval from dual;
 
   NEXTVAL
----------
         3
 
XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.ORASOS.COM');
 
已建立 1 行。
 
XFF_PDB@CHF> commit;
 
提交完成。
 
XFF_PDB@CHF> select * from t_xifenfei;
 
        ID NAME
---------- ------------------------------
         2 WWW.orasos.COM
         4 WWW.ORASOS.COM
 
XFF_PDB@CHF> ALTER SESSION SET EVENTS ? TRACE NAME CONTEXT FOREVER, LEVEL 1
2'
 
會話已更改。
 
XFF_PDB@CHF>  select value from v$diag_info where name='Default Trace File'
 
VALUE
--------------------------------------------------------------------------------
 
D:＼APP＼FFCHENG＼diag＼rdbms＼xff＼xff＼trace＼xff_ora_10628.trc
 
XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.111cn.net');
 
已建立 1 行。
 
XFF_PDB@CHF> COMMIT;
 
提交完成。
 
--跟蹤trace檔案
PARSING IN CURSOR #688719640 len=55 dep=0 uid=103 oct=2 lid=103 tim=15129490112 hv=961646460
ad=ƍff05d11a18' sqlid=དuzyjhwp33vw'
INSERT INTO T_XIFENFEI(name) VALUES('WWW.111cn.net')
END OF STMT
PARSE #688719640:c=15600,e=18909,p=0,cr=44,cu=0,mis=1,r=0,dep=0,og=1,plh=2541165129,tim=15129490112
EXEC #688719640:c=0,e=347,p=0,cr=1,cu=5,mis=0,r=1,dep=0,og=1,plh=2541165129,tim=15129490731
STAT #688719640 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  T_XIFENFEI (cr=1 pr=0 pw=0 time=296 us)'
STAT #688719640 id=2 cnt=1 pid=1 pos=1 obj=91983 op='SEQUENCE  ISEQ$$_91982 (cr=0 pr=0 pw=0 time=89 us)'
WAIT #688719640: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=15129490971
 
XFF_PDB@CHF> alter table t_xifenfei2 modify(id number GENERATED ALWAYS AS IDENTI
TY MINVALUE 1 MAXVALUE 99999999999999 INCREMENT BY 1 START WITH 1 CACHE  100);
 
表已更改。
 
XFF_PDB@CHF> drop sequence ISEQ$$_91982;
drop sequence ISEQ$$_91982
              *
第 1 行出現錯誤:
ORA-32794: 無法刪除系統產生的序列
 
XFF_PDB@CHF> drop table t_xifenfei;
 
表已刪除。
 
XFF_PDB@CHF> select object_name,object_type from user_objects where object_name=
'ISEQ$$_91982'
 
OBJECT_NAME          OBJECT_TYPE
-------------------- -----------------------
ISEQ$$_91982         SEQUENCE
 
XFF_PDB@CHF> select object_name,object_type from user_objects where object_name=
'ISEQ$$_91982'
 
OBJECT_NAME          OBJECT_TYPE
-------------------- -----------------------
ISEQ$$_91982         SEQUENCE
 
XFF_PDB@CHF> purge table t_xifenfei;
 
表已清除。
 
XFF_PDB@CHF> select object_name,object_type from user_objects where object_name=
'ISEQ$$_91982'
 
未選定行
這裡的出來幾個結論：
1. GENERATED ALWAYS AS IDENTITY 列無法人工指定值和修改該值
2. GENERATED IDENTITY 本質也是通過sequence實現
3. GENERATED IDENTITY 中sequence不能單獨被刪除
4. GENERATED IDENTITY 中的表刪除,如果存在資源回收筒中,該sequence依然儲存,如果表被徹底刪除,則sequence也被刪除
5. GENERATED IDENTITY 中的sequence可以通過select 語句查詢
6. 通過alert table 語句來修改GENERATED IDENTITY 的sequence相關值

GENERATED BY DEFAULT AS IDENTITY方式測試
XFF_PDB@CHF>  create table t_xifenfei2 (id number GENERATED BY DEFAULT AS IDENTI
TY,name varchar2(100)) tablespace users;
 
表已建立。
 
XFF_PDB@CHF> insert into t_xifenfei2 values (1,'www.111cn.net');
 
已建立 1 行。
 
XFF_PDB@CHF> insert into t_xifenfei2(name) values ('www.orasos.com');
 
已建立 1 行。
 
XFF_PDB@CHF> col name for a20
XFF_PDB@CHF> select * from t_xifenfei2;
 
        ID NAME
---------- --------------------
         1 www.111cn.net
         1 www.orasos.com
 
XFF_PDB@CHF> insert into t_xifenfei2 values (null,'www.111cn.net');
insert into t_xifenfei2 values (null,'www.111cn.net')
                                *
第 1 行出現錯誤:
ORA-01400: 無法將 NULL 插入 ("CHF"."T_XIFENFEI2"."ID")
 
 
XFF_PDB@CHF> desc t_xifenfei2
 名稱                                      是否為空白? 類型
 ----------------------------------------- -------- ---------------------------
 
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(100)
得出結論：
1. GENERATED BY DEFAULT AS IDENTITY方式不能在該列中插入null值
2. GENERATED BY DEFAULT AS IDENTITY方式可以指定具體值插入

GENERATED BY DEFAULT ON NULL AS IDENTITY方式測試
XFF_PDB@CHF>  create table t_xifenfei3 (id number GENERATED BY DEFAULT on null A
S IDENTITY,name varchar2(100)) tablespace users;
 
表已建立。
 
XFF_PDB@CHF> desc t_xifenfei3
 名稱                                      是否為空白? 類型
 ----------------------------------------- -------- ----------------------------
 
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(100)
 
XFF_PDB@CHF> insert into t_xifenfei3 values (null,'www.111cn.net');
 
已建立 1 行。
 
XFF_PDB@CHF> select * from t_xifenfei3;
 
        ID NAME
---------- --------------------
         1 www.111cn.net
測試結論：GENERATED BY DEFAULT ON NULL AS IDENTITY的列上可以查詢null值，只是預設轉換為對應的sequence值

傳統自增長列實現方法
XFF_PDB@CHF> create table t_xifenfei4 (id number,name varchar2(100)) tablespace
users;
 
表已建立。
 
XFF_PDB@CHF> create sequence xff_sequence
  2  increment by 1
  3  minvalue 1
  4  nomaxvalue
  5  start with 1
  6  cache 20
  7  order;
 
序列已建立。
 
XFF_PDB@CHF> create or replace trigger xifenfei_id
  2  before insert on t_xifenfei4
  3  for each row
  4  begin
  5  select xff_sequence.nextval into :new.id from dual;
  6  end;
  7  /
 
觸發器已建立
 
XFF_PDB@CHF> insert into t_xifenfei4(name) values('www.111cn.net');
 
已建立 1 行。
 
XFF_PDB@CHF> select * from t_xifenfei4;
 
        ID NAME
---------- --------------------
         1 www.111cn.net

 

 

聯絡：手機(13429648788) QQ(107644445)
連結：http://www.xifenfei.com/5755.html
作者：惜分飛