--階段4：添加一個(gè)入住客人的信息IF EXISTS(SELECT * FROM sysobjects WHERE name='usp_insertGuestRecord')  DROP PROC usp_insertGuestRecordGOCREATE procedure usp_insertGuestRecord    @GuestID int OUTPUT,                ---客戶流水號(hào)    @identityID varchar(50),            ---身份證號(hào)    @guestName nchar(20),                ---客戶姓名    @roomID int,                        ---房間號(hào)    @ResideDate datetime,                ---入住時(shí)間    @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 = '已入住'    ---客房狀態(tài)變?yōu)?ldquo;入住”，客人數(shù)量增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 0GO--調(diào)用存儲(chǔ)過程DECLARE @identityID varchar(50)            ---身份證號(hào)DECLARE @guestName nchar(20)            ---客戶姓名DECLARE @roomID int                        ---房間號(hào)DECLARE @deposit decimal(18,2)            ---押金DECLARE @ResideDate datetime            ---入住時(shí)間DECLARE @Result varchar(20)DECLARE @GuestID intSET @identityID = '11010119950506112x'SET @guestName = '風(fēng)無痕'SET @roomID = 1008SET @deposit = 1000SET @ResideDate = GETDATE()EXEC @Result = usp_insertGuestRecord @GuestID OUTPUT,@identityID,@guestName,                                     @roomID,@ResideDate,@deposit IF (@Result = 0)BEGIN  PRINT '插入客人記錄操作成功'   PRINT '客人編號(hào)是' + CAST(@GuestID AS varchar)ENDELSE  PRINT '插入客人記錄操作失敗' 

--查詢在維修狀態(tài)的房間信息create view  RoomStateNameas    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

--使用事物刪除room表里面已經(jīng)退房的旅客begin transaction declare @errorNum  int --錯(cuò)誤的號(hào)碼set @errorNum=0select *  into historyGuest from  GuestRecordwhere LeaveDate is not nullset @errorNum=@errorNum+@@ERRORdelete  from  GuestRecordwhere LeaveDate is not nullset @errorNum=@errorNum+@@ERRORif(@errorNum<>0)beginprint '刪除失敗，事物回滾'rollback transactionendelsebeginprint '刪除成功'commit transactionend