数据库课程设计（酒店客房管理系统）论文[1]

2005级信管专业2班数据库应用系统课程设计课程论文

Rno char(10), Goodsno char(20), Dnum int ,

Amendstime datetime not null, primary key(Gno,Rno,Goodsno), foreign key (Gno)references guest(Gno), foreign key(Rno)references Roominfo(Rno), foreign key(Goodsno)references RoGoInfo(Goodsno) )

4.2建立视图SQL语句

（1）用于查询预订房信息的视图定义如下：

create view

BookView(Gno,Gname,Rno,Rtype,Rfloor,Toward,IntoPrice,Rtime,Rltime,Days,Stime) as select

RoomState.Gno,Gname,RoomState.Rno,Rtype,Rfloor,Toward,IntoPrice,Rtime,Rltime,Days,Stime from Roominfo,RoomState,guest

where flag='1' and Roominfo.Rno=RoomState.Rno and RoomState.Gno=guest.Gno

（2）用于查询已入住房的试图如下：

create view

IntoView(Gno,Gname,Rno,Rtype,Rfloor,Toward,IntoPrice,Atime,Ltime,Days,Account) as select

RoomState.Gno,Gname,RoomState.Rno,Rtype,Rfloor,Toward,IntoPrice,Atime,Ltime,Days,Account from Roominfo,RoomState,guest

where flag='2' and Roominfo.Rno=RoomState.Rno and RoomState.Gno=guest.Gno

（3）用于查询空房的视图定义如下：

create view

EmRoView(Rno,Rtype,Rprice,Rfloor,Toward) as

select Rno,Rtype,Rprice,Rfloor,Toward from Roominfo

where Rno not in (select Rno From RoomState)

29

2005级信管专业2班数据库应用系统课程设计课程论文

4.3建立存储过程SQL语句

1.lsqSearchDate的定义

create procedure lsqSearchDate @date datetime as

select Atno,sum(Amount) from Consumelist where Wtime=@date group by Atno

2.lsqSearchEmpty的定义

create procedure lsqSearchEmpty @floor int as

select Rno,Rtype,Rprice,Rfloor,Toward from EmRoView where Rfloor=@floor lsqSearchEmpty 2

3.lsqWatchGuest的定义 create procedure lsqWatchGuest as

select Gno,Gname,Gsex,Gid from guest

4.lsqSearchGuest的定义

create procedure lsqSearchGuest @Gno char(20) as begin

select Gno,Gname,Account,balance from guest where Gno=@Gno

select RoomState.Rno,Rtype,IntoPrice from RoomState,Roominfo

where RoomState.Gno=@Gno and RoomState.Rno=Roominfo.Rno select c.Atno,Atname,Amount, Amount*Atprice AmuMoney,Wtime from Consumelist c,Atariff a

where c.Gno=@Gno and c.Atno=a.Atno

30

2005级信管专业2班数据库应用系统课程设计课程论文

selectg.Rno,r.Goodsname,g.Dnum,r.Oprice,r.Dmultiple,Oprice*g.Dnum*r.Dmultiple AmendMoney,g.Amendstime from GoAmInfo g,RoGoInfo r

where g.Gno=@Gno and g.Goodsno=r.Goodsno end

5.lsqConsumeList的定义

CREATE PROCEDURE lsqConsumeList @Consumelist_Gno char(20), @Consumelist_Atno char(20), @Consumelist_Amount float, @Consumelist_wtime datetime as insert

into Consumelist

values(@Consumelist_Gno,@Consumelist_Atno , @Consumelist_Amount ,@Consumelist_wtime ) lsqConsumeList 'G00004','A-KTV-M',2,'2007-1-5' 6.lsqAddRoomGoods的定义 create procedure lsqAddRoomGoods @GDnumber char(20), @GDname char(20), @GDprice float, @GDmultiple float as insert

into RoGoInfo(Goodsno,Goodsname,Oprice,Dmultiple) values(@GDnumber,@GDname,@GDprice,@GDmultiple) 7.lsqAddAmusement的定义 create procedure lsqAddAmusement

@Atno char(20), @Atname char(20), @Atprice float as insert into Atariff

values(@Atno,@Atname,@Atprice)

8.lsqAddGuest的定义

create procedure lsqAddGuest @Gno char(20), @Gname char(20), @Gsex char(20),

31

2005级信管专业2班数据库应用系统课程设计课程论文

@Gid char(20), @discount float as insert

into guest(Gno,Gname,Gsex,Gid,discount) values(@Gno,@Gname,@Gsex,@Gid,@discount)

9.lsqMoney的定义

create procedure lsqMoney @Gno char(20), @Account float as

update guest

set Account=@Account where Gno=@Gno

10.lsqBook的定义

create procedure lsqBook @Rno char(10), @Gno char(20), @Rtime datetime, @Rltime datetime, @Days int, @Stime datetime, @discount float output, @Rprice float output as begin

select @discount=discount from guest where Gno=@Gno select @Rprice=Rprice from Roominfo where Rno=@Rno insert

into RoomState(Rno,Gno,Rtime,Rltime,IntoPrice,Days,Stime,flag)

values(@Rno,@Gno,@Rtime,@Rltime,@discount*@Rprice,@Days,@Stime,'1') end

11.lsqInto的定义

create procedure lsqInto @Rno char(10), @Gno char(20), @Atime datetime, @Ltime datetime, @Days int, @Stime datetime,

32