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
相关推荐：
loading