一个简单的酒店系统的数据库设计

go

--12,统计当前的在住顾客数(利用客人入住时,outdate为空表示客人在住)
create proc guestnumber
as
   select count(*)as guestinroom from guestroom where outdate is null
go
   exec guestnumber
go

--13,基于房号显示客人享用的服务列表
create proc serverbyroom @roomid char(6)
as
begin
  if (select count(*) from guestroom where roomid=@roomid)<>0
     select a.serverid,a.servername,a.servercost from serverlist a
         where serverid in
              (select serverid from serveruse where guestid in
                 (select guestid from guestroom where roomid=@roomid ))
  else
     select  '无效房号或者没被占用的房号'
end 
go
  exec serverbyroom '1001'
  exec serverbyroom '1002'
--输入无效房号或者没被占用的房号
  exec serverbyroom '1003'
go

--14,删除顾客所享用的某项服务--是否应该带上消费日期-- 
create proc deleserveruse @guestname char(6),@servername char(20)
as
  delete serveruse
  where guestid =
         (select guestid from viewguest where guestname=@guestname)
  and serverid =
         (select serverid from serverlist where servername=@servername)
go
  exec deleserveruse '张生','洗车-小车'
  select * from serveruse
go

--15,随时查询顾客的应付金额
create proc guestcost @guestid int ,@total money output
as
  declare @servercost money,@roomcost money
  select @servercost=sum(servercost)
  from serverlist
  where serverid in
      (select serverid
       from serveruse
       where guestid=@guestid)
  select @roomcost=roomtypelist.roomcost
  from roomtypelist
  where roomtypeid in
      (select roomtypeid
       from roomlist

上一页 [1] [2] [3] [4] [5] [6] [7] [8] 下一页

复制本页网址和标题，发送给你QQ/Msn的好友一起分享

上一篇:数据库备份中可能出错的情况及解决办法

下一篇:collate chinese_prc_ci_as null 是什么意思