### 实践任务一：统计特定类型客房的入住人数
#### 需求描述
本练习旨在通过存储过程统计特定类型客房的入住总人数。
#### 技术提示
- 存储过程需接受一个输入参数，即客房类型名称。
- 使用`@result`作为输出参数，用于返回入住该类型客房的顾客总数。
```sql
USE Hotel;
GO
-- 检查并删除已存在的存储过程
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetGuestCountByType]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_GetGuestCountByType];
GO
-- 创建存储过程
CREATE PROCEDURE usp_GetGuestCountByType
@typeName VARCHAR(50), -- 客房类型名称
@result INT OUTPUT -- 返回值，居住在指定客房类型客房的顾客数
AS
BEGIN
SELECT @result = COUNT(*)
FROM GuestRecord gr
JOIN Room r ON gr.RoomID = r.RoomID
JOIN RoomType rt ON r.RoomTypeID = rt.TypeID
WHERE rt.TypeName = @typeName;
PRINT 'Total guests: ' + CAST(@result AS VARCHAR);
END;
GO
-- 调用存储过程示例
DECLARE @guestCount INT;
EXEC usp_GetGuestCountByType @typeName = 'Standard', @result = @guestCount OUTPUT;
PRINT 'Total guests in Standard rooms: ' + CAST(@guestCount AS VARCHAR);
```
### 实践任务二：根据房间号查询客房信息
#### 需求描述
通过存储过程，根据提供的房间号查询相关客房信息。若房间号为-1，则查询所有客房的信息。
#### 技术提示
- 在存储过程中使用IF语句判断输入的房间号是否为-1。
```sql
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetRoomDetails]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_GetRoomDetails];
GO
CREATE PROCEDURE usp_GetRoomDetails
@roomID INT
AS
BEGIN
IF @roomID = -1
BEGIN
SELECT r.RoomID, r.BedNum, r.RoomStateID, r.Description, r.GuestNum, r.RoomTypeID,
rt.TypeName, rt.TypePrice, rs.RoomStateName
FROM Room r
JOIN RoomType rt ON r.RoomTypeID = rt.TypeID
JOIN RoomState rs ON r.RoomStateID = rs.RoomStateID;
END
ELSE
BEGIN
SELECT r.RoomID, r.BedNum, r.RoomStateID, r.Description, r.GuestNum, r.RoomTypeID,
rt.TypeName, rt.TypePrice, rs.RoomStateName
FROM Room r
JOIN RoomType rt ON r.RoomTypeID = rt.TypeID
JOIN RoomState rs ON r.RoomStateID = rs.RoomStateID
WHERE r.RoomID = @roomID;
END
END;
GO
-- 调用存储过程示例
EXEC usp_GetRoomDetails @roomID = -1;
```
### 实践任务三：删除特定类型客房的记录
#### 需求描述
根据客房类型删除相关记录，并返回删除的记录数或错误代码。
#### 技术提示
- 输入参数为指定的客房类型名称。
- 使用`NOT EXISTS`关键字检查是否有关联的客房记录。
- 利用`@@ROWCOUNT`获取受影响的行数。
- 使用`RETURN`语句返回执行结果。
```sql
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DeleteRoomType]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_DeleteRoomType];
GO
CREATE PROCEDURE usp_DeleteRoomType
@typeName VARCHAR(50)
AS
BEGIN
DECLARE @typeID INT;
SELECT @typeID = TypeID FROM RoomType WHERE TypeName = @typeName;
IF NOT EXISTS (SELECT * FROM Room WHERE RoomTypeID = @typeID)
BEGIN
DELETE FROM RoomType WHERE TypeID = @typeID;
RETURN @@ROWCOUNT;
END
ELSE
BEGIN
RETURN -1;
END
END;
GO
-- 调用存储过程示例
DECLARE @result INT;
EXEC @result = usp_DeleteRoomType @typeName = 'Triple';
IF (@result > 0)
PRINT 'Deleted ' + CAST(@result AS VARCHAR) + ' records of room type ' + @typeName;
ELSE
PRINT 'Failed to delete records of room type ' + @typeName;
```
以上三个练习涵盖了SQL Server中存储过程的基本应用，包括数据查询、统计和删除操作。
京公网安备 11010802041100号