联系：手机/微信(+86 17813235971) QQ(107644445)
标题：ORACLE 12C 新特性Identity Columns—实现ORACLE自增长列功能
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在ORACLE 12C以前的版本中,如果要实现列自增长,需要通过序列+触发器实现,到了12C ORACLE 引进了Identity Columns新特性,从而实现了列自增长功能,和mysql,sql server类似功能.
使用语法
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 '10046 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.XIFENFEI.COM');
已创建 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='7ff05d11a18' sqlid='21uzyjhwp33vw'
INSERT INTO T_XIFENFEI(name) VALUES('WWW.XIFENFEI.COM')
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.xifenfei.com');
已创建 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.xifenfei.com
1 www.orasos.com
XFF_PDB@CHF> insert into t_xifenfei2 values (null,'www.xifenfei.com');
insert into t_xifenfei2 values (null,'www.xifenfei.com')
*
第 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.xifenfei.com');
已创建 1 行。
XFF_PDB@CHF> select * from t_xifenfei3;
ID NAME
---------- --------------------
1 www.xifenfei.com
测试结论：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.xifenfei.com');
已创建 1 行。
XFF_PDB@CHF> select * from t_xifenfei4;
ID NAME
---------- --------------------
1 www.xifenfei.com