Oracle列自增-12c

谁践踏了优雅 2021-12-20 13:47 373阅读 0赞

在ORACLE 12C以前的版本中,如果要实现列自增长,需要通过序列+触发器实现,到了12C ORACLE 引进了Identity Columns新特性,从而实现了列自增长功能。

## 一、Identity Columns使用语法 ##

GENERATED
    
    [ ALWAYS | BY DEFAULT [ ON NULL ] ]
    
    AS IDENTITY [ ( identity_options ) ]identity_options

## 二、[identity\_clause][identity_clause] ##

![1383841-20190227135029531-128094263.gif][]

### 2.1 ALWAYS选项 ###

DROP TABLE IDENTITY_TEST_TAB PURGE;
    
    CREATE TABLE identity_test_tab (
    
    id NUMBER GENERATED ALWAYS AS IDENTITY,
    
    description VARCHAR2(30)
    
    );

**插入测试1：**

INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION')

\[SQL\]INSERT INTO identity\_test\_tab (description) VALUES ('Just DESCRIPTION')

受影响的行: 1

时间: 0.008s

**插入测试2：**

INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION')

\[SQL\]INSERT INTO identity\_test\_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION')

\[Err\] ORA-32795: cannot insert into a generated always identity column 无法插入到“始终生成”身份列

**插入测试3：**

INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION')

\[SQL\]INSERT INTO identity\_test\_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION')

\[Err\] ORA-32795: cannot insert into a generated always identity column

![1383841-20190227135234947-61374371.png][]

**更新测试：**

UPDATE IDENTITY_TEST_TAB SET ID=2 WHERE ID=1

\[SQL\]UPDATE IDENTITY\_TEST\_TAB SET ID=2 WHERE ID=1

\[Err\] ORA-32796: cannot update a generated always identity column

**结论：**

1.  GENERATED ALWAYS AS IDENTITY 可以不指定该列进行插入
2.  GENERATED ALWAYS AS IDENTITY不能在该列中插入NULL值
3.  GENERATED ALWAYS AS IDENTITY不能指定具体值插入
4.  GENERATED ALWAYS AS IDENTITY 不能使用update更新该列

### `2.2 BY DEFAULT``选项` ###

DROP TABLE identity_test_tab PURGE;
    
    CREATE TABLE identity_test_tab (
    
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    
    description VARCHAR2(30)
    
    );

**插入测试1：**

INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');

\[SQL\]INSERT INTO identity\_test\_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION')

受影响的行: 1

时间: 0.001s

SELECT * FROM identity_test_tab;

![1383841-20190227135417321-599599362.png][]

**插入测试2：**

INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');

\[SQL\]INSERT INTO identity\_test\_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION')

受影响的行: 1

时间: 0.001s

SELECT * FROM identity_test_tab;

![1383841-20190227135514902-1888673998.png][]

**插入测试3：**

INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');

\[SQL\]INSERT INTO identity\_test\_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION')

\[Err\] ORA-01400: cannot insert NULL into ("TEST\_USER"."IDENTITY\_TEST\_TAB"."ID")

![1383841-20190227135559620-29052414.png][]

**更新测试：**

UPDATE IDENTITY_TEST_TAB SET ID=2 WHERE ID=1

\[SQL\]UPDATE IDENTITY\_TEST\_TAB SET ID=2 WHERE ID=1

受影响的行: 1

时间: 0.001sUPDATE IDENTITY\_TEST\_TAB SET ID=2 WHERE ID=1

**结论:**

1.  GENERATED BY DEFAULT AS IDENTITY 可以不指定该列进行插入
2.  GENERATED BY DEFAULT AS IDENTITY 可以指定具体值插入
3.  GENERATED BY DEFAULT AS IDENTITY 不能在该列中插入null值
4.  可以使用update更新该列，但不能更新为NULL

### `2.3 DEFAULT ON NULL``选项` ###

DROP TABLE identity_test_tab PURGE;
    
    CREATE TABLE identity_test_tab (
    
    id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    
    description VARCHAR2(30)
    
    );

**插入测试：**

INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');
    
    INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');
    
    INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');

\[SQL\]INSERT INTO identity\_test\_tab (description) VALUES ('Just DESCRIPTION')

受影响的行: 1

时间: 0.003s

\[SQL\]INSERT INTO identity\_test\_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION')

受影响的行: 1

时间: 0.001s

\[SQL\]INSERT INTO identity\_test\_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION')

受影响的行: 1

时间: 0.002s

SELECT * FROM identity_test_tab;

![1383841-20190227135751316-559884072.png][]

**更新测试**

UPDATE IDENTITY_TEST_TAB SET ID=3 WHERE ID=1

\[SQL\]UPDATE IDENTITY\_TEST\_TAB SET ID=3 WHERE ID=1

受影响的行: 1

时间: 0.004s

**结论:**

1.  GENERATED BY DEFAULT ON NULL AS IDENTITY 可以不指定该列进行插入
2.  GENERATED BY DEFAULT ON NULL AS IDENTITY 方式可以指定具体值插入
3.  GENERATED BY DEFAULT ON NULL AS IDENTITY 可以在该列中插入null值
4.  可以使用update更新该列

## 三、原理 ##

### 3.1 Identity Columns 是基于序列实现的 ###

使用此语法实现ID自增，要求必须有创建序列的权限。可以推测是基于序列实现的

执行完建表语句后：

CREATE TABLE identity_test_tab (
    
    id NUMBER GENERATED ALWAYS AS IDENTITY,
    
    description VARCHAR2(30)
    
    );

查看user\_objects

SELECT object_name, object_type FROM user_objects;

![1383841-20190227135953978-1814041846.png][]

发现建表的时候自动生成了一个sequence

SELECT table_name, 
    
    column_name,
    
    generation_type,
    
    identity_options
    
    FROM all_tab_identity_cols
    
    WHERE owner = 'TEST_USER';

![1383841-20190227140036005-397812278.png][]

表和sequence的关系存在`SYS.IDNSEQ$``表中`

`Sys``登陆查看`

SELECT a.name AS table_name,
    
    b.name AS sequence_name
    
    FROM sys.idnseq$ c
    
    JOIN obj$ a ON c.obj# = a.obj#
    
    JOIN obj$ b ON c.seqobj# = b.obj#
    
    where a.name='IDENTITY_TEST_TAB';

![1383841-20190227140111223-931514066.png][]

### 3.2 GENERATED IDENTITY 中sequence不能单独被删除 ###

DROP TABLE IDENTITY_TEST_TAB

删除表后，该sequence还存在。且该sequence无法被删除

![1383841-20190227140159412-1900201227.png][]

必须

purge table IDENTITY_TEST_TAB，

**结论：**

1.  Identity Columns 是基于序列实现的
2.  GENERATED IDENTITY 中sequence不能单独被删除
3.  GENERATED IDENTITY 中的表删除,如果存在回收站中,该sequence依然存储,如果表被彻底删除,则sequence也被删除

### 3.3 执行插入语句时的解释计划 ###

INSERT INTO identity\_test\_tab (description) VALUES ('Just DESCRIPTION');

![1383841-20190227140247961-2065527999.png][]

对比发现：该方式的效率比触发器+序列的方式高！

## 四、[identity\_options][identity_options] ##

![1383841-20190227140325821-823533577.gif][]

查看表的DDL

select dbms_metadata.get_ddl('TABLE','IDENTITY_TEST_TAB') FROM DUAL;

CREATE TABLE "TEST_USER"."IDENTITY_TEST_TAB"
    
        ( "ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOT NULL ENABLE,
    
        "DESCRIPTION" VARCHAR2(30)
    
        ) 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"

**INCREMENT BY**

> 用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表序列的值是按照此步长递减的。

**START WITH**

> 定义Oracle序列的初始值(即产生的第一个值)，默认为1。

**MAXVALUE**

> 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增序列，系统能够产生的最大值是10的27次方;对于递减序列，最大值是-1。

**MINVALUE**

定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，

**CYCLE****和NOCYCLE**

> 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。

**CACHE**

> (缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。

原文地址：https://www.cnblogs.com/xqzt/p/4455149.html

转载于:https://www.cnblogs.com/fmgao-technology/p/10443328.html

[identity_clause]: http://docs.oracle.com/database/121/SQLRF/statements_7002.htm#CJAHJHJC
[1383841-20190227135029531-128094263.gif]: /images/20211220/fa1099f958b148328a19885b0e212e88.png
[1383841-20190227135234947-61374371.png]: /images/20211220/67eca185206c4b2f9277c818bebe6525.png
[1383841-20190227135417321-599599362.png]: /images/20211220/ad57b691a6e9405eba8b6ba92a03a5f1.png
[1383841-20190227135514902-1888673998.png]: /images/20211220/b121a39f3d55405abb03c1a6f2b0bcf7.png
[1383841-20190227135559620-29052414.png]: /images/20211220/c1e119448b344690b7a346f175a96e6e.png
[1383841-20190227135751316-559884072.png]: /images/20211220/509d0be97de1433d9743074289451666.png
[1383841-20190227135953978-1814041846.png]: /images/20211220/fd4b4043102a4f90a86a5b110eec51d3.png
[1383841-20190227140036005-397812278.png]: /images/20211220/3a51f505ef874f5d9620fa808809216a.png
[1383841-20190227140111223-931514066.png]: /images/20211220/a2e06b041a234916a8ce65795a821b5a.png
[1383841-20190227140159412-1900201227.png]: /images/20211220/15f24cfccb644464b4f6fc654f811a20.png
[1383841-20190227140247961-2065527999.png]: /images/20211220/6e46dd97be124d789bff394ce5fc9bd9.png
[identity_options]: http://docs.oracle.com/database/121/SQLRF/statements_7002.htm#CJAIJGEG
[1383841-20190227140325821-823533577.gif]: /images/20211220/c079aab92d794e95ac422e98b8296c57.png