你的位置:首页 > 数据库

[数据库]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