1.Seq自增策略
Oracle创建表和主键自增
创建表:
create table tuser(
id number(11) not null,
name varchar2(20) not null,
password varchar2(20),
birthday date,
constraint tuser_pk primary key (id)
);
创建序列:
create sequence increase_seq increment by 1 start with 1 nomaxvalue
nocycle cache 10;
创建trigger:
create or replace trigger tuser_trigger
before insert on tuser for each row
begin
select increase_seq.nextval into :new.id from dual;
end;
/
根据使用的工具,可能需要增加“/”来执行PL/SQL块。
测试:
insert into tuser(name,password,birthday) values('wujay','123456',null);
commit;
select * from tuser;
ID NAME PASSWORD BIRTHDAY
---------- -------------------- -------------------- --------------
1 wujay 123456
修改表:
alter table tuser rename column id to pk_tuser;
2.UUID策略
1、创建表:
create table table1(
)
2、插入数据
insert into table1(ID)values(SYS_GUID(),"小王");
commit;
3、查询数据
select * from table1;
注:此方法也适用于存储过程,将SYS_GUID()保存为变量,可以作为查询条件使用。
create or replace procedure pro is
v_name varchar2(32);
v_uuid varchar2(32);
begin
end pro;