在oracle中,创建自增字段的步骤:
①建表(蓝色部分为自定义部分)
create table users(
userId number not null,
userName varchar2(20) not null,
password varchar2(20) not null,
email varchar2(30) not null,
grade number not null);
②创建sequence序列(在这里我们以users表的userId为例说明,蓝色部分为自定义部分)
create sequence users_seq increment by 1 start with 1 maxvalue 99999999;
③创建触发器trigger(蓝色部分为自定义部分)
create or replace trigger tr_users
before insert on users
for each row when(new.userId is null)
begin
select users_seq.nextval into :new.userId from dual;
end;
测试一下:
insert into users(username,password,email,grade) values('user1','user1','user1@126.com',3);
特别说明:
第一次我在创建trigger时,句子“select users_seq.nextval into :new.userId from dual; ”中的“:new ”冒号和new之间留了个空格,于是报这个错误:
LINE/COL ERROR
-------- -----------------------------------------------------------------------------------------------------
0/0 PLS-00801: 内部错误 [ph2csql_strdef_to_diana:bind]
2/49 PL/SQL: ORA-06544: PL/SQL: 内部错误,参数: [ph2csql_strdef_to_diana:bind], [], [], [], [], [], [], []
2/1 PL/SQL: SQL Statement ignored