SQL Server存儲過程作業（二）

2024-08-31 00:54:28

字體：大中小

來源：轉載

供稿：網友

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 +'的記錄，失敗'

上一篇：SQL Server存儲過程作業（三）

下一篇：SQL Server存儲過程作業（一）