会议室预定系统数据库文件pro

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();