oracle自增序列实现

1.建立表

 1 -- Create table
 2 create table SCYJ_ROLES
 3 (
 4   id          VARCHAR2(30) not null,
 5   rolename    VARCHAR2(100),
 6   description VARCHAR2(200)
 7 )
 8 tablespace TBS_SFCDATA
 9   pctfree 10
10   initrans 1
11   maxtrans 255;
12 -- Add comments to the table 
13 comment on table SCYJ_ROLES
14   is '角色表';
15 -- Add comments to the columns 
16 comment on column SCYJ_ROLES.id
17   is '角色ID';
18 comment on column SCYJ_ROLES.rolename
19   is '角色名稱';
20 comment on column SCYJ_ROLES.description
21   is '描述';
22 -- Create/Recreate primary, unique and foreign key constraints 
23 alter table SCYJ_ROLES
24   add constraint PRI_ID2 primary key (ID)
25   using index 
26   tablespace TBS_SFCDATA
27   pctfree 10
28   initrans 2
29   maxtrans 255;

2.建立一个序列

1 -- Create sequence 
2 create sequence SEQ_SCYJ_ROLES
3 minvalue 1
4 maxvalue 1000000000000000
5 start with 1
6 increment by 1
7 cache 20;

 

3.用触发器关联起来

1 create or replace trigger tr_scyj_roles--触发器名称
2   before insert on scyj_roles--表名
3   for each row
4 begin
5   select SEQ_SCYJ_ROLES.nextval into :new.id from dual;--SEQ_SCYJ_ROLES为SEQ名称，id为要建立自增的字段
6 end;

 

posted @ 2021-12-31 15:55  v流年v  阅读(356)  评论(0)    收藏  举报