Oracle12c如何实现自增列

小编给大家分享一下Oracle12c如何实现自增列，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

网站建设哪家好，找成都创新互联公司！专注于网页设计、网站建设、微信开发、小程序制作、集团企业网站建设等服务项目。为回馈新老客户创新互联还提供了子洲免费建站欢迎大家使用！

一．MySQL自增列

众所周知，mysql有自增列功能。

Mysql中每张表只能有一个自增列，并且自增列必须是主键或者唯一键。

mysql> create table test2(id int not null auto_increment);

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql> create table test2(id int not null auto_increment primary key);

Query OK, 0 rows affected (0.01 sec)

Mysql插入空值，可以看到是允许的，默认会用自增列值插入。

mysql> insert into test2 values();

Query OK, 1 row affected (0.01 sec)

mysql> select * from test2;

+----+

| id |

+----+

| 1 |

+----+

1 row in set (0.00 sec)

Mysql也是允许插入的值不连续

mysql> insert into test2 values(3);

Query OK, 1 row affected (0.00 sec)

mysql> select * from test2;

+----+

| id |

+----+

| 1 |

| 3 |

+----+

2 rows in set (0.00 sec)

表加一列，显式插入新增列，自增列会隐式地从当前最大值自增。

mysql> insert into test2(b) values(4);

Query OK, 1 row affected (0.00 sec)

mysql> select * from test2;

+----+------+

| id | b |

+----+------+

| 1 | NULL |

| 3 | NULL |

| 4 | 4 |

+----+------+

3 rows in set (0.00 sec)

二．Oracle自增列

Oracle在12c之前的版本，自增列功能只能通过序列+触发器的方式实现。12c版本终于迎来了这个新功能。

自增列语法

Oracle12c如何实现自增列

建表

SQL> CREATE TABLE test1 (

2 id int GENERATED ALWAYS AS IDENTITY

3 );

Table created.

SQL> desc test1

Name Null? Type

----------------------------------------- -------- ----------------------------

ID NOT NULL NUMBER(38)

自增列会自动加上not null约束。

此时会自动生成一个序列

SQL> select SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,CACHE_SIZE from dba_sequences where SEQUENCE_OWNER='MING';

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CACHE_SIZE

------------------------------ ---------- ---------- ------------ ----------

ISEQ$$_45410 1 1.0000E+28 1 20

SQL> set line 150

SQL> select TABLE_NAME,COLUMN_NAME,DATA_DEFAULT from dba_tab_columns where TABLE_NAME='TEST1' and COLUMN_NAME='ID';

TABLE_NAME COLUMN_NAME DATA_DEFAULT

------------------------------ ------------------------------ --------------------------------------------------

TEST1 ID "MING"."ISEQ$$_45410".nextval

注意下面这两个动作

SQL> select ISEQ$$_45410.nextval from dual;

NEXTVAL

----------

SQL> select ISEQ$$_45410.nextval from dual;

NEXTVAL

----------

插入一个值

SQL> insert into test1 values(1);

insert into test1 values(1)

ERROR at line 1:

ORA-32795: cannot insert into a generated always identity column

不能向自增列插入值。

SQL> alter table test1 add b int;

Table altered.

SQL> insert into test1(b) values(1);

1 row created.

SQL> select * from test1;

ID B

---------- ----------

3 1

可以看到ID列插入了值，但是不是1，而是3.因为之前手动select从序列中取过值。

经过上面实验不难猜测，下面语句会报错

SQL> insert into test1(id,b) values(4,1);

insert into test1(id,b) values(4,1)

ERROR at line 1:

ORA-32795: cannot insert into a generated always identity column

Update和delete

SQL> update test1 set id=5;

update test1 set id=5

ERROR at line 1:

ORA-32796: cannot update a generated always identity column

SQL> delete from test1;

1 row deleted.

SQL> commit;

Commit complete.

Update不可以，但是delete是可以的。

可以用default指代

SQL> insert into test1 values(default,1);

1 row created.

SQL> select * from test1;

ID B

---------- ----------

5 1

SQL> commit;

Commit complete.

生成的序列其他用户也是可以用的

SQL> insert into tx(a) values(ISEQ$$_45410.nextval);

1 row created.

SQL> commit;

Commit complete.

这个序列是不能删除的。

SQL> drop sequence ming.ISEQ$$_45410;

drop sequence ming.ISEQ$$_45410

ERROR at line 1:

ORA-32794: cannot drop a system-generated sequence

SQL> drop table test1;

Table dropped.

SQL> select ISEQ$$_45410.nextval from dual;

NEXTVAL

----------

Purge回收站后就没有了。

SQL> purge recyclebin;

Recyclebin purged.

所以在删除表时可以加purge，加cascade constraint也不会马上删除序列。

SQL> CREATE TABLE test2 (

2 id int GENERATED BY DEFAULT ON NULL AS IDENTITY

3 );

Table created.

SQL> insert into test2 values('');

1 row created.

SQL> select * from test2;

----------

Insert可以指定值，但是指定的值不会被序列识别，换句话说，如果手动插入值2，序列的下一个值是2，那么在此插入空的时候，会插入2，而不是3.

SQL> insert into test2 values(4);

1 row created.

SQL> insert into test2 values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> select ISEQ$$_45418.nextval from dual;

NEXTVAL

----------

SQL> select * from test2;

----------

SQL> insert into test2 values('');

1 row created.

SQL> select * from test2;

----------

下面这个Update的动作有点意思，当前表上有2条ID=4的值，用default更新的时候会用序列赋予不同的值。

SQL> update test2 set id=default where id=4;

2 rows updated.

SQL> select * from test2;

----------

既然已经知道，自增列是用序列创建的，那么下面两种方式也是可以的

create sequence ming$seq01 minvalue 1 maxvalue 9999999 start with 1 increment by 2 cache 20 nocycle;

create table test3(id int default ming$seq01.nextval);

create table test3(id number);

alter table test3 modify id number default seq_1.nextval;

区别在于是插入null的时候不会变成数字，该列也没有not null约束。

SQL> insert into test3 values(null);

1 row created.

SQL> select * from test3;

----------

l Oracle自增列是利用序列做到的。自增列会自动加上非空约束；

l 表删除的时候，序列不会马上删除，这是因为删除的表会进入回收站，要关联删除需要加purge；

l GENERATED ALWAYS AS IDENTITY可以delete，不能显示insert，不能update；

l GENERATED BY DEFAULT ON NULL AS IDENTITY会自动将null值插入序列中的值，增删改都可以，相比GENERATED ALWAYS AS IDENTITY更加灵活，但是列的唯一性不能保证。

l 受自增列启发，可以自己创建序列，指定为表列的默认值。

l 系统自建序列的属性不能更改，可以在创建自增列的时候手动修改，否则较小cache默认值，会造成性能问题。Like this:

CREATE TABLE test4 (id NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 10 cache 100));

l 其他用户如果要向带有自增列表中插入数据的话，那么需要序列权限

以上是“Oracle12c如何实现自增列”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注创新互联行业资讯频道！

网页题目：Oracle12c如何实现自增列
转载来于：http://lszwz.com/article/igoesj.html

乐山网站建设

Oracle12c如何实现自增列

一．MySQL自增列

二．Oracle自增列

其他资讯

联系我们

135-1821-9792

028-86922220

快捷导航

二维码

友情链接交换友情链接

乐山网站建设

Oracle12c如何实现自增列

一．MySQL自增列

二．Oracle自增列

其他资讯

联系我们

135-1821-9792

028-86922220

快捷导航

二维码

友情链接 交换友情链接

友情链接交换友情链接