11

use hotel
select * from GuestRecord  --客户记录表
select * from dbo.ResideState  --居住状态(是否结账)
select * from dbo.Room  --房间信息
select * from RoomState  --放间状态
select * from RoomType --房间类型

--分析表结构

--阶段1：指导--统计某类型客房的入住客人人数
--需求：统计入住过指定客房类型的客人总人数
--分析：根据用户传入的类型，获取该类型客房的客人总数 
create procedure usp_GetNumByType
 @TypeName nvarchar(20),
 @num int output
as
select @num= count(*) from GuestRecord
where roomid in 
 (
   select roomid from room where roomtypeid in
   (
      select typeid from roomtype
      where typename=@typename
   )
 )
--调用存储过程：
--标准间
--单人间
--三人间
--总统套房
--长包房
--豪华标准间
--单人套房
--双人套房
select * from guestrecord


declare @num int
exec usp_GetNumByType '标准间',@num output
print '入住酒店标准间的客人总人数是：'+convert(nvarchar(20),@num)




--阶段2练习 --根据房间号查询客房信息
--通过房间号查询客房的相关信息
--如果房间号为-1，表示查询所有客房信息，包括客房ID，床位数，
--客房状态ID,客房类型和房价。
create procedure usp_RoomInfo
 @roomnum int
as 
if(@roomnum=-1)
 begin
   --查询所有客房信息
   select room.RoomID,Room.BedNum,Room.RoomStateID,TypeName,TypePrice
    from room,roomtype
    where room.roomtypeid=Roomtype.TypeID
 end

else
begin
    select room.RoomID,Room.BedNum,Room.RoomStateID,TypeName,TypePrice
    from room,roomtype
    where room.roomtypeid=Roomtype.TypeID
    and Room.RoomID=@roomnum
end
--调用
exec usp_RoomInfo 1008

--阶段3：删除某种客房类型记录
--需求说明：根据客房类型删除客房类型记录，
--如果操作成功(客房信息表Room没有
--对应记录才可以删除)，返回删除的记录数，否则返回-1
select * from roomtype
select * from room

create procedure  usp_DelListByType
  @typeName nvarchar(20)
as
 if not exists
 (
   select RoomTypeId from room
   where roomtypeid in
   (
     select typeid
     from roomtype
     where typename=@typename
   )    
 )
 begin
    delete from roomtype
    where typeName=@typename
    return @@rowcount
end
else
return -1

--调用
declare @result int
exec @result=usp_DelListByType '标准间'
if(@result>0)
print '删除了'+convert(nvarchar(20),@result)+'条记录'
else
print @result

--阶段4：练习--插入入住客人记录
 --需求说明：使用存储过程将入住客人信息插入客人信息表中，
 --检查身份证号必须是18个字符组成
 --押金的默认值为1000元
 --将客人入住房间的当前状态设置为"入住"的状态编号
 --如果客人记录插入成功，输出客人流水号。
 create Proc usp_insertGuestRecord
  @identityID nvarchar(20),
  @guestName nvarchar(20),
  @roomID int,
  @resideID int,
  @resideDate datetime,
  @leaveDate datetime,
  @Deposit decimal(18,2)=1000,
  @TotalMoney decimal(18,2),
  @guestID int output
as
 --检查身份证号
  if(len(@identityID)!=18)
  begin
    print '身份证号只能是18位'
    return
  end
  --押金的默认值为1000元(在参数的时候已经设置)
  --插入记录
  begin tran
   declare @ErrorSum int
   insert into GuestRecord
   values(@identityID,@guestName,@roomID,@resideID,
   @resideDate,@leaveDate,@Deposit,@TotalMoney)
   set @ErrorSum=@ErrorSum+@@Error
   
   --将客人入住房间的当前状态设置为"入住"的状态编号
   declare @roomStateID int
   select @roomStateID=RoomStateID from roomState
   where RoomStateName='已入住'
   
   update room set RoomStateID=@roomStateID
   where RoomID=@roomID
   set @ErrorSum=@ErrorSum+@@Error
   if(@ErrorSum>0)
   begin
     rollback tran
   end
   else
   begin
   commit tran
   set @guestID=@@Identity
   end
   
 --调用
 declare @guestID int
 exec usp_insertGuestRecord 

'410523198806267523','李小龙',1008,1,'2013-3-11','2013-3-15',500,500,@guestID output
if(@guestID>0)
begin
  print '操作成功，客户编号是'+convert(nvarchar(20),@guestID)
end
else
 print '操作失败'
 
 use Myschool
 
 --分组总结