SQL Server存储过程作业(三)

9/1/2015来源:SQL技巧人气:1524

SQL Server存储过程作业(三)阶段4:练习——插入入住客人记录需求说明使用存储过程将入住客人信息插入客人信息表中,要求:检查身份证号必须是18个字符组成押金的默认值为1000元如果客人记录插入成功,输出客人流水号;否则输出出错信息提示:客人的信息作为存储过程的输入参数客人流水号作为存储过程的输出参数利用事务确保数据完整性客人记录插入客人信息表修改客人入住房间的人数
--阶段4:添加一个入住客人的信息IF EXISTS(SELECT * FROM sysobjects WHERE name='usp_insertGuestRecord')  DROP PROC usp_insertGuestRecordGOCREATE 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 0GO--调用存储过程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 = 1008SET @deposit = 1000SET @ResideDate = GETDATE()EXEC @Result = usp_insertGuestRecord @GuestID OUTPUT,@identityID,@guestName,                                     @roomID,@ResideDate,@deposit IF (@Result = 0)BEGIN  PRINT '插入客人记录操作成功'   PRINT '客人编号是' + CAST(@GuestID AS varchar)ENDELSE  PRINT '插入客人记录操作失败' 
阶段5:练习——使用视图查询正在维修的房间信息创建视图查询维修房间的信息,要求:要有房间号,房间名称,房间状态提示:连接房间客房信息表客房状态表客房类型表查询
--查询在维修状态的房间信息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
阶段6:练习——使用事物将已经退房的客户信息删除:提示:首先将已经退房的客户信息放到历史表里面(historyGuest)在将客人信息表里面的数据删除(GuestRecord)判断客人是否退房可以判断离开时间是否为空,不为空即是退房
--使用事物删除room表里面已经退房的旅客begin transaction declare @errorNum  int --错误的号码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