oracle每执行sequence清0,Oracle sequence的基本概念与理解

1.如何查看sequence的DDL语句

SQL>SELECT DBMS_METADATA.GET_DDL('SEQUENCE','SEQ_SMECUSTPERIODINFO','CLMS01') FROM DUAL;  --第一个是类型，第二个是sequence的名字，第三个是sequence的owner

CREATE SEQUENCE "CLMS01"."SEQ_SMECUSTPERIODINFO" MINVALUE 1 MAXVALUE 999999

9999 INCREMENT BY 1 START WITH 8798675575 CACHE 20 ORDER NOCYCLE  --START WITH 8798675575 此值为dba_sequneces中的LAST_NUMBER，比较奇怪！！

2.dba_sequences相关字的定义

Column

Datatype

NULL

Description

SEQUENCE_OWNER

VARCHAR2(30)

NOT NULL

Owner of the sequence

SEQUENCE_NAME

VARCHAR2(30)

NOT NULL

Sequence name

MIN_VALUE

NUMBER

Minimum value of the sequence

MAX_VALUE

NUMBER

Maximum value of the sequence

INCREMENT_BY

NUMBER

NOT NULL

Value by which sequence is incremented

CYCLE_FLAG

VARCHAR2(1)

Indicates whether the sequence wraps around on reaching the limit (Y) or not (N)

ORDER_FLAG

VARCHAR2(1)

Indicates whether sequence numbers are generated in order (Y) or not (N)

CACHE_SIZE

NUMBER

NOT NULL

Number of sequence numbers to cache

LAST_NUMBER

NUMBER

NOT NULL

Last sequence number written to disk. If a sequence uses caching, the number written to disk is the last number placed in the sequence cache. This number is likely to be greater than the last sequence number that was used.

CYCLE_FLAG：标志sequence是否循环使用(到最大值之后变为最小值)

ORDER_FLAG：标志sequence是否是按照顺序生成。

CACHE_SIZE：标志内存中缓存的sequence个数(保存在shared pool中)，设置较小时会出现row cache lock的等待事件。

LAST_NUMBER：上一个写入到disk的sequence number。

3.如何修改sequence

修改cache，连接到sequence的owner，执行以下语句，调整cache值，其他的修改类似。

SQL>alter sequence cache 1000;

修改maxvalue值：

SQL> alter sequence testeq maxvalue 9999999999;

Sequence altered.

修改order、noorder值：

SQL> alter sequence testeq order/noorder;

Sequence altered.

4.sequence的使用

情景一：直接调用

****************************

创建序列

****************************

create sequence testseq

increment by 1

start with 900000

maxvalue 999999999

cycle

cache 200;

****************************

创建存储过程

****************************

create or replace procedure proc_arg

as

begin

insert into test

(object_id,object_name,owner)

values

(testseq.nextval,'dayu','dayu');

commit;

end;

情景二：创建trigger，当值为空时插入sequence

create sequence testseq

increment by 1

start with 900000

maxvalue 999999999

cycle

cache 200;

create or replace trigger test_trigger

before insert on test

for each row

declare

tempkey test.object_id%type;

begin

select testseq.NEXTVAL into tempkey from dual;

if :new.object_id is null then

:new.object_id := tempkey+1;

end if;

end;

DECLARE

i number := 0;

BEGIN

for i in 90000 .. 200000 loop

if mod(i,2)=0 then

insert into test

(object_name, owner)

values

('dayu','dayu');

commit;

end if;

end loop;

END;

DECLARE

i number := 0;

BEGIN

for i in 90000 .. 200000 loop

if mod(i,2)=1 then

insert into test

(object_name, owner)

values

('dayu', 'dayu');

commit;

end if;

end loop;

END;