oracle实现自增长列

桃扇骨 2021-12-09 05:43 432阅读 0赞

[螺湾小成][Link 1]

## [oracle实现自增长列][oracle]    ##

序列（Sequence）是一种可以被多个用户使用的用于产生一系列唯一数字的数据库对象。序列定义存储在数据字典中，通过提供唯一数值的顺序表来简化程序设计工作，可以使用序列自动产生主键的键值。当一个序列第一次被查询调用时，它将返回一个预定值。在随后的每次查询中，序列将产生一个按指定的增量增长的值。序列可以循环，或者是连续增加的，直到指定的最大值为止。创建序列语法如下：create sequence \[模式\]序列名称\[start with 起始数字\] \[increment by 增量\]\[maxvalue 最大值|nomaxvalue\]\[minvalue 最小值|nominva lue\]\[cycle|nocuyle\]\[cache 数目|nocache\]\[order|noorder\]。  
通过序列起始数字、最大值、最小值和增量值可以确定序列是增序还是减序，每次增或减的多少。Nocyle选项用于确定在序列达到最大值（增序列）或最小值（减序列）之后不能再产生更多的值，用以防止序列回转。  
在Oracle数据库中创建序列，在使用sql语句向数据库中写入数据的时候，利用序列产生的唯一值，实现表中主键值自增。例如：

SQL>create table tablename   
         
            (id number notnull,…);     
              
        SQL >create sequence autoID increment by 1 start with 1 maxvalue 999999 cycle; 
                 
        SQL >insert into tablename values(autoID.nextval,...);

多个用户可以共用一个序列,但它是针对所有的表,因此产生的序号对一个表而言产生的主键值唯一但是不连续。  
使用触发器产生主键值。  
在数据表中，有时候需要主键值自动增加，但在Oracle数据库中，没有象Mysql的Autoincrement一样自动增长的数据类型。在实现Oracle数据库字段自增功能时，利用DML触发器来完成。  
触发器（trigger）是一些过程，当发生一个特定的数据库事件时就执行这些过程，可以使用触发器扩充引用的完整性。DML即数据操纵语言,用于让用户或程序员使用，实现对数据库中数据的操作。基本的数据操作分成两类四种：检索（查询）和更新（插入、删除、修改）。触发器类似于函数和过程，其在数据库中以独立身分存在。触发事件可以是对数据库表的DML（insert、update或delete）操作等。DML触发器是目前最广泛使用的一种触发器，即由DML语句激发的触发器，并有该语句决定DML触发器的类型。其触发事件包括insert（插入）、update（更新）和delete（删除）。无论哪种触发事件，都能为每种触发事件创建before触发器和after触发器。如可以在表上建立一个before insert 语句，表示在insert事件发生之前采取行动。  
创建触发器的语法如下：

create [or replace] trigger 触发器名称   
         
        {before|after|insteadof} 激发触发事件   
         
        referencing_clause   
         
        [WHEN trigger_condition]   
         
        [FOR EACH ROW]

referencing\_clause用来引用正在处于修改状态下的行中的数据，如果在WHEN子句中指定trigger\_condition的话，则首先对该条件求值。触发器主体只有在该条件为真值时才运行。利用触发器与序列相结合，可以实现在进行DML操作的时候，使表中主键值自动增加。其实现步骤可参照如下例子。

drop table book;  
        --创建表     
        create table book(      
           bookId varchar2(4) primary key,  
           name varchar2(20)        
        );  
        --创建序列     
        create sequence book_seq start with 1 increment by 1;   
         
        --创建触发器     
        create or replace trigger book_trigger      
        before inserton book      
        for each row      
        begin      
        select book_seq.nextval into :new.bookId from dual;     
        end ;  
        --添加数据     
        insert into book(name)  values ('cc');   
        insert into book(name)  values ('dd');  
         
        commit;

查询数据：**select** \***from** book;

当需要对book数据库表中的主键值进行写值时，便可以使用sql语句利用序列+触发器实现主键值自动增加.

这里所说的自动增长列，主要是指一个表中主键id的自动增长。

Oracle与Mysql不同，不能在CREATE建立表时设置自动增长列功能。Oracle必须通过创建sequence序列来实现自动增加列的功能。  
首先要建立序列(当然必须要先建好表，添加好主键约束，这列假设约束名为test\_sequence)  
create sequence test\_sequence  
\[increment by 1\]--增长的步长  
\[start with 1\]--从几开始增长  
\[maxvalue 100\]--增长的最大值  
\[nomaxvalue\]--不设最大值  
\[cycle|nocycle\];--循环增长/不循环增长  
定义了sequence以后就可以在insert语句中使用test\_sequence.nextval和test\_sequence.currval。  
test\_sequence.currval返回当前sequence的值，但必须在第一次初始化test\_sequence.nextval后才能使用  
test\_sequence.currval。  
test\_sequence.nextval会增加sequence的值，并返回增加后的sequence的值。  
然后可以alter修改sequence序列，来改变自动增加的方式。  
alter sequence test\_sequence increment by 1 ...;后面的选项跟建表时一样。  
还可以用drop删除sequence序列。  
drop sequence test\_sequence;  
  
Mysql先对Oracle来说就简单多了，可以在建表时进行设置。  
create table（  
id int(10) auto\_incrementprimary key  
)auto\_increment=1;  
auto\_increment=1设置自动增长列从1开始  
  
曾经给某个Mysql表格添加主键时，用了以下SQL语句

1.  **alter** **table** \`表格名\` **add** **column** \`列名\` **int** not null auto\_increment comment '主键' before \`某一列名\`;

结果遇到如下两个错误 :

1.  Error Code : 1064
2.  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'before 列名' at line 1
3.  (0 ms taken)

这个错误是说before附近有语法错误

1.  Error Code : 1075
2.  Incorrect table definition; there can be only one auto column and it must be defined as a key
3.  (0 ms taken)

这个错误是说只有主键才能指定自动增长列

网上找了下原因，说是要先添加列，再修改列。我觉得这样挺麻烦的。自己试验了一下，一句话也能搞定：

1.  **alter** **table** \`表格名\` **add** **column** \`列名\` **int** not null auto\_increment **primary** **key** comment '主键' **first**;

上面语句表示给某个表格添加一个自动增长的主键字段，并且将该字段放在第一列。

如果你想放到其他列后面，可以将上面sql语句中的 **first** 换成**after \`已经有的列名\`**

MYSQL自动增长列的问题，  
表是这样的：  
create table tips   
(   
 id int auto\_increment not null,   
 date varchar(10) null,   
 content varchar(300) null.  
 primary key(id)   
)   
我插入一条数据   
insert into tips values("2009/09/20","小TIPS");   
结果报错了，  
其实应该这样写：  
insert into tips(date,content) values("2009/09/20","小TIPS") ;  
或者  
insert into tips values(null,"2009/09/20","小TIPS") ;

[Link 1]: http://www.cnblogs.com/chenggong/
[oracle]: http://www.cnblogs.com/chenggong/archive/2012/08/02/2620109.html