第八章：课程总复习项目

最新推荐文章于 2021-01-18 10:36:22 发布

Star_Gemini

最新推荐文章于 2021-01-18 10:36:22 发布

阅读量334

点赞数

本文链接：https://blog.csdn.net/qq_36074093/article/details/57387123

版权

--1
alter procedure usp_GetTotalNum
@Typename nvarchar(32),
@GuestNum int output
as
select @GuestNum=COUNT(*) from GuestRecord
where RoomID in
(
select RoomID from Room
where RoomTypeID in
(
select TypeID from RoomType
where TypeName=@Typename
)
)
declare @num int
exec usp_GetTotalNum 'Single',@num output
print 'total：'+convert(nvarchar(20),@num)
select * from guestrecord
--2
create procedure usp_RoomInfo
@roomnum int
as
if(@roomnum=-1)
begin
select room.RoomID,Room.BedNum,Room.RoomStateID,TypeName,TypePrice
from room,roomtype
where room.roomtypeid=Roomtype.TypeID
end
else
begin
select room.RoomID,Room.BedNum,Room.RoomStateID,TypeName,TypePrice
from room,roomtype
where room.roomtypeid=Roomtype.TypeID
and Room.RoomID=@roomnum
end
exec usp_RoomInfo 1008
--3
select * from roomtype
select * from room
create procedure usp_DelListByType
@typeName nvarchar(20)
as
if not exists
(
select RoomTypeId from room
where roomtypeid in
(
select typeid
from roomtype
where typename=@typename
)
)
begin
delete from roomtype
where typeName=@typename
return @@rowcount
end
else
return -1
declare @result int
exec @result=usp_DelListByType '标准间'
if(@result>0)
print '删除了'+convert(nvarchar(20),@result)+'条记录'
else
print @result
--4
create Proc usp_insertGuestRecord
@identityID nvarchar(20),
@guestName nvarchar(20),
@roomID int,
@resideID int,
@resideDate datetime,
@leaveDate datetime,
@Deposit decimal(18,2)=1000,
@TotalMoney decimal(18,2),
@guestID int output
as
if(len(@identityID)!=18)
begin
print '身份证号只能是18位'
return
end
begin tran
declare @ErrorSum int
insert into GuestRecord
values(@identityID,@guestName,@roomID,@resideID,
@resideDate,@leaveDate,@Deposit,@TotalMoney)
set @ErrorSum=@ErrorSum+@@Error
declare @roomStateID int
select @roomStateID=RoomStateID from roomState
where RoomStateName='已入住'
update room set RoomStateID=@roomStateID
where RoomID=@roomID
set @ErrorSum=@ErrorSum+@@Error
if(@ErrorSum>0)
begin
rollback tran
end
else
begin
commit tran
set @guestID=@@Identity
end
declare @guestID int
exec usp_insertGuestRecord '410523198806267523','李小龙',1008,1,'2013-3-11','2013-3-15',500,500,@guestID output
if(@guestID>0)
begin
print '操作成功，客户编号是'+convert(nvarchar(20),@guestID)
end
else
print '操作失败'