SQL Server存储过程作业(二)

9/1/2015来源:SQL技巧人气:1493

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