Oracle 设置自动增长列

1、

-- Create table
create table PM_PROD_REGISTRATION_JOBBOOK
(
  id            NUMBER(9) not null,
  main_id       VARCHAR2(50) not null,
  jobbooking_id VARCHAR2(50) not null,
  isrevocation  NUMBER default 0 not null,
  createdate    DATE default sysdate,
  createuser    VARCHAR2(50),
  updatedate    DATE,
  updateuser    VARCHAR2(50),
  ext1          VARCHAR2(50),
  ext2          VARCHAR2(50),
  ext3          VARCHAR2(50),
  ext4          VARCHAR2(50),
  ext5          VARCHAR2(50)
)

2、

--创建序列

create sequence prodRegistrJobbookDao_sequence --序列名

minvalue 1 --最小值

maxvalue 999999999 --最大值

start with 1--从1开始

increment by 1--每次加1

nocache;--不建缓冲区

3、

--创建触发器

CREATE TRIGGER prodRegistrJobbookDao_trigger BEFORE
insert ON  PM_PROD_REGISTRATION_JOBBOOK FOR EACH ROW
begin
select prodRegistrJobbookDao_sequence.nextval into:New.id from dual;
end;

4、

--插入数据

insert into PM_PROD_REGISTRATION_JOBBOOK(Main_Id, Createuser, Createdate, Jobbooking_Id, Isrevocation)
values ('qwe','asd',sysdate,'zxc',0);

--查询结果
select * from PM_PROD_REGISTRATION_JOBBOOK;