DELIMITER $$
DROP PROCEDURE IF EXISTS pro$$
CREATE PROCEDURE pro()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
declare dateBegin date default '2009-5-2';
declare dateEnd date default '2009-5-2';
declare timeBegin time default '9:00';
declare timeEnd time default '11:00';
set timeBegin = '9:00';
set timeEnd = '11:00';
select distinct rm.roomName
from tblRoom as rm, tblReserve as rs
where rm.roomBuilding = 'A1'
and
(
(rm.pkRoomId not in (select fkRoomId from tblReserve))
or
((rm.pkRoomId = rs.fkRoomId)
and
(
(timeBegin >= rs.rsrvTimeEnd
and timeEnd <= (select min(rs2.rsrvTimeBegin)
from tblReserve as rs2
where rs2.rsrvTimeBegin >= rs.rsrvTimeEnd
and rm.pkRoomId = rs2.fkRoomId
)
)
or
(timeEnd <= rs.rsrvTimeBegin
and timeBegin >= (select max(rs2.rsrvTimeEnd)
from tblReserve as rs2
where rs2.rsrvTimeEnd <= rs.rsrvTimeBegin
and rm.pkRoomId = rs2.fkRoomId
)
)
or
(timeBegin >= (select max(rs2.rsrvTimeEnd)
from tblReserve as rs2
where rm.pkRoomId = rs2.fkRoomId
)
)
or
(timeEnd <= (select min(rs2.rsrvTimeBegin)
from tblReserve as rs2
where rm.pkRoomId = rs2.fkRoomId
)
)
/*浠ヤ笂鏄椂闂达紝浠ヤ笅鏄棩鏈?/
or
(dateBegin > rs.rsrvDateEnd
and dateEnd < (select min(rs2.rsrvDateBegin)
from tblReserve as rs2
where rs2.rsrvDateBegin > rs.rsrvDateEnd
and rm.pkRoomId = rs2.fkRoomId
)
)
or
(dateEnd < rs.rsrvDateBegin
and dateBegin > (select max(rs2.rsrvDateEnd)
from tblReserve as rs2
where rs2.rsrvDateEnd < rs.rsrvDateBegin
and rm.pkRoomId = rs2.fkRoomId
)
)
or
(dateBegin > (select max(rs2.rsrvDateEnd)
from tblReserve as rs2
where rm.pkRoomId = rs2.fkRoomId
)
)
or(dateEnd < (select min(rs2.rsrvDateBegin)
from tblReserve as rs2
where rm.pkRoomId = rs2.fkRoomId
)
)
)
)
);
END$$
DELIMITER ;
call pro();