Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2659
  • 博文数量: 28
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 205
  • 用 户 组: 普通用户
  • 注册时间: 2014-01-12 15:22
个人简介

没有挫败,只有暂未成功而已。

文章分类

全部博文(28)

文章存档

2018年(28)

我的朋友

分类: Oracle

2018-09-28 15:02:47

identifiy 是定义自动增加的列,就像是自动编号,以前在做.net开发中使用sqlserver的时候也有这类的方法。这个值的特点就是不会重复,所以叫它自动生成的唯一值。
以下是生成的两种方式:
GENERATED BY ALWAYS AS IDENTITY 
GENERATED BY DEFAULT AS IDENTITY


By always和by default是说明生成这个identifiy的方式
By  always是完全由系统自动生成;
by default是可以由用户指定一个值生成。
下面我用by default 的值来实验一下:


[oracle@dbsnc ~]$ sqlplus / as sysdba


SQL*Plus: Release 12.2.0.1.0 Production on Fri Sep 28 11:18:10 2018


Copyright (c) 1982, 2016, Oracle.  All rights reserved.




Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


--建表
SQL> create table customer(customer_id number generated by default as identity(start with 1 increment by 1),name varchar2(20));


Table created.


--新增数据
insert into customer(name)  values('A1');
insert into customer(name)  values('A2');
insert into customer(name)  values('A3');
insert into customer(name)  values('A4');
insert into customer(name)  values('A5');
insert into customer(name)  values('A6');


1 row created.


--提交
SQL> commit;


Commit complete.


--查看
SQL> select * from customer;


CUSTOMER_ID NAME
----------- --------------------
 1 A1
 2 A2
 4 A4
 5 A5
 6 A6


--删除数据
SQL> delete from customer where name='A3';


1 row deleted.


SQL> commit;


Commit complete.


--再次查看
SQL> select * from customer;


CUSTOMER_ID NAME
----------- --------------------
 1 A1
 2 A2
 4 A4
 5 A5
 6 A6






SQL> insert into customer(name) values('A7');
insert into customer(name) values('A8');
1 row created.


SQL> commit;


Commit complete.


SQL> select * from customer;


CUSTOMER_ID NAME
----------- --------------------
 1 A1
 2 A2
 4 A4
 5 A5
 6 A6
 7 A7
 8 A8


7 rows selected.


SQL> delete from customer where name='A8';


1 row deleted.


SQL> commit;


Commit complete.


SQL> insert into customer(name) values('A9');


1 row created.


SQL> select * from customer;


CUSTOMER_ID NAME
----------- --------------------
 1 A1
 2 A2
21 A9
 4 A4
 5 A5
 6 A6
 7 A7


7 rows selected.


SQL> commit;


Commit complete.


SQL> select * from customer;


CUSTOMER_ID NAME
----------- --------------------
 1 A1
 2 A2
21 A9
 4 A4
 5 A5
 6 A6
 7 A7


7 rows selected.


SQL> insert into customer(name) values('A10');


1 row created.


SQL> commit;


Commit complete.


SQL> select * from customer;


CUSTOMER_ID NAME
----------- --------------------
 1 A1
 2 A2
21 A9
 4 A4
 5 A5
 6 A6
 7 A7
22 A10


8 rows selected.


综上实验可以得出:自动生成的CUSTOMER_ID即使删除掉了,也不会重复回收利用,而是重新生成一个编号。
阅读(32) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册