有关SEQUENCE的简单问题

suihu 2010-12-14 09:52:52



/* 创建一个序列，默认从1开始，每次递增量为1*/

CREATE SEQUENCE myseq;



/* 创建一个表 */

CREATE testa (a VARCHAR2(10), b VARCHAR2(10));



/* 向testa表插入数据 */

INSERT INTO testa VALUES(myseq.NEXTVAL, myseq.NEXTVAL)

INSERT INTO testa VALUES(myseq.NEXTVAL, myseq.NEXTVAL)

INSERT INTO testa VALUES(myseq.NEXTVAL, myseq.NEXTVAL)

得到的结果是：

a b
1 1
2 2
3 3

但只要调用一次myseq.NEXTVAL,序列里的值就会递增1个，那么结果为什么不是这样？

a b
1 2
3 4
5 6

...全文

96 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

minitoy 2010-12-14

打赏
举报

对一条语句,sequence.nextval只取一次.感谢楼主,以前还真没注意到这个.

minitoy 2010-12-14

打赏
举报

As defined, the first reference to order_seq.NEXTVAL returns the value 1. Each subsequent statement that references order_seq.NEXTVAL generates the next sequence number (2, 3, 4,. . .). The pseudo-column NEXTVAL can be used to generate as many new sequence numbers as necessary. However, only a single sequence number can be generated for each row. In other words, if NEXTVAL is referenced more than once in a single statement, then the first reference generates the next number, and all subsequent references in the statement return the same number.

gelyon 2010-12-14

打赏
举报





--你要想实现那样的话，可以通过trigger来做：

CREATE OR REPLACE TRIGGER trg_testa

BEFORE INSERT ON testa

FOR EACH ROW 

BEGIN

     :NEW.b:=:NEW.a;

END;



/* 向testa表插入数据 */

INSERT INTO testa(a) VALUES(myseq.NEXTVAL) ;

INSERT INTO testa(a) VALUES(myseq.NEXTVAL);

INSERT INTO testa(a) VALUES(myseq.NEXTVAL) ;