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

use mastergo if exists(select * from sysdatabases where name='Houtel')drop database Houtelcreate Database Houtelon 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 Houtelgocreate 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 RoomTypeadd 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开始，递增值为1RoomStateName nvarchar(20) null)alter table RoomStateadd 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开始，递增值为1BedNum  int   null,                --床位数GuestNum int  null,                --入住客人数Description nvarchar(50) not null, --客房描述RoomStsteID int not null,          --客房状态编号RoomTypeID int not null            --客房类型编号 )alter table Roomadd constraint pk_RID primary key (RoomID)  --主键alter table Room                             add constraint df_bn default (2) for BedNum  --默认值为2alter table Roomadd constraint df_GN default (0) for GuestNum  --默认值为0alter table Roomadd constraint ck_GN check (GuestNum>=0)  --必须大于等于0alter table Roomadd 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 GuestRecordadd constraint pk_Gid primary key (GuestID) --主键alter table GuestRecordadd constraint ck_ident check (IdentityID=18) --字数必须等于18alter table GuestRecordadd constraint fk_RID foreign key(RoomID) references Room(RoomID)  --外键alter table GuestRecordadd constraint fk_Resid foreign Key(ResideId) references ResideState(ResideId) --外键alter table GuestRecordadd constraint df_Resid default ('未结账') for ResideId alter table GusetRecordadd constraint ck_LD check (LeaveDate>=ResideDate)--根据输入的客房类型名称,统计入住的客人总人数create procedure usp_GetGuestNum @TypeName varchar(32),@GuestNum int outputasselect @GuestNum=COUNT(*) from GuestRecordwhere RoomID in(  select RoomID from Room  where RoomTypeID in  (    select RoomTypeID from RoomType    where TypeName=@TypeName  ))--调用存储过程declare @num intexec usp_GetGuestNum '小小',@num outputprint 'total:'+convert(nvarchar(20),@num) --阶段2:根据房间号查询客房信息--通过房间号查询客房的相关信息--如果房间号为-1，表示查询所有客房信息，包括客房ID，床位数，--客房状态ID,客房类型和房价。create procedure usp_RoomInfo @roomnum intas if(@roomnum=-1) begin     select Room.RoomID,Room.BedNum,Room.RoomStsteID,TypeName,TypePricefrom Room,RoomTypewhere Room.RoomTypeID=RoomType.TypeID endelsebeginselect Room.RoomID,Room.BedNum,Room.RoomStsteID,TypeName,TypePricefrom Room,RoomTypewhere Room.RoomTypeID=RoomType.TypeIDand Room.RoomID=@roomnumend--调用存储过程exec usp_RoomInfo 1008--阶段3：删除某种客房类型记录--需求说明：根据客房类型删除客房类型记录，--如果操作成功(客房信息表Room没有--对应记录才可以删除)，返回删除的记录数，否则返回-1select * from RoomTypeselect * from Roomcreate procedure  usp_DelListByType  @typeName nvarchar(20)as if not exists (   select RoomTypeId from room   where RoomTypeID in   (     select TypeID from RoomType         where TypeName=@typeName   ) ) begindelete from RoomTypewhere TypeName=@typeName    return @@rowcountendelse  return -1--调用存储过程declare @result intexec @result=usp_DelListByType '标准间'if(@result>0)print '删除了'+convert(nvarchar(20),@result)+'条记录'elseprint @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 outputas --检查身份证号  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 outputif(@guestid>0)begin  print '操作成功，客户编号是'+convert(nvarchar(20),@guestid)endelse print '操作失败'