导入存储过程.txt

酒店管理系统 1.先建立数据库HOTEL 2.选中HOTEL数据库

CREATE proc addguest
@orderid char(6),@prepay money
as
begin transaction
declare @guestID char(18),@guestName char(10),@roomid char(3)
update guestdata set guestEnterTime=getdate() 
where orderid=@orderid
select @roomid=RoomID from guestdata
where orderid=@orderid
update roomTab set orderflag='N',emptyflag='N',orderid=@orderid 
where RoomID=@RoomID
select @guestID=guestID,@guestName=guestName from guestdata where orderid=@orderid
insert into guestbill values(@guestID,@guestName,0,0,0,@prepay,0,@orderid)
commit transaction
GO

CREATE proc querycheckin as
select guestdata.orderid,guestName,guestSex,guestID,roomTab.RoomID,roomLevel,roomPrice,guestEnterTime
from guestdata,roomTab
where roomTab.orderflag='N' and roomTab.emptyflag='N' and guestdata.orderid=roomTab.orderid
order by guestdata.orderid ASC
GO

CREATE proc queryemptyroom as
   select * from roomTab where emptyflag='Y' and orderflag='N'
GO

CREATE proc regservice
@orderid char(6),@serviceID char(4)
as
begin transaction
declare @guestName char(10),@roomID char(3),@servicePrice money,@serviceName char(10)
select @guestName=guestName, @roomID=roomID from guestdata 
where orderid=@orderid
select @servicePrice=servicePrice,@serviceName=serviceName from serviceTable
where serviceID=@serviceID
insert into serviceData values(@orderid,@guestName,@roomID,@serviceID,getdate(),@servicePrice,@serviceName)
update guestbill set servfee=servfee+@servicePrice
where orderid=@orderid
commit transaction
GO

CREATE proc signin
@orderid char(6),@prepay money
as
begin transaction
declare @guestID char(18),@guestName char(10),@roomPrice money
update guestdata set guestEnterTime=getdate() 
where orderid=@orderid
update roomTab set orderflag='N',emptyflag='N'
where orderid=@orderid
select @roomPrice=roomPrice from roomTab where orderid=@orderid
select @guestID=guestID,@guestName=guestName from guestdata where orderid=@orderid
insert into guestbill values(@guestID,@guestName,@roomPrice,0,0,@prepay,0,@orderid)
commit transaction
GO


CREATE proc usercheckout
@orderid char(6)
as
begin transaction
declare @start datetime,@end datetime,@days int,@allfee money,@prepay money,@yingFu money
update guestdata set guestOutTime=getdate()
where orderid=@orderid
select @start=guestEnterTime,@end=guestOutTime from guestdata
where orderid=@orderid
set @days=DATEDIFF(day,@start,@end)+1
update guestbill set roomfee=roomfee*@days,allfee=servfee+roomfee
where orderid=@orderid
select @allfee=allfee,@prepay=prepay from guestbill
where orderid=@orderid
set @yingFu=@allfee-@prepay
update guestbill set yingFu=@yingFu
where orderid=@orderid
update roomTab set orderflag='N',emptyflag='Y',orderid=NULL
where orderid=@orderid
commit transaction
GO