oracle序列递增的性能,ORACLE自动递增--序列

关于oracle的自动递增，一般依靠序列来产生。当然没SQL SERVICE 2005那样两粒简单的关键字就吃定。

创建序列的语法：

CREATE SEQUENCE 序列名

[INCREMENT BY n]  --增长量，默认为1，负值为减量

[START WITH n]  ---初始值 ，默认为1

[{MAXVALUE/ MINVALUE n|NOMAXVALUE}]--最大值，最小值，默认不规定最大最小值

[{CYCLE|NOCYCLE}]--达到最大值循环或不循环

[{CACHE n|NOCACHE}]; --缓冲，爱理不理的。

个人认为以下三个选项少用 :

[{MAXVALUE/ MINVALUE n|NOMAXVALUE}]

[{CYCLE|NOCYCLE}]

[{CACHE n|NOCACHE}];

不管你信不信，反正我信了，我就是没曾用过。

创建一张测试表：

SQL>createtabletestseq(

2nextnumber,

3  curr number

4  );

Tablecreated

(1)最常用的，最简单的序列：

SQL>createsequencemyseq;

Sequencecreated

插入五条数据测试：

SQL>insertintotestseqvalues(myseq.nextval,myseq.currval);

1 row inserted

SQL>insertintotestseqvalues(myseq.nextval,myseq.currval);

1 row inserted

SQL>insertintotestseqvalues(myseq.nextval,myseq.currval);

1 row inserted

SQL>insertintotestseqvalues(myseq.nextval,myseq.currval);

1 row inserted

SQL>insertintotestseqvalues(myseq.nextval,myseq.currval);

1 row inserted

SQL>select*fromtestseq;

NEXTCURR

---------- ----------

1          1

2          2

3          3

4          4

5          5

(2)改变增量的序列

SQL>dropsequencemyseq;

Sequencedropped

SQL>deletefromtestseq;

5rowsdeleted

SQL>createsequencemyseq incrementby10;

Sequencecreated

插入五条数据测试：

SQL>insertintotestseqvalues(myseq.nextval,myseq.currval);

1 row inserted

SQL>insertintotestseqvalues(myseq.nextval,myseq.currval);

1 row inserted

SQL>insertintotestseqvalues(myseq.nextval,myseq.currval);

1 row inserted

SQL>insertintotestseqvalues(myseq.nextval,myseq.currval);

1 row inserted

SQL>insertintotestseqvalues(myseq.nextval,myseq.currval);

1 row inserted

SQL>select*fromtestseq;

NEXTCURR

---------- ----------

1          1

11         11

21         21

31         31

41         41

(3)设置初始值的序列

SQL>dropsequencemyseq;

Sequencedropped

SQL>deletefromtestseq;

5rowsdeleted

SQL>createsequencemyseq incrementby10 startwith25;

Sequencecreated

插入五条数据测试：

SQL>insertintotestseqvalues(myseq.nextval,myseq.currval);

1 row inserted

SQL>insertintotestseqvalues(myseq.nextval,myseq.currval);

1 row inserted

SQL>insertintotestseqvalues(myseq.nextval,myseq.currval);

1 row inserted

SQL>insertintotestseqvalues(myseq.nextval,myseq.currval);

1 row inserted

SQL>insertintotestseqvalues(myseq.nextval,myseq.currval);

1 row inserted

SQL>select*fromtestseq;

NEXTCURR

---------- ----------

25         25

35         35

45         45

55         55

65         65

SQL>

(4)设置序列的最大值-----挺少用，十分少用

SQL>dropsequencemyseq;

Sequencedropped

SQL>deletefromtestseq;

5rowsdeleted

SQL>createsequencemyseq incrementby10 startwith25 maxvalue 50;

Sequencecreated

SQL>

SQL>insertintotestseqvalues(myseq.nextval,myseq.currval);

1 row inserted

SQL>insertintotestseqvalues(myseq.nextval,myseq.currval);

1 row inserted

SQL>insertintotestseqvalues(myseq.nextval,myseq.currval);

1 row inserted

SQL>insertintotestseqvalues(myseq.nextval,myseq.currval);

insertintotestseqvalues(myseq.nextval,myseq.currval)

ORA-08004: 序列 MYSEQ.NEXTVAL exceeds MAXVALUE 无法实例化

SQL>insertintotestseqvalues(myseq.nextval,myseq.currval);

insertintotestseqvalues(myseq.nextval,myseq.currval)

ORA-08004: 序列 MYSEQ.NEXTVAL exceeds MAXVALUE 无法实例化

c

SQL>select*fromtestseq;

NEXTCURR

---------- ----------

25         25

35         35

45         45

SQL>

你可以看出，当插入数据超过最大值的时候，就会产生错误。况且平常这种操作几乎没用，最大值设置感觉没意义。

掌握前面三个常用的序列方法就够应付平常的序列问题了。