/**
* 动态给表mtg_room添加自增字段int_id,自动赋值,根据表名和字段名替换[mtg_room],[int_id]
*/
-- 1.新增数字类型字段,按rownum+10000000形成唯一8位数数据填充
alter table mtg_room add int_id INTEGER;
update mtg_room t set t.int_id = rownum + 10000000;
commit;
-- 2.创建自增序列,起始值11000000,已有数据量限制在百万之内
drop sequence seq_mtg_room;
create sequence seq_mtg_room
minvalue 11000000
maxvalue 99999999
start with 11000001
increment by 1
nocache;
-- 3.创建触发器自增
create or replace trigger mtg_room_test_id
before insert on mtg_room --mtg_room 是表名
for each row
begin
select seq_mtg_room.nextval into :new.int_id from dual;
end mtg_room_test_id;
-- 4.插数据测试
--INSERT INTO mtg_Room(id,Room_Name,Basic_Situation) values('1004','会议室1004','坐席200');
select * from mtg_room;
-- ***如遇异常,撤销更改****
-- 移除字段 int_id,序列、触发器
/*
alter table mtg_room drop column int_id;
drop sequence seq_mtg_room;
drop trigger mtg_room_test_id;
*/