insert语句中sequence的使用方法

 

我们常常在表中需要插入一些自动增长的值;

一方面,我们可以手动添加这些值，

另一方面,oracle提供的sequence可以帮助我们实现插入的值自动增长,

而不需要我们手动的提供值,我们需要做的就是设置好sequence的初值和增长值即可。

关于序列的定义和参数设置,

请参考:http://blog.youkuaiyun.com/BOBO12082119/archive/2011/01/08/6124597.aspx

下面是一个实例：

create table goods( goods_id varchar2(5) primary key, goods_name varchar2(20), store_date date, goods_num number(5)); create sequence seq_goods_id start with 10001 increment by 1 nomaxvalue nocycle nocache order; 

1.在insert语句中,直接使用sequence.nextval引用；

insert into goods(goods_id,goods_name,store_date,goods_num) values(seq_goods_id.nextval,'圣爽矿泉水',to_date('2011-01-01','yyyy-mm-dd'),500); insert into goods(goods_id,goods_name,store_date,goods_num) values(seq_goods_id.nextval,'海飞丝男士',to_date('2011-01-08','yyyy-mm-dd'),1000); insert into goods(goods_id,goods_name,store_date,goods_num) values(seq_goods_id.nextval,'洽洽瓜子',to_date('2011-02-25','yyyy-mm-dd'),950); SQL> select * from goods; GOODS_ID GOODS_NAME STORE_DATE GOODS_NUM -------- -------------------- ----------- --------- 10001 圣爽矿泉水 2011-1-1 500 10002 海飞丝男士 2011-1-8 1000 10003 洽洽瓜子 2011-2-25 950 

注意：在首次引用定义好的sequence时,必须先引用nextval,否则你会得到下面的错误：

SQL> create sequence ddd 2 start with 1 3 increment by 1 4 nomaxvalue; Sequence created SQL> select ddd.currval from dual; select ddd.currval from dual ORA-08002: sequence DDD.CURRVAL is not yet defined in this session SQL> select ddd.nextval from dual; NEXTVAL ---------- 1 

2.通过before insert触发器

每一次向表中添加值时,都触动此触发器,自动为这个insert语句加上一个sequence值.

create or replace trigger tri_goods_id before insert on goods for each row begin select seq_goods_id.nextval into :new.goods_id from dual; end tri_goods_id; insert into goods(goods_name,store_date,goods_num) values('银鹭八宝粥',to_date('2011-03-26','yyyy-mm-dd'),100); insert into goods(goods_name,store_date,goods_num) values('茅台啤酒',to_date('2011-02-10','yyyy-mm-dd'),10000); SQL> select * from goods; GOODS_ID GOODS_NAME STORE_DATE GOODS_NUM -------- -------------------- ----------- --------- 10001 圣爽矿泉水 2011-1-1 500 10002 海飞丝男士 2011-1-8 1000 10003 洽洽瓜子 2011-2-25 950 10004 银鹭八宝粥 2011-3-26 100 10005 茅台啤酒 2011-2-10 10000 

sequence有两个伪列:currval和nextval.

currval:引用当前的sequence值，

nextval:引用下一个sequence值.

那么currval和nextval可以引用在那些场合呢？那些场合又不适合用此二值呢？

You can use CURRVAL and NEXTVAL in: 1.The SELECT list of a SELECT statement that is not contained in a subquery, materialized view, or view 2.The SELECT list of a subquery in an INSERT statement 3.The VALUES clause of an INSERT statement 4.The SET clause of an UPDATE statement You cannot use CURRVAL and NEXTVAL: 1.A subquery in a DELETE, SELECT, or UPDATE statement 2.A query of a view or of a materialized view 3.A SELECT statement with the DISTINCT operator Pseudocolumns 4.A SELECT statement with a GROUP BY clause or ORDER BY clause 5.A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator 6.The WHERE clause of a SELECT statement 7.DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement 8.The condition of a CHECK constraint