无标题1.sql

酒店管理系统项目需求说明 第一部分 引言 1 1.1编写目的 1 1.2背景 1 第二部分 任务概述 2 3.1实现目标 2 3.1.1客房类型模块： 2 3.1.2客房信息

---*--*-*--*-*-**-*-*-*--*-*---**视图
drop view view_Room
create view view_Room
as

select number ,roomtype.typename,bednumber,guestnumber,state
 from room inner join roomType on room.typeID=roomtype.typeid 
 
select * from view_Room
-------
drop view view_room_roomtype
create view view_room_roomtype
as
SELECT  room.roomid,dbo.Room.Number, dbo.RoomType.TypeName,
      dbo.Room.State, dbo.Room.GuestNumber,dbo.Room.BedNumber,
      dbo.RoomType.TypePrice, dbo.RoomType.IsAddBed, 
      dbo.RoomType.AddBedPrice,dbo.Room.Description
FROM dbo.Room INNER JOIN
      dbo.RoomType ON dbo.Room.TypeID = dbo.RoomType.TypeID
select * from view_room_roomtype

-----------
drop view View_RoomInfo
CREATE VIEW View_RoomInfo
AS
SELECT dbo.Room.RoomID, dbo.Room.Number, dbo.RoomType.TypeName, 
      dbo.Room.BedNumber, dbo.Room.State, dbo.Room.GuestNumber, 
      dbo.Room.Description
FROM dbo.Room INNER JOIN
      dbo.RoomType ON dbo.Room.TypeID = dbo.RoomType.TypeID

GO
select * from View_RoomInfo
------

drop view View_RoomInfoByFilter
CREATE VIEW View_RoomInfoByFilter
AS
SELECT     dbo.Room.RoomID, dbo.Room.Number, dbo.Room.BedNumber, dbo.Room.Description, dbo.Room.State, dbo.Room.GuestNumber, 
                      dbo.RoomType.TypeName
FROM         dbo.Room INNER JOIN
                      dbo.RoomType ON dbo.Room.TypeID = dbo.RoomType.TypeID

GO
select * from View_RoomInfoByFilter

--------
drop view View_RoomType
CREATE VIEW View_RoomType
AS
SELECT TypeName, TypePrice, IsAddBed, AddBedPrice, Remark
FROM dbo.RoomType

GO
select * from View_RoomType
--------

drop view View_UserReception
CREATE VIEW View_UserReception
AS
SELECT     dbo.HotelUser.UserID, dbo.HotelUser.UserName, dbo.HotelUser.Name, dbo.Room.RoomID, dbo.Room.Number, dbo.RoomType.TypeName, 
                      dbo.UserRoom.State
FROM         dbo.RoomType INNER JOIN
                      dbo.Room ON dbo.RoomType.TypeID = dbo.Room.TypeID INNER JOIN
                      dbo.UserRoom ON dbo.Room.RoomID = dbo.UserRoom.RoomID INNER JOIN
                      dbo.HotelUser ON dbo.UserRoom.UserID = dbo.HotelUser.UserID

GO
select * from View_UserReception


----*-*--*-*-*--**-*-*-*-*-*-

select * from hoteluser
select * from room 
select * from roomtype
select * from userroom

------------------------存储过程--------*-*----*-*-*-*-*-***-
-------------------------------------*-*-*-*--*--*-*-***-
create proc UPD_State
@roomid int
as 
update userroom set state = 
(select state from room where roomid = @roomid) 
where roomid = @roomid
---------
create proc DEL_HOTELUSER
@UserID int 
as 
delete from HotelUser where userId = @UserID 
and Status <> 1 
and (select count(*) from userroom where userid = @UserID)=0

---------
create proc DEL_ROOM
@RoomID int
as
if(select State from room where roomid = @RoomID)<>'入住'
delete from room where roomid = @roomid
----------
create proc DEL_ROOM1
@RoomID int
as
if(select State from room where roomid = @RoomID)!='入住'
select * from room
else
begin
delete from room where roomid = @roomid
end

--------
CREATE PROC DEL_TAB1
   -- @typeName varchar(50) = '商业房'
  @typeid int
AS
  if (
	select count(*) from  Room where TypeId=
		(select typeid from roomtype where typeid=@typeid)
	)=0
	begin
	delete from roomtype where typeid=@typeid
	end
else
	begin
	print 'aaaa'
	end
----------
create proc SEL_ROOM

@TypeName nvarchar(50),
@BedNumber int,
@GuestNumber int
AS
select roomid,number ,roomtype.typename,bednumber,guestnumber,state
from room inner join roomType on 
room.typeid = roomtype.typeid 
and roomtype.typeName=@typename
and room.bednumber>=@bednumber 
and room.guestNumber>=@GuestNumber
----------

create proc SEL_ROOM1
as
select roomid, number ,roomtype.typename,bednumber,guestnumber,state
 from room inner join roomType on room.typeID=roomtype.typeid 

---------
create proc SEL_ROOM2
@TypeName nvarchar(50),
@BedNumber int,
@GuestNumber int
as
select count(*) from room inner join roomtype on 
room.typeid = roomtype.typeid
and roomtype.typename=@typename
and room.bednumber>=@bednumber
and room.GuestNumber>=@guestNumber

-------
create proc SEL_UserRoom
@UserName nvarchar(50),
@Number nvarchar(50)
as
select count(*) from userroom where userid=
(select userid from hoteluser where username = @UserName)
and roomid = (select roomid from room where number = @Number)

exec SEL_UserRoom 'aaa','1003'

insert into userroom values (2,2,'已预订')
insert into userroom values (2,3,'已预订')
insert into userroom values (3,2,'已预订')