阶段1：练习——统计某类型客房的入住客人人数
需求说明
使用存储过程统计在指定类型的客房入住客人的总人数
提示：
存储过程的输入参数是指定的客房类型名称
USE HotelGO--阶段1：查询入住在指定客房类型的客房的顾客数IF EXISTS(SELECT * FROM sysobjects WHERE name='usp_GetGuestNumByTypeName') DROP PROC usp_GetGuestNumByTypeNameGOCREATE PROCEDURE usp_GetGuestNumByTypeName @typeName varchar(50), ----客房类型名称 @result int OUTPUT ---返回值，居住在指定客房类型客房的顾客数AS SELECT @result = count(1) FROM GuestRecord WHERE RoomID IN (SELECT roomID FROM Room WHERE RoomTypeID= (SELECT TypeID FROM RoomType WHERE TypeName = @typeName)) PRINT @resultGO--调用存储过程SET NOCOUNT ONDECLARE @Count intDECLARE @RoomType varchar(20)SET @RoomType = '标准间'EXEC usp_GetGuestNumByTypeName @RoomType,@Count OUTPUT PRINT '入住酒店' + @RoomType + '的客人总人数是:' + CAST(@Count AS varchar(10))
阶段2：练习——根据房间号查询客房信息
需求说明
通过房间号查询客房的相关信息
如果房间号为-1表示查询所有客房信息
提示：
在存储过程中，使用IF语句判断输入参数是否为-1
IF EXISTS(SELECT * FROM sysobjects WHERE name='usp_GetRoomInfo') DROP PROC usp_GetRoomInfoGOCREATE procedure usp_GetRoomInfo @roomID intAS IF @roomID=-1 SELECT a.RoomID, a.BedNum, a.RoomStateID, a.Description, a.GuestNum, a.RoomTypeID, b.TypeName, b.TypePrice, RTRIM(c.RoomStateName) AS RoomStateName FROM [Room] a INNER JOIN [RoomType] b ON a.RoomTypeID = b.TypeID INNER JOIN [RoomState] c ON a.RoomStateID = c.RoomStateID ELSE SELECT a.RoomID, a.BedNum, a.RoomStateID, a.Description, a.GuestNum, a.RoomTypeID, b.TypeName, b.TypePrice, RTRIM(c.RoomStateName) AS RoomStateName FROM Room a INNER JOIN [RoomType] b ON a.RoomTypeID = b.TypeID INNER JOIN [RoomState] c ON a.RoomStateID = c.RoomStateID WHERE roomID = @roomIDGO--调用存储过程/*DECLARE @RoomID intSET @RoomID = 1008EXEC usp_GetRoomInfo @RoomID*/EXEC usp_GetRoomInfo -1
阶段3：练习——删除某种客房类型居住记录
需求说明
根据客房类型删除客房类型记录
如果操作成功，返回删除的记录数；否则返回-1
提示：
输入参数是指定的客房类型名称
使用NOT EXISTS关键字判断客房信息表是否存在要删除的客房类型
利用全局变量@@ROWCOUNT获得受影响的记录数
利用return语句返回执行结果
IF EXISTS(SELECT * FROM sysobjects WHERE name='usp_deleteRoomTypeById') DROP PROC usp_deleteRoomTypeByIdGOCREATE PROCEDURE usp_deleteRoomTypeById @typeName varchar(20) ----客房类型AS DECLARE @typeID int SELECT @typeID=TypeID FROM RoomType WHERE TypeName = @typeName IF NOT EXISTS (SELECT * FROM Room WHERE RoomTypeID = @typeID) --Room表里没有相关信息时才删除 BEGIN DELETE FROM RoomType WHERE TypeID=@typeID return @@ROWCOUNT END ELSE return -1GO--调用存储过程DECLARE @RoomTYPE varchar(20)DECLARE @Result intSET @RoomTYPE = '三人间'EXEC @Result=usp_deleteRoomTypeById @RoomTYPEIF (@Result > 0) PRINT '删除酒店客房类型是'+ @RoomTYPE +'的记录' + CAST(@Result AS varchar(10)) + '条'ELSE PRINT '删除酒店客房类型是'+ @RoomTYPE +'的记录，失败'