Oracle实现id自动增长

1. 创建表空间

 

     create tablespace wanghao datafile 'E:\wanghao.dbf' size 20M autoextend on next 5M maxsize unlimited;

 

2. 创建用户

 

     create user wanghao identified by wanghao default tablespace wanghao;

 

3. 给用户授权

 

     grant resource to wanghao;
     grant create session to wanghao;

 

4.创建表

create table wanghao.userinfo (
	userid number(8) primary key,
	username varchar2(16) not null,
	pwd varchar2(20)
);

5. 创建序列

create sequence userinfo_seq minvalue 1 maxvalue 999999999999999999 start with 1 increment by 1 cache 20;

 

6. 创建触发器

CREATE OR REPLACE TRIGGER "userinfo_trig"
  BEFORE INSERT ON userinfo
  REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW 
DECLARE
BEGIN
  SELECT userinfo_seq.NEXTVAL INTO :NEW.userid FROM DUAL;
END userinfo_trig;

 

7. 插入数据检验

 

     insert into userinfo(username, pwd) values ('pao','pao');