你的位置:首页 > 数据库

[数据库]SQL Server存储过程作业(二)


阶段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 +'的记录,失败'