指导学习:课程总复习

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))
  ----------------------------阶段二
  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
----------------------------------------------阶段三
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 +'的记录，失败'
  --------------------------------------------------------------------------------------------------
  --阶段4：添加一个入住客人的信息
IF EXISTS(SELECT * FROM sysobjects WHERE name='usp_insertGuestRecord')
  DROP PROC usp_insertGuestRecord
GO
CREATE procedure usp_insertGuestRecord
    @GuestID int OUTPUT,				---客户流水号
	@identityID varchar(50),			---身份证号
	@guestName nchar(20),				---客户姓名
	@roomID int,						---房间号
	@ResideDate datetime,            	---入住时间
	@deposit decimal(18,2) = 1000		---押金

AS
    SET @GuestID = -1
	IF (@identityID IS NULL OR LEN(@identityID) <> 18)
		return -1

	BEGIN TRANSACTION
	INSERT INTO GuestRecord
	(
		IdentityID,
		GuestName,
		RoomID,
		ResideID,
		ResideDate,
		Deposit)
	VALUES
	(
		@identityID,
		@guestName,
		@roomID,
		1,
		@ResideDate,
		@deposit
	)
	IF (@@ERROR <> 0)
	BEGIN
		ROLLBACK TRANSACTION
		return 'false'
	END

	DECLARE @RoomStateID int
	SELECT @RoomStateID=RoomStateID FROM RoomState
	WHERE RoomStateName = '已入住'
	---客房状态变为“入住”，客人数量增1
	Update Room set RoomStateID =@RoomStateID,GuestNum=GuestNum+1
	 WHERE RoomID = @roomID
	IF (@@ERROR <> 0)
	BEGIN
		ROLLBACK TRANSACTION
		return -1
	END
	COMMIT TRANSACTION
	SET @GuestID=@@IDENTITY
	return 0
GO

--调用存储过程
DECLARE @identityID varchar(50)			---身份证号
DECLARE @guestName nchar(20)			---客户姓名
DECLARE @roomID int						---房间号
DECLARE @deposit decimal(18,2)			---押金
DECLARE @ResideDate datetime			---入住时间
DECLARE @Result varchar(20)
DECLARE @GuestID int

SET @identityID = '11010119950506112x'
SET @guestName = '风无痕'
SET @roomID = 1008
SET @deposit = 1000
SET @ResideDate = GETDATE()

EXEC @Result = usp_insertGuestRecord @GuestID OUTPUT,@identityID,@guestName,
                                     @roomID,@ResideDate,@deposit 
IF (@Result = 0)
BEGIN
  PRINT '插入客人记录操作成功' 
  PRINT '客人编号是' + CAST(@GuestID AS varchar)
END
ELSE
  PRINT '插入客人记录操作失败'