Oracle创建表，id为自增序列

最新推荐文章于 2023-11-25 14:34:26 发布

weixin_34062329

最新推荐文章于 2023-11-25 14:34:26 发布

阅读量223

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/youfen/blog/1924489

版权

2019独角兽企业重金招聘Python工程师标准>>>

1.创建表

create table my_table (

id number(10) constraint pk_id primary key,
name varchar2(20) not null,
phone_Number varchar2(20) constraint unique_phone_number unique,
email_Address varchar2(200) constraint email_not_null not null,
home_Address varchar2(200) constraint home_addr_not_null not null
)
#查看约束
select * from user_constraints;

2.创建序列

create sequence my_table_seq start with 1 increment by 1;
#查看序列
select * from user_sequences;

#删除序列

DROP SEQUENCE my_table_seq；

3.创建触发器

create or replace trigger bi_my_table
before insert on my_table
for each row
when(new.id is null)
begin
select my_table_seq.nextval into:NEW.ID from dual;
end;

或者

CREATE OR REPLACE TRIGGER  "bi_my_table" 
  before insert on "my_table"              
  for each row 
begin  
  if :new."ID" is null then
    select "my_table_seq".nextval into :new."ID" from sys.dual;
  end if;
end;

ALTER TRIGGER  "bi_my_table" ENABLE

#查看触发器
select * from user_triggers;

#删除触发器

DROP TRIGGER "bi_my_table" ;

#测试

insert into my_table(name,phone_number,email_address,home_address) values('zcl','13800138000','youremail@gmail.com','guangzhou')
commit;

select * from t_user;

转载于:https://my.oschina.net/youfen/blog/1924489