优化MySchool 第八章 酒店管理系统

use master
go 
if exists(select * from sysdatabases where name='Houtel')
drop database Houtel

create Database Houtel
on primary
( 
--数据文件
name='Houtel_data',
filename='E:\\Houtel_data.mdf',
size=5mb,
maxsize=100mb,
filegrowth=15%
)
log on
(
--日志文件
name='Houtel_log',
filename='E:\\Houtel_log.ldf',
size=2mb,
filegrowth=1mb
)


use Houtel
go
create table RoomType  --客房类型表
(
TypeID int identity(1,1) not null,       --标志列从1开始，递增值为1，编号
TypeName nvarchar(20) null,              --名称
TypePrice Decimal null                   --价格
)

alter table RoomType   --主键
add  constraint pk_ID primary key (TypeID)

alter table RoomType
add constraint Ck_Price check (TypePrice>=0)   --客房价格大于等于零

insert into RoomType (TypeName,TypePrice) values ('标准',CAST(180.00 AS Decimal(18, 2)))
insert into RoomType (TypeName,TypePrice) values ('单人', CAST(128.00 AS Decimal(18, 2)))
insert into RoomType (TypeName,TypePrice) values ('双人', CAST(208.00 AS Decimal(18, 2)))


create table RoomState   --客房状态表
(
RoomStateID  int identity(1,1) not  null,  --房间状态编号，标志列从1开始，递增值为1
RoomStateName nvarchar(20) null
)
alter table RoomState
add constraint pk_St primary key (RoomStateID)  --主键

insert into RoomState (RoomStateName) values ('结账')
insert into RoomState (RoomStateName) values ('未结账')



create table Room  --客房信息表
(
RoomID int identity(1,1) not null, --房间号，主键，标志列从1开始，递增值为1
BedNum  int   null,                --床位数
GuestNum int  null,                --入住客人数
Description nvarchar(50) not null, --客房描述
RoomStsteID int not null,          --客房状态编号
RoomTypeID int not null            --客房类型编号 
)
alter table Room
add constraint pk_RID primary key (RoomID)  --主键

alter table Room                             
add constraint df_bn default (2) for BedNum  --默认值为2

alter table Room
add constraint df_GN default (0) for GuestNum  --默认值为0

alter table Room
add constraint ck_GN check (GuestNum>=0)  --必须大于等于0

alter table Room
add constraint fk_RoomStsteID foreign key(RoomStsteID) references  RoomState(RoomStsteID)  --外键约束

insert into Room(BedNum,GuestNum,Description,RoomStsteID,RoomTypeID) values (3,2,'标准间',1,1)
insert into Room(BedNum,GuestNum,Description,RoomStsteID,RoomTypeID) values (4,3,'双人标准间',2,1)
insert into Room(BedNum,GuestNum,Description,RoomStsteID,RoomTypeID) values (2,2,'标准间',2,2)
insert into Room(BedNum,GuestNum,Description,RoomStsteID,RoomTypeID) values (4,4,'豪华标准间',1,2)


--结账状态表
CREATE TABLE [dbo].[ResideState](
	[ResideId] [int] IDENTITY(1,1) NOT NULL,
	[ResideName] [varchar](50) NULL,
 CONSTRAINT [PK_ResideState] PRIMARY KEY CLUSTERED 
(
	[ResideId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

insert into ResideState(ResideName) values ('已入住')
insert into ResideState(ResideName) values ('空闲')
insert into ResideState(ResideName) values ('维修')



create table GuestRecord  --客人信息表
(
GuestID int identity(1,1) not null,  --客人入住流水号
GuestName  nvarchar(20) not null,    --客人姓名
IdentityID nvarchar(30)not null,        --身份证号
RoomID int  null,                 --房间号
ResideId int  null,               --结账状态编号 
ResideDate datetime   null,       --入住日期
LeaveDate datetime  null,         --结账日期
Deposit decimal(18,2)  null,            --押金
TotalMoney decimal(18,2)  null          --总金额
)

alter table GuestRecord
add constraint pk_Gid primary key (GuestID) --主键

alter table GuestRecord
add constraint ck_ident check (IdentityID=18) --字数必须等于18

alter table GuestRecord
add constraint fk_RID foreign key(RoomID) references Room(RoomID)  --外键

alter table GuestRecord
add constraint fk_Resid foreign Key(ResideId) references ResideState(ResideId) --外键

alter table GuestRecord
add constraint df_Resid default ('未结账') for ResideId 

alter table GusetRecord
add constraint ck_LD check (LeaveDate>=ResideDate)





--根据输入的客房类型名称,统计入住的客人总人数
create procedure usp_GetGuestNum 
@TypeName varchar(32),
@GuestNum int output
as

select @GuestNum=COUNT(*) from GuestRecord
where RoomID in
(
  select RoomID from Room
  where RoomTypeID in
  (
    select RoomTypeID from RoomType
    where TypeName=@TypeName
  )
)
--调用存储过程
declare @num int
exec usp_GetGuestNum '小小',@num output
print 'total:'+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.RoomStsteID,TypeName,TypePrice
	from Room,RoomType
	where Room.RoomTypeID=RoomType.TypeID
 end

else
begin
	select Room.RoomID,Room.BedNum,Room.RoomStsteID,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,  --押金的默认值为1000元
  @TotalMoney decimal(18,2),
  @guestID int output
as
 --检查身份证号
  if(len(@identityID)!=18)
  begin
    print '身份证号只能是18位'
    return
  end

  --插入记录
  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
   --insert或update这两个过程中至少有一个出了错
   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 '操作失败'