一个简单的酒店系统的数据库设计

--2,空余房间数,roomstatus=0的房间表示空余.
create proc roomisvoid @roomcount int output
as
select @roomcount=count(*) from roomlist where roomstatus=0
go
declare @count int
execute roomisvoid @count output
select @count as '当前空余房间数'
go

--3,空房间列表,roomstatus=0的房间表示空余.
create proc roomvoidlist
as
select roomid as '空房间号码'  from roomlist where roomstatus=0 
order by roomid
go
exec roomvoidlist
go

--4,空双人房列表,这里规定roomstatus=0时为空缺状态. 
create proc roomdoublevoid
as
select roomid,'空双人房' from roomlist where roomtypeid in(
select roomtypeid from roomtypelist where roomtype='双人高级房' )
and roomstatus=0
       order by roomid
go
exec roomdoublevoid
go

--5,空单人房列表
create proc roomonevoid
as
select roomid,'空单人房' from roomlist
where roomtypeid in(
select roomtypeid from roomtypelist where roomtype='单人高级房'
 and roomstatus=0) order by roomid
go
exec roomonevoid
go

--6,服务列表（procserverlist）
--表名与 过程名不能相同
create proc procserverlist
as
select '服务名称'=servername,'服务价格'=servercost
from serverlist order by serverid
go
exec procserverlist
go
--7,根据顾客名字查看他的详细信息(包括房间号)
/*alter proc guestinfobyname @guestname char(10)
as
select * from guest where guestname=@guestname
union
select * from guestroom where guestid in
(select guestid from guest where guest=@guestname)
*/
create proc guestinfobyname @guestname char(10)
as
select b.roomid ,a.* from guest a join guestroom b
on a.guestid=b.guestid
where a.guestname=@guestname
go
exec guestinfobyname '张生'
go

--8,根据房间号可查看相应顾客的信息

上一页 [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] 下一页

复制本页网址和标题，发送给你QQ/Msn的好友一起分享

上一篇:数据库备份中可能出错的情况及解决办法

下一篇:collate chinese_prc_ci_as null 是什么意思