数据库有个特殊的字段,自增字段,这个字段是有系统自己帮我们填上的。
oracle中没有自增字段,可通过序列+触发器间接实现,
CREATE SEQUENCE EMPID_sequence
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE
order;
create or replace trigger tri_insert_CEMP
before insert on CEMP
for each row
begin
select EMPID_sequence.nextval into :new.EMPID from dual;
end;
/
commit;
sqlserver和db2中是IDENTITY
创建自增字段表的语句如下:
第一创建表的时候就创建自增字段,语法:
CONNECT TO SAMPLES;
CREATE TABLE T_TEST (
F_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ) ,
F_NAME VARCHAR (10) ) ;
CONNECT RESET;
第二种就是修改已有表的字段为自增字段:
db2 => alter table ORGAN_NODE alter column ID set GENERATED ALWAYS AS
IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE )
oracle中没有自增字段,可通过序列+触发器间接实现,
CREATE SEQUENCE EMPID_sequence
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE
order;
create or replace trigger tri_insert_CEMP
before insert on CEMP
for each row
begin
select EMPID_sequence.nextval into :new.EMPID from dual;
end;
/
commit;
sqlserver和db2中是IDENTITY
创建自增字段表的语句如下:
第一创建表的时候就创建自增字段,语法:
CONNECT TO SAMPLES;
CREATE TABLE T_TEST (
F_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ) ,
F_NAME VARCHAR (10) ) ;
CONNECT RESET;
第二种就是修改已有表的字段为自增字段:
db2 => alter table ORGAN_NODE alter column ID set GENERATED ALWAYS AS
IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE )