SQL Server存储过程作业（三）

阶段4：练习——插入入住客人记录需求说明使用存储过程将入住客人信息插入客人信息表中，要求：检查身份证号必须是18个字符组成押金的默认值为1000元如果客人记录插入成功，输出客人流水号；否则输出出错信息提示：客人的信息作为存储过程的输入参数客人流水号作为存储过程的输出参数利用事务确保数据完整性客人记录插入客人信息表修改客人入住房间的人数

--阶段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 intSET @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 '插入客人记录操作失败' 

阶段5：练习——使用视图查询正在维修的房间信息创建视图查询维修房间的信息，要求：要有房间号，房间名称，房间状态提示：连接房间客房信息表客房状态表客房类型表查询 

--查询在维修状态的房间信息
create view  RoomStateName
as
    select Room.RoomID,RoomType.TypeName,
    RoomState.RoomStateName
    from Room
    inner  join RoomType on RoomType.TypeID=Room.RoomTypeID
    inner join RoomState on RoomState.RoomStateID=Room.RoomStateID
    where RoomState.RoomStateName='维修'
goselect * from RoomStateName

阶段6：练习——使用事物将已经退房的客户信息删除：提示：首先将已经退房的客户信息放到历史表里面(historyGuest)在将客人信息表里面的数据删除(GuestRecord)判断客人是否退房可以判断离开时间是否为空，不为空即是退房  

--使用事物删除room表里面已经退房的旅客
begin transaction
declare @errorNum  int --错误的号码
set @errorNum=0
select *  into historyGuest from  GuestRecord
where LeaveDate is not null
set @errorNum=@errorNum+@@ERROR
delete  from  GuestRecord
where LeaveDate is not null
set @errorNum=@errorNum+@@ERROR
if(@errorNum<>0)
begin
print '删除失败，事物回滚'
rollback transaction
end
else
begin
print '删除成功'
commit transaction
end