select count(*) from meet where roomid=参数roomid and (入参开始时间 between start_date and end_date or 入参结束时间 between start_date and end_date or start_date between 入参开始时间 and 入参结束时间 or end_date between 入参开始时间 and 入参结束时间 )
create or replace procedure p_meet(id meet.meeting_room_id%type,v_start meet.start_date%type,v_end meet.end_date%type) as v_num number; begin select count(1) into v_num from meet where meeting_room_id=id and (start_date between v_start and v_end or end_date between v_start and v_end); if v_num>0 then dbms_output.put_line(id||'会议室'||'已经被占用'); else insert into meet values(id,v_start,v_end); end if; commit; end; /
这个问题在Oracle编程艺术中有原题:如下 select count(*) from a where a.begin_time<b.end_time and a.end_time>b.begin_time 即可,很拗的数学思想,仔细体会下,呵呵!
好像写反了。改一下: select count(*) from a where begin_time<a.end_time and end_time>a.begin_time 这样可以了!
--的改下 create or replace procedure p_meet(id meet.meeting_room_id%type,v_start meet.start_date%type,v_end meet.end_date%type) as v_num number; begin select count(1) into v_num from meet where meeting_room_id=id and ((start_date between v_start and v_end) or (end_date between v_start and v_end)); if v_num>0 then dbms_output.put_line(id||'会议室'||'已经被占用'); else insert into meet values(id,v_start,v_end); end if; commit; end; /
select count(*) from a where begin_time>a.end_time and end_time<a.begin_time
还是这个容易理解点: select count(1) from meet where meeting_room_id=id and ((start_date between v_start and v_end) or (end_date between v_start and v_end));
嗯 补充点: select count(1) from meet where meeting_room_id=id and ( (start_date between v_start and v_end) or (end_date between v_start and v_end) or (v_start between start_date and end_date ) or (v_end between start_date and end_date ) );跟 monitoy一样思路 哎此题教会我们考虑问题要全面啊!
select count(*) from a where begin_time>a.end_time or end_time<a.begin_time这样简单 只要保证都在之前或都在之后就行 而且begin一定比end小 所以之前的只拍段end,之后的只判断begin
(roomid varchar2(3),
start_date date,
end_date date);
select count(*) from meet
where roomid=参数roomid
and (入参开始时间 between start_date and end_date or 入参结束时间 between start_date and end_date or
start_date between 入参开始时间 and 入参结束时间 or end_date between 入参开始时间 and 入参结束时间 )
as
v_num number;
begin
select count(1) into v_num from meet where meeting_room_id=id and (start_date between v_start and v_end
or end_date between v_start and v_end);
if v_num>0 then
dbms_output.put_line(id||'会议室'||'已经被占用');
else
insert into meet values(id,v_start,v_end);
end if;
commit;
end;
/
select count(*)
from a
where a.begin_time<b.end_time
and a.end_time>b.begin_time
即可,很拗的数学思想,仔细体会下,呵呵!
select count(*)
from a
where begin_time<a.end_time
and end_time>a.begin_time
这样可以了!
--的改下
create or replace procedure p_meet(id meet.meeting_room_id%type,v_start meet.start_date%type,v_end meet.end_date%type)
as
v_num number;
begin
select count(1) into v_num from meet where meeting_room_id=id and ((start_date between v_start and v_end)
or (end_date between v_start and v_end));
if v_num>0 then
dbms_output.put_line(id||'会议室'||'已经被占用');
else
insert into meet values(id,v_start,v_end);
end if;
commit;
end;
/
from a
where begin_time>a.end_time
and end_time<a.begin_time
select count(1) from meet where meeting_room_id=id and ((start_date between v_start and v_end) or (end_date between v_start and v_end));
这也是有占用,但start_date和end_date 都不在区间内
嗯
补充点:
select count(1) from meet where meeting_room_id=id
and (
(start_date between v_start and v_end)
or
(end_date between v_start and v_end)
or
(v_start between start_date and end_date )
or
(v_end between start_date and end_date )
);跟 monitoy一样思路
哎此题教会我们考虑问题要全面啊!
select count(*)
from a
where begin_time>a.end_time
or end_time<a.begin_time这样简单
只要保证都在之前或都在之后就行
而且begin一定比end小
所以之前的只拍段end,之后的只判断begin