SQL Server存储过程作业（二）

阶段1：练习——统计某类型客房的入住客人人数

需求说明

使用存储过程统计在指定类型的客房入住客人的总人数

提示：

存储过程的输入参数是指定的客房类型名称

USE Hotel
GO

--阶段1：查询入住在指定客房类型的客房的顾客数
IF EXISTS(SELECT * FROM sysobjects WHERE name='usp_GetGuestNumByTypeName')
  DROP PROC usp_GetGuestNumByTypeName
GO
CREATE 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 @result
GO

--调用存储过程
SET NOCOUNT ON
DECLARE @Count int
DECLARE @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_GetRoomInfo
GO
CREATE procedure usp_GetRoomInfo
    @roomID int
AS
    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 = @roomID
GO

--调用存储过程
/*
DECLARE @RoomID int
SET @RoomID = 1008
EXEC 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_deleteRoomTypeById
GO
CREATE 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 -1
GO

--调用存储过程
DECLARE @RoomTYPE varchar(20)
DECLARE @Result int
SET @RoomTYPE  = '三人间'
EXEC @Result=usp_deleteRoomTypeById @RoomTYPE
IF (@Result > 0)
  PRINT '删除酒店客房类型是'+ @RoomTYPE +'的记录' + CAST(@Result AS varchar(10)) + '条'
ELSE
  PRINT '删除酒店客房类型是'+ @RoomTYPE +'的记录，失败'