如果现在有一个酒店的管理表格,怎么让同一个房间号的入住插入保证在这个房间号的最晚的LiDian之后?
比如:CREATE TABLE JiuDian
(fangJian INTEGER NOT NULL,
Daoda DATE NOT NULL,
LiDian DATE NOT NULL,
LvKe CHAR(30) NOT NULL,
PRIMARY KEY (fangJian, Daoda));INSERT INTO JiuDian(fangJian, Daoda, LiDian, LvKe)
VALUES
(123, to_date('20160202', 'YYYYMMDD'), to_date('20160206','YYYYMMDD'), 'A'),
(123, to_date('20160204', 'YYYYMMDD'), to_date('20160208','YYYYMMDD'), 'B')
;相当于123的房间在离店之前就被新的旅客入住。表格要怎么改呢
比如:CREATE TABLE JiuDian
(fangJian INTEGER NOT NULL,
Daoda DATE NOT NULL,
LiDian DATE NOT NULL,
LvKe CHAR(30) NOT NULL,
PRIMARY KEY (fangJian, Daoda));INSERT INTO JiuDian(fangJian, Daoda, LiDian, LvKe)
VALUES
(123, to_date('20160202', 'YYYYMMDD'), to_date('20160206','YYYYMMDD'), 'A'),
(123, to_date('20160204', 'YYYYMMDD'), to_date('20160208','YYYYMMDD'), 'B')
;相当于123的房间在离店之前就被新的旅客入住。表格要怎么改呢
用触发器 或者 存储过程
麻烦就在这,问题是不允许用变量和触发器,但是允许修改表格的结构,或者增加冗余表格都可以(效率不在考虑范围之内)我一直在想能不能用check约束来实现,后来发现check约束中不可以包含子查询或者函数,所以卡在这了
(select '101' fangJian,to_date('20170101','yyyymmdd') daoda,to_date('20170103','yyyymmdd')likai,'A' lvke from dual
union all select '101',to_date('20170104','yyyymmdd'),to_date('20170106','yyyymmdd'),'B' from dual
union all select '123',to_date('20170104','yyyymmdd'),to_date('20170110','yyyymmdd'),'C' from dual);create table JiuDianNew as
(select '101' fangJian,to_date('20170102','yyyymmdd') daoda,to_date('20170103','yyyymmdd')likai,'P' lvke from dual
union all select '101',to_date('20170107','yyyymmdd') ,to_date('20170202','yyyymmdd'),'Q' from dual);insert into JiuDian
select j.* from (select fangjian,max(daoda) daoda,max(likai) likai from JiuDian group by fangjian)t ,JiuDianNew j
where t.fangJian=j.fangjian
and t.likai<j.daoda;
commit;select * from JiuDian;
drop table JiuDian;
drop table JiuDianNew; 我这个是个例子,供参考!
多谢你的回答~~麻烦的是要求只能重新设计table,而下边的INSERT语句不能修改就是说修改完table以后,下边的insert语句可以正常工作,不会有这样的bug