Oracle自增主键

mysql中自增主键：create table test（id primary key AUTO_INCREMENT）  AUTO_INCREMENT=值;

 

Oracle中没有这种写法，Oracle中自增主键，需要靠序列和触发器实现

例：

drop table account_la;
create table account_la(
  customer_id integer,--主键
  customer_name varchar2(20) not null, --客户名
  customer_id_card varchar2(20) not null, --客户身份证号
  customer_sex char(4) default'男' not null,--客户性别
  customer_phone number(11) not null,--客户手机号
  customer_password varchar2(128) not null,--密码
  create_time timestamp default sysdate not null,--注册时间，默认为系统当前时间
  bank_id integer not null,--银行号
  customer_address varchar2(256) not null,--客户地址
  customer_status integer default 1 not null,--客户账户状态，0：不可用，1：可用
  account_balance varchar2(64) not null,--账户余额
  constraint account_pk primary key(customer_id),
  constraint check_sex check(customer_sex in ('男','女')),
  constraint check_status check(customer_status in(0,1)),
  constraint unique_id_card unique(customer_id_card),
  constraint unique_phone unique(customer_phone)
);
drop sequence SEQ_account_autoid 
-- 创建account_la的序列，自增初始值为1，最大值为999999999999，每次加1
create sequence SEQ_account_autoid
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
nocache;

drop trigger TRG_account_autoid;
-- 创建account_la的触发器，每次插入值时查询序列的nextVal（下一个序列值）
create trigger TRG_account_autoid before insert on account_la for each row when(new.customer_id is null)
begin 
  select SEQ_account_autoid.nextval into:new.customer_id from dual;
end;