【SQL开发实战技巧】系列（四十四）：Oracle12C常用新特性☞你知道吗Oracle新增了类似Mysql的自增列（Identity Columns）功能

本文链接：https://blog.csdn.net/qq_28356739/article/details/129995064

系列文章目录

文章目录

系列文章目录
前言
Oralcle自增列（Identity Columns）
总结

前言

本篇文章主要给讲解的Oracle12C+的新特性是：在ORACLE 12C以前的版本中,如果要实现列自增长,需要通过序列+触发器实现,到了12C ORACLE 引进了Identity Columns新特性(GENERATED ALWAYS AS IDENTITY、GENERATED BY DEFAULT AS IDENTITY、GENERATED BY DEFAULT ON NULL AS IDENTITY、手动调用自增列序列),从而实现了列自增长功能,和mysql,sql server类似功能。

【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

Oralcle自增列（Identity Columns）

在ORACLE 12C以前的版本中,如果要实现列自增长,需要通过序列+触发器实现,到了12C ORACLE 引进了Identity Columns新特性(GENERATED ALWAYS AS IDENTITY、GENERATED BY DEFAULT AS IDENTITY、GENERATED BY DEFAULT ON NULL AS IDENTITY、手动调用自增列序列),从而实现了列自增长功能,和mysql,sql server类似功能：
在这里插入图片描述

案例一：GENERATED ALWAYS AS IDENTITY

首先查看当前用户下的用户对象

SQL> select object_name,object_type from user_objects;
No rows

接下来创建测试用表：

CREATE TABLE TEST_GEN(
ID  NUMBER GENERATED  ALWAYS AS IDENTITY,
V_NAME   VARCHAR2(100)
);

查看当前用户下的用户对象

SQL> select object_name,object_type from user_objects;
OBJECT_NAME     OBJECT_TYPE                                                            
----------- -----------------------
TEST_GEN             TABLE
ISEQ$$_77416         SEQUENCE

发现创建表的同时，一个序列同时被创建。接下来挖掘一下此表的DDL语句：

1:29:13 用户名: SCOTT >set long 10000
11:29:19 用户名: SCOTT >select dbms_metadata.get_ddl('TABLE','TEST_GEN') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','TEST_GEN')
----------------------------------------------------------------------------
  CREATE TABLE "SCOTT"."TEST_GEN"
   (	"ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 99999999999999
99999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NO
SCALE  NOT NULL ENABLE,
	"V_NAME" VARCHAR2(100)
   ) 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

DBMS_METADATA.GET_DDL('TABLE','TEST_GEN')
----------------------------------------------------------------------------
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

仔细看其实这个自增列就是序列的语法，其实内部来讲就是一个序列。
尝试手动drop这个序列：

SQL> drop sequence ISEQ$$_77416;
ORA-32794: 无法删除系统生成的序列

手动drop这个表，

SQL> drop table test_gen
Table dropped
SQL> select object_name,object_type from user_objects;
OBJECT_NAME     OBJECT_TYPE                                                            
----------- -----------------------
ISEQ$$_77416         SEQUENCE
SQL> purge table test_gen;
Done
SQL> select object_name,object_type from user_objects;
No rows.

说明当要drop使用自增列的表时，要直接删除而不是放入回收站，放入回收站，虽然表被删除，但是使用的索引并未被删除。
重建表后接下来手动插入数据：

begin 
  for i in 1..10 
    loop
      insert into test_gen(v_name)values('a'||i);
      end loop;
      commit;
      end;
      
SQL> select * from test_gen;
        ID V_NAME
---------- -------------------
         1 a1
         2 a2
         3 a3
         4 a4
         5 a5
         6 a6
         7 a7
         8 a8
         9 a9
        10 a10

删除一部分数据再次插入数据：

SQL> delete from test_gen where id between 5 and 8;
4 rows deleted
SQL> commit;
Commit complete
SQL> insert into test_gen values(44,'a34');
insert into test_gen values(44,'a34')
ORA-32795: 无法插入到“始终生成”身份列
SQL> insert into test_gen(v_name)values('a34');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test_gen;
        ID V_NAME
---------- ------------------------------------------
        21 a34
         1 a1
         2 a2
         3 a3
         4 a4
         9 a9
        10 a10

自增列会继续递增。
这里的出来几个结论：

GENERATED ALWAYS AS IDENTITY 列无法人工指定值和修改该值
GENERATED IDENTITY 本质也是通过sequence实现
GENERATED IDENTITY 中sequence不能单独被删除
GENERATED IDENTITY 中的表删除,如果存在回收站中,该sequence依然存储,如果表被彻底删除,则sequence也被删除
GENERATED IDENTITY 中的sequence可以通过select 语句查询
通过alert table 语句来修改GENERATED IDENTITY 的sequence相关值

案例二：GENERATED BY DEFAULT AS IDENTITY

创建测试用表：

CREATE TABLE TEST_DEF (
ID  NUMBER GENERATED BY DEFAULT AS IDENTITY,
V_NAME   VARCHAR2(100)
);

插入数据：

SQL> insert into test_DEF values(default,'a');
SQL> insert into test_def(v_name)values('b');
SQL> insert into test_def values(1,'c');
SQL> commit;
SQL> insert into test_def values(null,'c');
insert into test_def values(null,'c')
ORA-01400: 无法将 NULL 插入 ("TEST"."TEST_DEF"."ID")
SQL> select * from test_def;
        ID V_NAME
---------- ------------------------------------------
         1 a
         2 b
         1 c
SQL> desc test_def;
Name   Type          Nullable Default                       Comments 
------ ------------- -------- ----------------------------- -------- 
ID     NUMBER                 "TEST"."ISEQ$$_77424".nextval          
V_NAME VARCHAR2(100) Y

通过上面操作可以得出简单的结论：

GENERATED BY DEFAULT AS IDENTITY方式不能在该列中插入null值
GENERATED BY DEFAULT AS IDENTITY方式可以指定具体值插入

案例三：GENERATED BY DEFAULT ON NULL AS IDENTITY

创建测试用表：

CREATE TABLE TEST_NUL (
ID  NUMBER GENERATED BY DEFAULT on null AS IDENTITY,
V_NAME   VARCHAR2(100)
);
SQL> insert into test_NUL values(default,'a');
SQL> insert into test_NUL(v_name)values('b');
SQL> insert into test_NUL values(1,'c');
SQL> insert into test_NUL values(null,'c');
SQL> commit;
SQL> desc test_nul;
Name   Type          Nullable Default                       Comments 
------ ------------- -------- ----------------------------- -------- 
ID     NUMBER                 "TEST"."ISEQ$$_77426".nextval          
V_NAME VARCHAR2(100) Y                                               
SQL> select * from test_nul;
        ID V_NAME
---------- ---------
         1 a
         2 b
         1 c
         3 c

通过上面操作，得出结论：

GENERATED BY DEFAULT ON NULL AS IDENTITY的列上可以查询null值，只是默认转换为对应的sequence值

案例四：手动调用自增列序列

创建测试用表：

CREATE TABLE TEST_seq (
ID  NUMBER GENERATED BY DEFAULT AS IDENTITY,
V_NAME   VARCHAR2(100)
);

查询当前表自增列的序列名：

select a.object_name,a.object_type from user_objects a ;
OBJECT_NAME  OBJECT_TYPE
------------ ------------
TEST_SEQ     TABLE
ISEQ$$_73210 SEQUENCE

手动插入一条数据：

insert into test_seq values(default,'a');

查询数据：

select * from test_seq;

在这里插入图片描述

在插入数据的当前会话手动调用序列查询：

select ISEQ$$_73210.Currval from dual

在这里插入图片描述

select ISEQ$$_73210.Nextval from dual

在这里插入图片描述

select ISEQ$$_73210.Currval from dual

在这里插入图片描述

再次插入数据：

insert into test_seq values(default,'b');

查询表:

select * from test_seq;

在这里插入图片描述

发现手动调用nextval后，再次插入默认值之后，发现默认值也跟着跳号了，如果此时在另一个会话再次调用currval：

SQL> select ISEQ$$_73210.Currval from dual;
ORA-08002: 序列 ISEQ$$_73210.CURRVAL 尚未在此会话中定义

报错，需要先执行nextval:

select ISEQ$$_73210.Nextval from dual;

在这里插入图片描述

这时再次插入数据：

insert into test_seq values(default,'c');

再次查询数据：
在这里插入图片描述

发现创建表时指定的自增列对应的序列，和我们手动建立的序列功能应该一致，在其他地方调用此序列，再次往表插数时也会跳号。不过因为自增列序列命名原因，不会清楚某个序列到底属于哪个表的自增列所有。

【SQL开发实战技巧】系列（四十四）：Oracle12C常用新特性☞你知道吗Oracle新增了类似Mysql的自增列（Identity Columns）功能

系列文章目录

文章目录

前言

Oralcle自增列（Identity Columns）

案例一：GENERATED ALWAYS AS IDENTITY

案例二：GENERATED BY DEFAULT AS IDENTITY

案例三：GENERATED BY DEFAULT ON NULL AS IDENTITY

案例四：手动调用自增列序列

总结

“相关推荐”对你有帮助么？