总结一下，oracle实现自增长的途径： 1、使用sequence; 创建：create sequence id_seq start with 1 increment by 1; 引用：select id_seq.nextval from dual; 在该表上可以创建一个触发器来引用，并自动插入相应的表 2、使用自建表加select * from update来维护，如果每天都序列号都要复位，可以用这种方式来实现。 建一张保存序列号的表，如tabseq(id number)，并插入初始值0; 使用锁定来实现串行:select * from tabseq for update; 取得当前表的id值加1，作为下一个id返回 更新回表并提交。如果要实现每天复位，可以增加一个日期字段，然后和当前日期进行比较决定是否复位。
使用序列来实现自动增长，不过需要自己取值sq.nextval来实现。
序列+触发器实现。创建SEQUENCE create sequence s_country_id increment by 1 start with 1 maxvalue 999999999; 创建一个基于该表的before insert 触发器，在触发器中使用该SEQUENCE create or replace trigger bef_ins_t_country_define before insert on t_country_define referencing old as old new as new for each row begin new.country_id=s_country_id.nextval; end;
触发器： create or replace TRIGGER Test_Trr BEFORE INSERT ON Table1 --在‘Table1’上创建触发器 FOR EACH ROW BEGIN SELECT SEQ1.NEXTVAL INTO :new.ID FROM sys.DUAL; --‘nextval=增加sequence的值，然后返回 sequence 值再将该值插入到ID中’ END;
--创建sequence create sequence seq_on_test increment by 1 start with 1 nomaxvalue nocycle nocache;--建表 drop table test; create table test( ID integer ,stu_name nvarchar2(4) ,stu_age number );--插入数据 insert into test values(seq_on_test.nextval,'张三',24); insert into test values(seq_on_test.nextval,'李四',25);select * from test;--结果 /* 1 张三 24 2 李四 25 */--seq的两个方法 select seq_on_test.currval from dual; select seq_on_test.nextval from dual;--结果 /* 2 3 */
创建SEQUENCE.调用此SEQUENCE.NEXTVAL即可
SQL> select * from v$version;BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - ProductionSQL> create sequence seq_t 2 increment by 1 3 start with 1 4 nomaxvalue 5 nocycle 6 nocache 7 /序列已创建。SQL> select seq_t.currval from dual; select seq_t.currval from dual * 第 1 行出现错误: ORA-08002: sequence SEQ_T.CURRVAL is not yet defined in this session SQL> select seq_t.nextval from dual; NEXTVAL ---------- 1SQL> select seq_t.currval from dual; CURRVAL ---------- 1SQL> drop table t; drop table t * 第 1 行出现错误: ORA-00942: table or view does not exist SQL> create table t(id int,col_01 varchar(30));表已创建。SQL> insert into t values(seq_t.nextval,'a');已创建 1 行。SQL> insert into t values(seq_t.nextval,'b');已创建 1 行。SQL> commit;提交完成。SQL> select seq_t.currval from dual; CURRVAL ---------- 3SQL> select * from t; ID COL_01 ---------- ------------------------------ 2 a 3 bSQL> alter system flush shared_pool;系统已更改。SQL> select seq_t.currval from dual; CURRVAL ---------- 3SQL> insert into t values(seq_t.nextval,'c');已创建 1 行。SQL> commit;提交完成。SQL> select * from t; ID COL_01 ---------- ------------------------------ 2 a 3 b 4 c
下面可以看到 alter system flush shared_pool 会对 cache 的 sequence 产生影响。SQL> alter sequence seq_t cache 20;序列已更改。SQL> select seq_t.currval from dual; CURRVAL ---------- 4SQL> alter system flush shared_pool;系统已更改。SQL> select seq_t.currval from dual; CURRVAL ---------- 4SQL> alter system flush shared_pool;系统已更改。SQL> select seq_t.currval from dual; CURRVAL ---------- 4SQL> exit 从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options 断开C:\Documents and Settings\kxning>sqlplus sys/oracle@as4 as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on 星期日 4月 11 11:16:04 2010Copyright (c) 1982, 2005, Oracle. All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining optionsSQL> select seq_t.currval from dual; select seq_t.currval from dual * 第 1 行出现错误: ORA-08002: sequence SEQ_T.CURRVAL is not yet defined in this session SQL> select seq_t.nextval from dual; NEXTVAL ---------- 5SQL> select seq_t.currval from dual; CURRVAL ---------- 5SQL> alter system flush shared_pool;系统已更改。SQL> select seq_t.nextval from dual; NEXTVAL ---------- 25SQL>
CREATE SEQUENCE emp_sequence
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加，不循环
CACHE 10; 一旦定义了emp_sequence，你就可以用CURRVAL，NEXTVAL
CURRVAL=返回 sequence的当前值
NEXTVAL=增加sequence的值，然后返回 sequence 值INSERT INTO emp VALUES
(empseq.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20); SELECT empseq.currval FROM DUAL;
2:触发器实现，参考我的博客：
http://blog.csdn.net/java3344520/archive/2009/11/30/4907591.aspx
2:触发器实现（其实就是使用序列）
1、使用sequence;
创建：create sequence id_seq start with 1 increment by 1;
引用：select id_seq.nextval from dual;
在该表上可以创建一个触发器来引用，并自动插入相应的表
2、使用自建表加select * from update来维护，如果每天都序列号都要复位，可以用这种方式来实现。
建一张保存序列号的表，如tabseq(id number)，并插入初始值0;
使用锁定来实现串行:select * from tabseq for update;
取得当前表的id值加1，作为下一个id返回
更新回表并提交。如果要实现每天复位，可以增加一个日期字段，然后和当前日期进行比较决定是否复位。
序列+触发器实现。创建SEQUENCE
create sequence s_country_id increment by 1 start with 1 maxvalue 999999999; 创建一个基于该表的before insert 触发器，在触发器中使用该SEQUENCE
create or replace trigger bef_ins_t_country_define
before insert on t_country_define
referencing old as old new as new for each row
begin
new.country_id=s_country_id.nextval;
end;
create or replace TRIGGER Test_Trr BEFORE INSERT ON Table1 --在‘Table1’上创建触发器 FOR EACH ROW BEGIN SELECT SEQ1.NEXTVAL INTO :new.ID FROM sys.DUAL; --‘nextval=增加sequence的值，然后返回 sequence 值再将该值插入到ID中’ END;
--创建sequence
create sequence seq_on_test
increment by 1
start with 1
nomaxvalue
nocycle
nocache;--建表
drop table test;
create table test(
ID integer
,stu_name nvarchar2(4)
,stu_age number
);--插入数据
insert into test values(seq_on_test.nextval,'张三',24);
insert into test values(seq_on_test.nextval,'李四',25);select * from test;--结果
/*
1 张三 24
2 李四 25
*/--seq的两个方法
select seq_on_test.currval from dual;
select seq_on_test.nextval from dual;--结果
/*
2
3
*/
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - ProductionSQL> create sequence seq_t
2 increment by 1
3 start with 1
4 nomaxvalue
5 nocycle
6 nocache
7 /序列已创建。SQL> select seq_t.currval from dual;
select seq_t.currval from dual
*
第 1 行出现错误:
ORA-08002: sequence SEQ_T.CURRVAL is not yet defined in this session
SQL> select seq_t.nextval from dual; NEXTVAL
----------
1SQL> select seq_t.currval from dual; CURRVAL
----------
1SQL> drop table t;
drop table t
*
第 1 行出现错误:
ORA-00942: table or view does not exist
SQL> create table t(id int,col_01 varchar(30));表已创建。SQL> insert into t values(seq_t.nextval,'a');已创建 1 行。SQL> insert into t values(seq_t.nextval,'b');已创建 1 行。SQL> commit;提交完成。SQL> select seq_t.currval from dual; CURRVAL
----------
3SQL> select * from t; ID COL_01
---------- ------------------------------
2 a
3 bSQL> alter system flush shared_pool;系统已更改。SQL> select seq_t.currval from dual; CURRVAL
----------
3SQL> insert into t values(seq_t.nextval,'c');已创建 1 行。SQL> commit;提交完成。SQL> select * from t; ID COL_01
---------- ------------------------------
2 a
3 b
4 c
下面可以看到 alter system flush shared_pool 会对 cache 的 sequence 产生影响。SQL> alter sequence seq_t cache 20;序列已更改。SQL> select seq_t.currval from dual; CURRVAL
----------
4SQL> alter system flush shared_pool;系统已更改。SQL> select seq_t.currval from dual; CURRVAL
----------
4SQL> alter system flush shared_pool;系统已更改。SQL> select seq_t.currval from dual; CURRVAL
----------
4SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开C:\Documents and Settings\kxning>sqlplus sys/oracle@as4 as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on 星期日 4月 11 11:16:04 2010Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining optionsSQL> select seq_t.currval from dual;
select seq_t.currval from dual
*
第 1 行出现错误:
ORA-08002: sequence SEQ_T.CURRVAL is not yet defined in this session
SQL> select seq_t.nextval from dual; NEXTVAL
----------
5SQL> select seq_t.currval from dual; CURRVAL
----------
5SQL> alter system flush shared_pool;系统已更改。SQL> select seq_t.nextval from dual; NEXTVAL
----------
25SQL>