一个完整的例子：

— (1) Create table

create table test_emp

(

USERID NUMBER not null,

USERNAME VARCHAR2(50),

SEX VARCHAR2(2),

ADDDATE DATE default sysdate

)

tablespace GAME_DATA

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64

minextents 1

maxextents unlimited

);

— Create/Recreate indexes

create index idx_test_emp on test_emp (username)

tablespace INDX

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64K

minextents 1

maxextents unlimited

)

compress;

–创建 sequence

create sequence SQ_test_emp

minvalue 1

maxvalue 99999999999

start with 1

increment by 1

cache 20;

–创建 触发器 trigger

create or replace trigger tig_test_emp

before insert on test_emp

for each row

declare

— local variables here

nextid number;

begin

select SQ_test_emp.NEXTVAL into nextid FROM dual;

:new.USERID := nextid;

end;

–序列提供两个方法，NextVal和CurrVal。顾名思义，NextVal为取序列的下一个值，一次NEXTVAL会增加一次sequence的值；CurrVal为取序列的当前值。例如，插入记录时

–insert into tablename(id) values(sequence_id.nextval);–sequence_id为序列名

本文固定链接: https://www.2hei.net/2007/02/20/oracle-%e5%bb%ba%e8%a1%a8-sequence-%e5%8f%8a-trigger%e7%9a%84%e5%88%9b%e5%bb%ba%e8%bf%87%e7%a8%8b/ | 2hei.net