【雪野实训记录】Oracle宾馆管理系统-L2综合项目案例

/*宾馆管理系统-综合项目
第一部分 案例描述
案例目的
  学习并巩固oracle数据库编程技术，包括存储过程、触发器、索引、视图、序列、同义词、事务、游标等，培养学生对数据库设计和程序的能力。
案例难度
  ★★★★
案例覆盖技能点
1、  存储过程
2、  触发器
3、  索引
4、  视图
5、  序列、同义词
6、  事务
7、  游标
8、  函数
推荐案例完成时间
   2天
适用课程和对象
  Oracle数据库设计
第二部分  需求和开发环境
使用技术和开发环境
  Oracle 10g
项目背景
随着我国改革开放的深入，宾馆服务业的竞争日益激烈，一个宾馆要想立于不败之地，就必须提高整体竞争能力，变革宾馆的管理模式，提高管理水平，实施信息化建设无疑是实现这一目的的必由之路和明智之举。目前，我国宾馆服务业的信息化管理进展缓慢，在激烈的竞争中，如何能把握机会，保持自己的优势，立于不败之地呢？这就需要提供最好的服务，提供最完善的设施和最先进的技术。一个成功的宾馆，其经营者不仅要提高服务水平和服务质量，从而提高客房占有率和回头率，还要有好的工作效率，并控制成本。在信息时代，更重要的是还必须要有一个完善的信息管理系统，以方便客人和更好地管理宾馆。
信息管理系统就是我们常说的MIS（Management Information System），在强调管理，强调信息的现代社会中它变得越来越普及。传统的登记表的做法极大的影响了工作流程效率和数据的正确性、完整性、安全性，已经逐渐落后于时代。利用软件管理系统代替手工的宾馆管理，将会大大提高工作效率。
案例需求
宾馆的主要活动首先可分为四个部分，即预订管理、入住管理、消费管理和退房结算管理。
 
预订管理主要包括登记客人的预订信息，查询预订信息，同时还需要注意预订信息不能出现冲突现象，例如两个客人都预订了同一天的同一个房间，这是不允许的；此外，在快到预订时确定的客人预抵时间时，接待人员要打电话证明客人是否能按时入住，如果不能，就会把预订单作废，或者称为失效；


除了按流程划分的这四个部分之外，还有两个部分：客房管理和用户管理。；这两部分信息需要在客人入住以前提前设定好。

*/

—————————————————————————————————————————————————————————————————————————————

第一部分 项目描述

1.1项目目的

               学习并巩固oracle数据库编程技术，包括存储过程、触发器、索引、视图、序列、同义词、事务、游标等，培养学生对数据库设计和程序的能力。第二部分 需求和开发环境

2.1使用技术和开发环境

               Oracle11g

2.2项目需求

信息管理系统就是我们常说的MIS（Management Information System），在强调管理，强调信息的现代社会中它变得越来越普及。传统的登记表的做法极大的影响了工作流程效率和数据的正确性、完整性、安全性，已经逐渐落后于时代。利用软件管理系统代替手工的宾馆管理，将会大大提高工作效率。宾馆的主要活动首先可分为四个部分，即预订管理、入住管理、消费管理和退房结算管理。

 

2.3详细功能

预订管理主要包括登记客人的预订信息，查询预订信息，同时还需要注意预订信息不能出现冲突现象，例如两个客人都预订了同一天的同一个房间，这是不允许的；此外，在快到预订时确定的客人预抵时间时，接待人员要打电话证明客人是否能按时入住，如果不能，就会把预订单作废，或者称为失效；

除了按流程划分的这四个部分之外，还有两个部分：客房管理和用户管理。；这两部分信息需要在客人入住以前提前设定好。

2.4 E-R图

 

 

 

 

2.5数据表的设计

 

表1 用户表

表名		hotel_t_User (用户表)
列名	描述	数据类型	空/非空	约束条件
userid	用户编号	NUMBER(38)	非空	主键(自增)
username	用户名称	VARCHAR2(20)	非空
userpassword	密码	VARCHAR2(20)	非空
truename	真实姓名	VARCHAR2(20)	非空

 

表2 角色表

表名		hotel_t_Role (角色表)
列名	描述	数据类型	空/非空	约束条件
roleid	用户编号	NUMBER	非空	主键
rolename	用户名称	VARCHAR2(20)	非空

 

 

表3 权限表

表名		hotel_t_Right (权限表)
列名	描述	数据类型	空/非空	约束条件
right	用户编号	NUMBER	非空	主键
rightname	用户名称	VARCHAR2(20)	非空

 

 

表4 角色权限表

表名		hotel _t_Roleright (角色权限表)
列名	描述	数据类型	空/非空	约束条件
rrid	编号	NUMBER	非空	主键
roleid	用户编号	NUMBER	非空	外键
rightid	权限编号	NUMBER	非空	外键

 

 

表5 用户角色表

表名		hotel_t_Userrole (用户角色表)
列名	描述	数据类型	空/非空	约束条件
urid	编号	NUMBER	非空	主键
roleid	用户编号	NUMBER	非空	外键
rightid	权限编号	NUMBER	非空	外键

 

 

 

 

表6 客房类型表

表名		hotel_t_Roomtype（客房类型表）
列名	描述	数据类型	空/非空	约束条件
typeid	类型编号	NUMBER	非空	主键
typename	类型名称	VARCHAR2(20)	非空
mardedprice	标价	NUMBER(12,2)	空
scale	折扣比例	NUMBER(5,4)	空
lowestprice	最低折扣价	NUMBER(12,2)	空

 

表7 客房信息表

表名		hotel_t_Room（客房信息表）
列名	描述	数据类型	空/非空	约束条件
roomid	序号	VARCHAR2(10)	非空	主键
typeid	类型编号	NUMBER	非空	外键
layer	楼层	VARCHAR2(20)	空
bendnumber	床位数	NUMBER	空
state	状态	NUMBER	非空

 

表8 预订信息表

表名		hotel_t_Predestine（预订信息表）
列名	描述	数据类型	空/非空	约束条件
predid	预订单号	CHAR(16)	非空	主键
roomid	房号	VARCHAR2(10)	非空
whenpred	预定时间	DATE	非空
whopred	预订人	VARCHAR2(10)	非空
phone	联系方式	VARCHAR2(10)	非空
arrivetime	预抵时间	DATE	非空
leavetime	预离时间	DATE	非空
trueprice	房价	NUMBER(12,2)
state	状态	NUMBER	非空

 

表9 历史预订信息表

表名		hotel_t_ Predestinehistory（历史预订信息表）
列名	描述	数据类型	空/非空	约束条件
predid	预订单号	CHAR(16)	非空	主键
roomid	房号	VARCHAR2(10)	非空
whenpred	预定时间	DATE	非空
whopred	预订人	VARCHAR2(10)	非空
phone	联系方式	VARCHAR2(10)	非空
arrivetime	预抵时间	DATE	非空
leavetime	预离时间	DATE	非空
trueprice	房价	NUMBER(12,2)
state	状态	NUMBER	非空

表10 入住信息表

表名		hotel_t_Lodge（入住信息表）
列名	描述	数据类型	空/非空	约束条件
lodgeid	入住单号	CHAR(16)	非空	主键
roomid	房号	VARCHAR2(10)	非空	外键
guestname	客人姓名	VARCHAR2(20)	非空
guestsex	性别	CHAR(1)	非空
cardtype	证件类别	VARCHAR2(20)	空
cardnumber	证件号码	VARCHAR2(30)	空
birthday	出生日期	DATE	空
guestaddress	地址	VARCHAR2(50)	空
phone	联系方式	VARCHAR2(20)	空
arrivetime	入住时间	DATE	非空
leavetime	预离或退房时间	DATE	非空
trueprice	房价	NUMBER(12,2)	非空
payinadvance	押金	NUMBER(12,2)	空
predid	预订单号	CHAR(16)	空
serverman	接待人员	VARCHAR2(20)	空

 

 

 

表11 历史入住信息表

表名		hotel_t_ Lodgehistory（历史入住信息表）
列名	描述	数据类型	空/非空	约束条件
lodgeid	入住单号	CHAR(16)	非空	主键
roomid	房号	VARCHAR2(10)	非空	外键
guestname	客人姓名	VARCHAR2(20)	非空
guestsex	性别	CHAR(1)	非空
cardtype	证件类别	VARCHAR2(20)	空
cardnumber	证件号码	VARCHAR2(30)	空
birthday	出生日期	DATE	空
guestaddress	地址	VARCHAR2(50)	空
phone	联系方式	VARCHAR2(20)	空
arrivetime	入住时间	DATE	非空
leavetime	预离或退房时间	DATE	非空
trueprice	房价	NUMBER(12,2)	非空
payinadvance	押金	NUMBER(12,2)	空
predid	预订单号	CHAR(16)	空
serverman	接待人员	VARCHAR2(20)	空

 

 

 

 

表12 消费信息表

表名		hotel_t_Consume（消费信息表）
列名	描述	数据类型	空/非空	约束条件
consid	消费编号	NUMBER	非空	主键
consname	消费项目	VARCHAR2(20)	非空
consmoney	消费金额	NUMBER(12,2)	非空
constime	消费时间	DATE
lodgeid	入住单号	CHAR(16)	非空	外键

 

2.6数据库约束的设计

 

功能：roleid参考hotel_t_Role表的roleid字段，外键约束

实现：roleidnumber not null references hotel_t_Role(roleid)

 

功能：rightid参考hotel_t_Right表的rightid字段，外键约束

实现：rightidnumber not null references hotel_t_Right(rightid)

 

功能：roleid参考hotel_t_Role表的roleid字段，外键约束

实现：roleidnumber not null references hotel_t_Role(roleid),

 

功能：rightid参考hotel_t_Right表的rightid字段，外键约束

实现：rightidnumber not null references hotel_t_Right(rightid)

 

功能：typeid参考hotel_t_Roomtype表的typeid字段，外键约束

实现：typeidNUMBER references hotel_t_Roomtype(typeid),

 

功能：检查约束0表示空闲，1表示入住，2表示预留，默认为0

实现：stateNUMBER default 0 check(state in (0,1,2)) not null 

 

功能：取值范围为0、1和2，0表示有效，1表示入住，2表示失效，默认为0

实现：stateNUMBER default 0 check(state in (0,1,2)) not null

 

功能：roomid参考hotel_t_Room表的Roomid字段，外键约束

实现：roomidVARCHAR2(10) not null references hotel_t_Room(Roomid)

 

功能：检查约束，性别为非空，“男”或“女”

实现：guestsexCHAR(1) default 0 check(guestsex in (0,1)) not null,

 

功能：lodgeid参考hotel_t_Lodge表的lodgeid字段，外键约束

实现：lodgeidCHAR(16) not null references hotel_t_Lodge(lodgeid)

2.7数据库序列的设计

 

功能：创建seq_hotel_t_User序列，在插入时实现添加序号的功能

实现：createsequence seq_hotel_t_User;

 

功能：创建seq_hotel_t_Role序列，在插入时实现添加序号的功能

实现：createsequence seq_hotel_t_Role;

 

功能：创建seq_hotel_t_Right序列，在插入时实现添加序号的功能

实现：createsequence seq_hotel_t_Right;

 

功能：创建seq_hotel_t_Roleright序列，在插入时实现添加序号的功能

实现：createsequence seq_hotel_t_Roleright;

 

功能：创建seq_hotel_t_Userrole序列，在插入时实现添加序号的功能

实现：createsequence seq_hotel_t_Userrole;

 

功能：创建seq_hotel_t_Userrole序列，在插入时实现添加序号的功能

实现：createsequence seq_hotel_t_Roomtype;

 

功能：创建seq_hotel_t_Consume序列，在插入时实现添加序号的功能

实现：createsequence seq_hotel_t_Consume;

 

2.8数据库索引的设计

 

功能：在入住历史表上，基于客人姓名和入住时间创建简单非聚集组合索引

实现：

create index guestname_arrivetime_index

on hotel_t_lodge(guestname,arrivetime);

2.9数据库视图的设计

 

功能：基于客房表和客房类型表创建视图

实现：

CREATE VIEW v_room AS

SELECThotel_t_Roomtype.typeid,typename,mardedprice,scale,lowestprice,

      roomid,layer,bednumber,state

FROM  hotel_t_Roomtype,hotel_t_Room

WHERE hotel_t_Roomtype.typeid = hotel_t_Room.typeid;

 

select * from v_room;

功能：查询预订信息的视图，以提高查询结果的可读性

实现：

create or replace view v_Predestine as

select * from hotel_t_Predestine where state in(0);

 

功能：查询预订历史信息的视图

实现：

create or replace view v_Predestine as

select * from hotel_t_Predestine where state in(1,2);

 

功能：查看在店客人的视图

实现：

create view lodgenow_view as

select *

from hotel_t_lodge

where to_char(sysdate,'yyyy-mm-dd') <to_char(leavetime,'yyyy-mm-dd');

 

功能：查看客人入住历史的视图

实现：

create view lodgeever_view as

select *

from hotel_t_lodge

where to_char(sysdate,'yyyy-mm-dd') >=to_char(leavetime,'yyyy-mm-dd');

2.11数据库触发器的设计

 

功能：在插入数据前，先给每一行附上序号，以免发生null错误。

实现：

create or replace trigger tir_hotel_t_User

before  insert orupdate  on hotel_t_User for each row

begin

  selectseq_hotel_t_User.nextval

         into:new.userid from dual;

end;

 

功能：在插入数据前，先给每一行附上序号，以免发生null错误。

实现：

create or replace trigger tir_hotel_Role --创建触发器

before  insert orupdate  on hotel_t_Role for each row

begin

  selectseq_hotel_t_Role.nextval

         into:new.roleid  from dual;

end;

 

功能：在插入数据前，先给每一行附上序号，以免发生null错误。

实现：

create or replace trigger tir_hotel_Right --创建触发器

before  insert orupdate  on hotel_t_Right for each row

begin

  selectseq_hotel_t_Right.nextval

         into:new.Rightid  from dual;

end;

 

功能：在插入数据前，先给每一行附上序号，以免发生null错误。

实现：

create or replace trigger tir_hotel_Roleright --创建触发器

before  insert orupdate  on hotel_t_Roleright for each row

begin

  selectseq_hotel_t_Roleright.nextval

         into:new.rrid  from dual;

end;

 

功能：在插入数据前，先给每一行附上序号，以免发生null错误。

实现：

create or replace trigger tir_hotel_Userrole --创建触发器

before  insert or update  on hotel_t_Userrole for each row

begin

  selectseq_hotel_t_Userrole.nextval

         into:new.urid  from dual;

end;

 

功能：在插入数据前，先给每一行附上序号，以免发生null错误。

实现：

create or replace trigger tir_hotel_Roomtype --创建触发器

before  insert orupdate  on hotel_t_Roomtype for each row

begin

  selectseq_hotel_t_Roomtype.nextval

         into:new.typeid  from dual;

end;

 

功能：在插入数据前，先给每一行附上序号，以免发生null错误。

实现：

create or replace trigger tir_hotel_Consume --创建触发器

before  insert orupdate  on hotel_t_Consume for each row

begin

  selectseq_hotel_t_Consume.nextval

         into:new.consid  from dual;

end;

 

功能：实现插入、修改预订信息时保证预订房价不得低于房价最低价

实现：

create or replace trigger tri_predestine_3_2

before insert or update of Trueprice on hotel_t_Predestine

for each row

declare

    mintrueprice   hotel_t_Predestine.Trueprice%type;

begin

  selectmin(trueprice) into mintrueprice from hotel_t_Predestine;

  if :new.trueprice< mintrueprice then

   raise_application_error(-20001,'预订房价低于房价最低价');

  end if;

end;

 

功能：实现预定时或修改预定时查找要预定的房间在预定的时间段有没有与其他的预定有冲突

实现：

create or replace trigger predestine_room_tri

before insert or update on hotel_t_predestine for each row

declare

 p_roomcount number;

begin

 select count(*) intop_roomcount

  fromHOTEL_T_PREDESTINE

  where roomid =:new.roomid and (arrivetime<:new.arrivetime or leavetime>:new.leavetime);

 if p_roomcount>0then

   raise_application_error(-20002,'该房间已经预定');

  end if;

end;

 

功能：创建插入入住信息的触发器

实现：

create or replace trigger lodge_insert_tri

before insert or update on HOTEL_T_LODGE for each row

declare

  r_stateHOTEL_T_ROOM.State%type;

  l_roomidHOTEL_T_LODGE.roomid%type;

begin

 dbms_output.put_line(:new.roomid);

  l_roomid :=:new.roomid;

  select state intor_state

  from HOTEL_T_ROOM

  where roomid =l_roomid;

  if r_state=0 then

  raise_application_error(-20003,'可以插入入住信息');

  else

   raise_application_error(-20004,'不可以插入入住信息');

  end if;

end;

 

功能：创建办理续住手续的触发器

实现：

create or replace trigger continue_lodge_tri

before update on hotel_t_lodge for each row

declare

  l_leavetimehotel_t_lodge.leavetime%type;

begin

  l_leavetime :=:new.leavetime;

  if l_leavetime <=:old.leavetime then

   raise_application_error(-20005,'办理续住手续错误');

    rollback;

  else

   raise_application_error(-20006,'办理续住手续正确');

    commit;

  end if;

end;

 

2.12数据库存储过程的设计

 

功能：编写一个存储过程，用来用户登录时验证用户，存储过程验证用户登陆信息

实现：

declare

  cc number;

begin

  select count(*) intocc from HOTEL_T_USER where userid =&userid and username='&userpassword';

  if cc>0 then

    dbms_output.put_line('登陆成功');

  else

   dbms_output.put_line('登陆失败');

  end if;

exception

  when no_data_foundthen

   dbms_output.put_line('登陆失败');

end;

 

功能：创建一个存储过程，当快要到客人预订的预抵时间时（默认提前两个

小时），将房间状态设为预留，可以提醒接待人员与客人联系确认是否

入住。该存储过程的调用应该是每隔一段时间就调用一次，人为来操作

肯定是不现实的，在数据库中可以通过作业来实现

实现：

create or replace procedure remind_proc

is

  p_roomid  hotel_t_predestine.roomid%type;

  p_arrivetimehotel_t_predestine.arrivetime%type;

begin

  selectroomid,arrivetime-2/24 into p_roomid,p_arrivetime

  fromhotel_t_predestine;

  ifp_arrivetime=sysdate then

    updatehotel_t_room set state = 2 where roomid = p_roomid;

  end if;

end;

declare

       job_num number;

begin

         dbms_job.submit(job_num,'remind_proc;',sysdate,'Sysdate＋1/1440');

          commit;

end;

 

功能：创建使预订单失效的存储过程

实现：

create or replace procedure predestine_state_proc(

p_predid varchar2

)

is

begin

  updatehotel_t_predestine set state = 2 where

  predid = p_predid;

end;

 

功能：创建结算存储过程(该存储过程首先根据客人的退房时间计算住宿的天数，然后用入住单上的房价乘以入住天数后得到住宿的房费；再分别计算出客人的其他消费合计，两项相加得到应收帐款，用应收帐款减去客人入住时交的押金，得到客人需要补交的金额，最后把这些信息输出。)

实现：

create or replace procedure accounts_proc(

a_roomid varchar2,a_guestname varchar2

)

is

  lodgedaypricenumber(12,2);

  c_consmoney number(12,2);

  shouldmoneynumber(12,2);

  paymoneynumber(12,2);

  l_payinadvancenumber(12,2);

 

begin

 

  select(leavetime-arrivetime)*trueprice into lodgedayprice

  from hotel_t_lodge

  where roomid =to_number(a_roomid) and guestname = a_guestname;

  select consmoneyinto c_consmoney

  from hotel_t_consumehtc,hotel_t_lodge htl

  where htc.lodgeid =htl.lodgeid and

  roomid = a_roomidand guestname = a_guestname;

  shouldmoney :=c_consmoney + lodgedayprice;

  select payinadvanceinto l_payinadvance

  from hotel_t_lodge;

  paymoney:=shouldmoney - l_payinadvance;

 dbms_output.put_line('补交的金额为：'||paymoney);

end;

 

功能：创建退房存储过程(先是把当前时间修改为客人的退房时间，然后是将客

--人退掉的房间状态改为“空闲”，最后将该客人的入住信息转存到入住历史表，

--再将该信息从入住表中删除)

实现：

create or replace procedure leavetime_proc(

  a_roomidvarchar2,a_guestname varchar2

)

is

  r_lodgeidhotel_t_lodge.lodgeid%type;

  r_roomidhotel_t_lodge.roomid%type;

  r_guestnamehotel_t_lodge.guestname%type;

  r_guestsexhotel_t_lodge.guestsex%type;

  r_cardtypehotel_t_lodge.cardtype%type;

  r_cardnumberhotel_t_lodge.cardnumber%type;

  r_birthdayhotel_t_lodge.birthday%type;

  r_guestaddresshotel_t_lodge.guestaddress%type;

  r_phonehotel_t_lodge.phone%type;

  r_arrivetimehotel_t_lodge.arrivetime%type;

  r_leavetimehotel_t_lodge.leavetime%type;

  r_truepricehotel_t_lodge.trueprice%type;

  r_payinadvancehotel_t_lodge.payinadvance%type;

  r_predidhotel_t_lodge.predid%type;

  r_sercermanhotel_t_lodge.serverman%type;

begin

  update hotel_t_lodgeset leavetime = sysdate where roomid = to_number(a_roomid) and guestname =a_guestname;

  update HOTEL_T_ROOMset state = 0 where roomid = a_roomid;

  selectlodgeid,roomid,guestname,guestsex,cardtype,cardnumber,birthday,guestaddress,phone,

 arrivetime,leavetime,trueprice,payinadvance,predid,serverman intor_lodgeid,r_roomid,r_guestname,r_guestsex,

 r_cardtype,r_cardnumber,r_birthday,r_guestaddress,r_phone,r_arrivetime,r_leavetime,r_trueprice,r_payinadvance,

  r_predid,r_sercerman

  from hotel_t_lodge htl;

  insert intoHOTEL_T_LODGEHISTORYvalues(r_lodgeid,r_roomid,r_guestname,r_guestsex,r_cardtype,r_cardnumber,

 r_birthday,r_guestaddress,r_phone,r_arrivetime,r_leavetime,r_trueprice,r_payinadvance,r_predid,

  r_sercerman);

  delete fromhotel_t_Consume where lodgeid = r_lodgeid;

  delete fromhotel_t_lodge where lodgeid = r_lodgeid;

  commit;

end;

附录代码：

--1.用户管理--------------------------------------------------------------------------------------------------------

--（1）建立相关的数据表及其约束，由于数据量都不大不需要建立所有
-- hotel_t_User（用户表）
Create table hotel_t_User(
    userid number primary key not null,--自增
    username varchar2(20) not null,
    userpassword varchar2(20) not null,
    truename varchar2(20) not null
);
select * from hotel_t_user;

create sequence seq_hotel_t_User;--序列

create or replace trigger tir_hotel_t_User --触发器
before  insert or update  on hotel_t_User for each row
begin
  select seq_hotel_t_User.nextval
         into :new.userid from dual;
end;
---------------
insert into hotel_t_User(username,userpassword,truename)
       values('Sky','147258','王聪');
insert into hotel_t_User(username,userpassword,truename)
       values('eee','145236','王小聪');
select * from hotel_t_user;


--（2）  编写一个存储过程，用来用户登录时验证用户
------存储过程验证用户登陆信息
select count(*) from hotel_t_User where userid =&userid and username ='&userpassword';


declare
  cc number;
begin
  select count(1) into cc from hotel_t_User where userid =&userid and username ='&userpassword';
  if cc>0 then
    dbms_output.put_line('登陆成功');
  else
    dbms_output.put_line('登陆失败');
  end if;
exception
  when no_data_found then
    dbms_output.put_line('登陆失败');
end;


--2.  客户基本信息管理：-----------------------------------------------------------------------------------------------

--（1）  建立相关的数据表及其约束，由于数据量都不大不需要建立所有
--客房基本信息管理
create table hotel_t_Roomtype(
        typeid NUMBER primary key not null, --自增
        typename VARCHAR2(20)  not null,  
        mardedprice NUMBER(12,2),  
        scale NUMBER(5,4),
        lowestprice NUMBER(12,2)
)
create sequence seq_hotel_t_Roomtype;
create or replace trigger tir_hotel_Roomtype
before  insert or update  on hotel_t_Roomtype for each row
begin
  select seq_hotel_t_Roomtype.nextval
         into :new.typeid  from dual;
end;

--（2）  基于客房表和客房类型表创建视图
CREATE VIEW v_room AS
SELECT hotel_t_Roomtype.typeid,typename,mardedprice,scale,lowestprice,
       roomid,layer,bednumber,state
FROM   hotel_t_Roomtype,hotel_t_Room
WHERE  hotel_t_Roomtype.typeid = hotel_t_Room.typeid;


select * from v_room;



--3.  预定管理-----------------------------------------------------------------------------------------------------------

--（1） 创建表及其相关约束
create table hotel_t_Predestine(--（预订信息表）
predid CHAR(16) primary key not null,--  非空  主键
roomid VARCHAR2(10) not null,--  非空  
whenpred DATE not null,--  非空  
whopred VARCHAR2(20) not null,--  非空  
phone VARCHAR2(20) not null,--  非空  
arrivetime DATE not null,--  非空  
leavetime DATE not null,--  非空  
trueprice NUMBER(12,2) not null,    
state NUMBER default 0 check(state in (0,1,2)) not null--  取值范围为0、1和2，0表示有效，1表示入住，2表示失效，默认为0*/
);

--表名  hotel_t_Predestine（预订信息表）
--列名 描述  数据类型 （精度范围）  空/非空  约束条件
--predid  预订单号  CHAR(16)  非空  主键
--roomid  房号  VARCHAR2(10)  非空  
--whenpred  预订时间  DATE  非空  
--whopred  预订人  VARCHAR2(20)  非空  
--phone  联系方式  VARCHAR2(20)  非空  
--arrivetime  预抵时间  DATE  非空  
--leavetime  预离时间  DATE  非空  
--trueprice  房价  NUMBER(12,2)    
--state  状态  NUMBER  非空  取值范围为0、1和2，0表示有效，1表示入住，2表示失效，默认为0*/

insert into hotel_t_Predestine values (0000000000000000,11,sysdate,'wa1','18111111111',sysdate+10,sysdate+34,100,0);
insert into hotel_t_Predestine values (0000000000000001,12,sysdate,'wa2','15555555555',sysdate+10,sysdate+34,100,1);
insert into hotel_t_Predestine values (0000000000000002,13,sysdate,'wa2','16666666666',sysdate+10,sysdate+34,100,2);
select * from hotel_t_Predestine;


--（2）  创建触发器：实现插入、修改预订信息时保证预订房价不得低于房价最低价
create or replace trigger tri_predestine_3_2
before insert or update of Trueprice on hotel_t_Predestine
for each row
declare
    mintrueprice   hotel_t_Predestine.Trueprice%type;
begin
  select min(trueprice) into mintrueprice from hotel_t_Predestine;
  if :new.trueprice < mintrueprice then
    raise_application_error(-20001,'预订房价低于房价最低价');
  end if;
end;
--（3）  创建触发器：实现预定时或修改预定时查找要预定的房间在预定的时间段有没有与其他的预定有冲突
select * from hotel_t_Predestine where roomid = &roomid;
select * from hotel_t_Predestine where roomid = &roomid;
select * from hotel_t_Predestine where roomid = &roomid;


create or replace trigger predestine_room_tri
before insert or update on hotel_t_predestine for each row
declare
 p_roomcount number;
begin
 select count(*) into p_roomcount
  from HOTEL_T_PREDESTINE
  where roomid = :new.roomid and (arrivetime<:new.arrivetime or leavetime>:new.leavetime);
 if p_roomcount>0 then
    raise_application_error(-20002,'该房间已经预定');
  end if;
end;


      
--（4）  创建一个查询预订信息的视图，以提高查询结果的可读性
create or replace view v_Predestine as
select * from hotel_t_Predestine where state in(0);

--（5）  创建一个查询预订历史信息的视图
create or replace view v_Predestine as
select * from hotel_t_Predestine where state in(1,2);

--（6）  创建一个存储过程，当快要到客人预订的预抵时间时（默认提前两个小时），
--       将房间状态设为预留，可以提醒接待人员与客人联系确认是否入住。该存储
--       过程的调用应该是每隔一段时间就调用一次，人为来操作肯定是不现实的，
--       在数据库中可以通过作业来实现
create or replace procedure remind_proc
is
  p_roomid  hotel_t_predestine.roomid%type;
  p_arrivetime hotel_t_predestine.arrivetime%type;
begin
  select roomid,arrivetime-2/24 into p_roomid,p_arrivetime
  from hotel_t_predestine;
  if p_arrivetime=sysdate then
    update hotel_t_room set state = 2 where roomid = p_roomid;
  end if;
end;
declare
       job_num number;
begin
          dbms_job.submit(job_num,'remind_proc;',sysdate,'Sysdate＋1/1440');
          commit;
end;


--（7） 创建使预订单失效的存储过程
create or replace procedure predestine_state_proc(
p_predid varchar2
)
is
begin
  update hotel_t_predestine set state = 2 where
  predid = p_predid;
end;


-------------------
ALTER proc [dbo].[存储过程名]
 as
 begin
   declare 游标名字 cursor for select 列名 from 表名 where 条件--先申明游标指向查询出的结果，一列，或者多列都可以，条件自定
   declare 变量名  varchar(400)--存储取到的值
   open 游标名 --开启游标
   while @@FETCH_STATUS=0--取值
     begin
     fetch next FROM 游标名 into 变量名--这样就将游标指向下一行，得到的第一行值就传给变量了
     -------------------------------------------
     --需要执行的操作，例如修改某表中的字段
     update 表名
     set 列名=值
     where （修改表中的列）=变量名
     -------------------------------------------
 end
  close 游标名--关闭游标


  deallocate  游标名--释放游标
end
-------------------
create or replace procedure pro_Predestine
is
   v_leavetime hotel_t_Predestine.leavetime%type;
begin
   select leavetime into v_leavetime from hotel_t_Predestine where
empno=v_id;
   dbms_output.put_line(v_ename||'   '||v_sal);   
end;

--4.  入住管理-------------------------------------------------------------------------------------------------------------------
--（1）  创建表及其相关约束

--hotel_t_Lodge（入住信息表）
create table hotel_t_Lodge(--（入住信息表）
--列名  描述  数据类型（精度范围）  空/非空  约束条件
lodgeid CHAR(16) primary key not null,-- 非空  主键
roomid VARCHAR2(10) not null references hotel_t_Room(Roomid),--  非空  外键
guestname VARCHAR2(20) not null,--  非空  
guestsex CHAR(1) default 0 check(guestsex in (0,1)) not null,--  非空  “男”或“女”
cardtype VARCHAR2(20),--  空  
cardnumber VARCHAR2(30),--  空  
birthday DATE,--  空  
guestaddress VARCHAR2(50),--  空  
phone VARCHAR2(20),--  空  
arrivetime DATE not null,--  非空  
leavetime DATE,--  非空  
trueprice NUMBER(12,2),--  非空  
payinadvance NUMBER(12,2) default 0,--  空  默认为0
predid CHAR(16),--  空  
serverman VARCHAR2(20)--  空
);
select  * from   hotel_t_Lodge;


--（2）  创建插入入住信息的触发器
create or replace trigger lodge_insert_tri
before insert or update on HOTEL_T_LODGE for each row
declare
  r_state HOTEL_T_ROOM.State%type;
  l_roomid HOTEL_T_LODGE.roomid%type;
begin
  dbms_output.put_line(:new.roomid);
  l_roomid := :new.roomid;
  select state into r_state
  from HOTEL_T_ROOM
  where roomid = l_roomid;
  if r_state=0 then
   raise_application_error(-20003,'可以插入入住信息');
  else
    raise_application_error(-20004,'不可以插入入住信息');
  end if;
end;


--（3）  创建办理续住手续的触发器
create or replace trigger continue_lodge_tri
before update on hotel_t_lodge for each row
declare
  l_leavetime hotel_t_lodge.leavetime%type;
begin
  l_leavetime := :new.leavetime;
  if l_leavetime <= :old.leavetime then
    raise_application_error(-20005,'办理续住手续错误');
    rollback;
  else
    raise_application_error(-20006,'办理续住手续正确');
    commit;
  end if;
end;


--（4） 创建查看在店客人的视图
create view lodgenow_view as
select *
from hotel_t_lodge
where to_char(sysdate,'yyyy-mm-dd') < to_char(leavetime,'yyyy-mm-dd');


--（5）  创建查看客人入住历史的视图
create view lodgeever_view as
select *
from hotel_t_lodge
where to_char(sysdate,'yyyy-mm-dd') >= to_char(leavetime,'yyyy-mm-dd');


--（6）  在入住历史表上，基于客人姓名和入住时间创建简单非聚集组合索引
--入住历史表
create table hotel_t_lodgehistory(
    lodgeid char(16) primary key not null,
    roomid varchar2(10) not null,
    guestname varchar2(20) not null,
    guestsex char(2) check(guestsex in('男','女')) not null,
    cardtype varchar2(20),
    cardnumber varchar2(30),
    birthday date,
    guestaddress varchar2(50),
    phone varchar2(20),
    arrivetime date not null,
    leavetime date not null,
    trueprice number(12,2) not null,
    payinadvance number(12,2) default 0,
    predid char(16),
    serverman varchar2(20)
);
------------------------
create index guestname_arrivetime_index
on hotel_t_lodge(guestname,arrivetime);
----------------

--5.  消费管理-----------------------------------------------------------------------------------------------------------------------
--（1） 创建消费信息表及其约束

--hotel_t_Consume（消费信息表）
create table hotel_t_Consume(--（消费信息表）
--列名  描述  数据类型（精度范围）  空/非空  约束条件
consid NUMBER primary key not null,--  非空  主键（自增）
consname VARCHAR2(20) not null,--  非空  
consmoney NUMBER(12,2) not null,-- 非空  
constime DATE,
lodgeid CHAR(16) not null references hotel_t_Lodge(lodgeid)-- 非空  外键
);

select  * from  hotel_t_Consume;
create sequence seq_hotel_t_Consume;
create or replace trigger tir_hotel_Consume
before  insert or update  on hotel_t_Consume for each row
begin
  select seq_hotel_t_Consume.nextval
         into :new.consid  from dual;
end;


--（2） 创建查询客人消费信息的视图
create index guestname_arrivetime_index
on hotel_t_lodge(guestname,arrivetime);


--6.  退房结算管理----------------------------------------------------------------------------------------------------------------
--（1）  创建结算存储过程(该存储过程首先根据客人的退房时间计算住宿的天数，然后用入住
--        单上的房价乘以入住天数后得到住宿的房费；再分别计算出客人的其他消费合计，两
--        项相加得到应收帐款，用应收帐款减去客人入住时交的押金，得到客人需要补交的金
--        额，最后把这些信息输出。)
create or replace procedure accounts_proc(
a_roomid varchar2,a_guestname varchar2
)
is
  lodgedayprice number(12,2);
  c_consmoney number(12,2);
  shouldmoney number(12,2);
  paymoney number(12,2);
  l_payinadvance number(12,2);
 
begin
 
  select (leavetime-arrivetime)*trueprice into lodgedayprice
  from hotel_t_lodge
  where roomid = to_number(a_roomid) and guestname = a_guestname;
  select consmoney into c_consmoney
  from hotel_t_consume htc,hotel_t_lodge htl
  where htc.lodgeid = htl.lodgeid and
  roomid = a_roomid and guestname = a_guestname;
  shouldmoney := c_consmoney + lodgedayprice;
  select payinadvance into l_payinadvance
  from hotel_t_lodge;
  paymoney :=shouldmoney - l_payinadvance;
  dbms_output.put_line('补交的金额为：'||paymoney);
end;


--（2）  创建退房存储过程(先是把当前时间修改为客人的退房时间，然后是将客人退掉的房间
--       状态改为“空闲”，最后将该客人的入住信息转存到入住历史表，再将该信息从入住表中删除)
create or replace procedure leavetime_proc(
  a_roomid varchar2,a_guestname varchar2
)
is
  r_lodgeid hotel_t_lodge.lodgeid%type;
  r_roomid hotel_t_lodge.roomid%type;
  r_guestname hotel_t_lodge.guestname%type;
  r_guestsex hotel_t_lodge.guestsex%type;
  r_cardtype hotel_t_lodge.cardtype%type;
  r_cardnumber hotel_t_lodge.cardnumber%type;
  r_birthday hotel_t_lodge.birthday%type;
  r_guestaddress hotel_t_lodge.guestaddress%type;
  r_phone hotel_t_lodge.phone%type;
  r_arrivetime hotel_t_lodge.arrivetime%type;
  r_leavetime hotel_t_lodge.leavetime%type;
  r_trueprice hotel_t_lodge.trueprice%type;
  r_payinadvance hotel_t_lodge.payinadvance%type;
  r_predid hotel_t_lodge.predid%type;
  r_sercerman hotel_t_lodge.serverman%type;
begin
  update hotel_t_lodge set leavetime = sysdate where roomid = to_number(a_roomid) and guestname = a_guestname;
  update HOTEL_T_ROOM set state = 0 where roomid = a_roomid;
  select lodgeid,roomid,guestname,guestsex,cardtype,cardnumber,birthday,guestaddress,phone,
  arrivetime,leavetime,trueprice,payinadvance,predid,serverman into r_lodgeid,r_roomid,r_guestname,r_guestsex,
  r_cardtype,r_cardnumber,r_birthday,r_guestaddress,r_phone,r_arrivetime,r_leavetime,r_trueprice,r_payinadvance,
  r_predid,r_sercerman
  from hotel_t_lodge htl;
  insert into HOTEL_T_LODGEHISTORY values(r_lodgeid,r_roomid,r_guestname,r_guestsex,r_cardtype,r_cardnumber,
  r_birthday,r_guestaddress,r_phone,r_arrivetime,r_leavetime,r_trueprice,r_payinadvance,r_predid,
  r_sercerman);
  delete from hotel_t_Consume where lodgeid = r_lodgeid;
  delete from hotel_t_lodge where lodgeid = r_lodgeid;
  commit;
end;