【安博培训笔记】Oracle宾馆管理系统-TI_综合项目20130917

------------------------------------------------------------------
--实现主键自增


create table t28(
       id number primary key,
       name varchar2(20)
)


insert into t28(name) values('zhangsan');


create sequence seq_name222; 


create or replace trigger tir_t28 
before insert or update on t28 for each row
begin
  select seq_name222.nextval 
         into :new.id from dual;
end;


create or replace trigger tir_t28 
before insert on t28 for each row
begin
  select seq_name222.nextval 
         into :new.id from dual;
end;


select * from t28;
update t28 set name = 'shihua' where id = 1;




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


除了按流程划分的这四个部分之外，还有两个部分：客房管理和用户管理。；这两部分信息需要在客人入住以前提前设定好。
*/
/*1.  用户权限管理
用户管理是管理系统的使用者，主要包括前台接待人员、前台收银员、餐厅服务员等，他们的权限按其身份不同而不同。
 
表名  hotel_t_User（用户表）
列名  描述  数据类型（精度范围）  空/非空  约束条件
userid  用户编号  Number  非空  主键（自增）
username  用户名称  VARCHAR2(20)  非空  
userpassword  密码  VARCHAR2(20)  非空  
truename  真实姓名  VARCHAR2(20)  非空 */ 


drop table 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;
---------------


---------------------------
drop sequence seq_hotel_t_User;
create sequence seq_hotel_t_User;


drop trigger tir_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('shihua','shihua','shihua');
---------------------
/*表名  hotel_t_Role（角色表）
列名  描述  数据类型（精度范围）  空/非空  约束条件
roleid  用户编号  NUMBER  非空  主键（自增）
rolename  用户名称  VARCHAR2(20)  非空*/


create table hotel_t_Role(
       roleid number primary key not null,--自增
       rolename varchar2(20) not null
);  


drop sequence seq_hotel_t_Role;
create sequence seq_hotel_t_Role;


drop trigger tir_hotel_t_Role;
create or replace trigger tir_hotel_Role 
before  insert or update  on hotel_t_Role for each row
begin
  select seq_hotel_t_Role.nextval 
         into :new.roleid  from dual;
end;


/*表名  hotel _t_Right（权限表）
列名  描述  数据类型（精度范围）  空/非空  约束条件
rightid  权限编号  NUMBER  非空  主键（自增）
rightname  权限名  VARCHAR2(50)  非空  */


create table hotel_t_Right(
       rightid number primary key not null,--自增
       rightname varchar2(20) not null
);


create sequence seq_hotel_t_Right;
create or replace trigger tir_hotel_Right 
before  insert or update  on hotel_t_Right for each row
begin
  select seq_hotel_t_Right.nextval 
         into :new.Rightid  from dual;
end;


/*表名  hotel_t_Roleright（角色权限表）
列名  描述  数据类型（精度范围）  空/非空  约束条件
rrid  编号  NUMBER  非空  主键（自增）
roleid  用户编号  NUMBER  非空  外键
rightid  权限编号  NUMBER  非空  外键*/


create table hotel_t_Roleright(
       rrid number primary key not null,--自增
       roleid number not null references hotel_t_Role(roleid),
       rightid number not null references hotel_t_Right(rightid)
);


create sequence seq_hotel_t_Roleright;
create or replace trigger tir_hotel_Roleright 
before  insert or update  on hotel_t_Roleright for each row
begin
  select seq_hotel_t_Roleright.nextval 
         into :new.rrid  from dual;
end;
/*表名  hotel_t_Userrole（用户角色表）
列名  描述  数据类型（精度范围）  空/非空  约束条件
urid  编号  NUMBER  非空  主键（自增）
roleid  用户编号  NUMBER  非空  外键
rightid  权限编号  NUMBER  非空  外键*/


create table hotel_t_Userrole(
       urid number primary key not null,--自增
       roleid number not null references hotel_t_Role(roleid),
       rightid number not null references hotel_t_Right(rightid)
);


create sequence seq_hotel_t_Userrole;
create or replace trigger tir_hotel_Userrole 
before  insert or update  on hotel_t_Userrole for each row
begin
  select seq_hotel_t_Userrole.nextval 
         into :new.urid  from dual;
end;
/*主要功能模块涉及的数据表的关系图：
 
2.  客房基本信息管理
客房管理主要是按客房的条件不同，对客房进行分类（例如分成标准间和豪华间），每一类制定一个标价，但实际上这个标价很少按照执行，为了迎合客人心理，各个宾馆一般都会对标价进行打折，即便这样，有的客人可能还不满意，或者是打折后出现零钱的现象，宾馆一般会给接待人员一个让价的权利，但是这个让价不能是无限度的，因此还需要对每类房间定一个最低价
表名  hotel_t_Roomtype（客房类型表）
列名  描述  数据类型（精度范围）  空/非空  约束条件
typeid  类型编号  NUMBER  非空  主键（自增）
typename  类型名称  VARCHAR2(20)  非空  
mardedprice  标价  NUMBER(12,2)  空  
scale  折扣比率  NUMBER(5,4)  空  
lowestprice  最低折扣价  NUMBER(12,2)  空 */ 


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;


/*表名  hotel_t_Room（客房信息表）
列名  描述  数据类型（精度范围）  空/非空  约束条件
roomid  房号  VARCHAR2(10)  非空  主键
typeid  类型编号  NUMBER  空  外键
layer  楼层  VARCHAR2(20)  空  
bednumber  床位数  NUMBER  空  
state  状态  NUMBER  非空  0表示空闲，1表示入住，2表示预留，默认为0*/


create table hotel_t_Room(--（客房信息表）
--列名  描述  数据类型（精度范围）  空/非空  约束条件
roomid VARCHAR2(10) primary key,--  非空  主键
typeid NUMBER references hotel_t_Roomtype(typeid),--  空  外键
layer VARCHAR2(20),--  空  
bednumber NUMBER,--  空  
state NUMBER default 0 check(state in (0,1,2)) not null  --0表示空闲，1表示入住，2表示预留，默认为0
);
/*3.  预订管理模块
预订管理主要包括登记客人的预订信息，查询预订信息，同时还需要注意预订信息不能出现冲突现象，例如两个客人都预订了同一天的同一个房间，这是不允许的；此外，在快到预订时确定的客人预抵时间时，接待人员要打电话证明客人是否能按时入住，如果不能，就会把预订单作废，或者称为失效；


表名  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*/


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*/
);


/*4.  入住管理模块
入住管理主要包括安排客人入住到空闲状态的房间、登记入住单信息，查询在店客人信息，以及客史查询（查询以前在本宾馆住宿过的客人信息），同时也需要避免和其他客人的预订信息冲突，例如入住的客人预计要等到10号退房，而其他客人已经预订了9号的这个房间，这种情况也是不允许的；
表名  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)  空  默认为0
predid  预订单号  CHAR(16)  空  
serverman  接待人员  VARCHAR2(20)  空*/  


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)--  空
);
/*5.  消费管理模块
消费管理主要包括登记客人的消费信息和查询消费信息，消费信息主要包括客人在宾馆的用餐费和电话费等；
表名  hotel_t_Consume（消费信息表）
列名  描述  数据类型（精度范围）  空/非空  约束条件
consid  消费编号  NUMBER  非空  主键（自增）
consname  消费项目  VARCHAR2(20)  非空  
consmoney  消费金额  NUMBER(12,2)  非空  
constime  消费时间  DATE    
lodgeid  入住单号  CHAR(16)  非空  外键*/


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)-- 非空  外键
);


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;
6.  退房结算管理
退房结算管理主要指客人在退房时，收银员计算客人在宾馆的所有费用，包括住宿费和消费管理中的餐费和电话费等，然后将这些费用的和与客人入住时交纳的押金进行对比，最后多退少补，完成退房结算手续。退房手续办完后，房间就变成了空闲房，其他客人可以入住了。






上机推荐步骤：
1.  用户管理：
1)  建立相关的数据表及其约束，由于数据量都不大不需要建立所有
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
);


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('shihua','shihua','shihua');
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;


--客房信息表
create table hotel_t_Room(
roomid VARCHAR2(10) primary key not null,--  非空  主键
typeid NUMBER references hotel_t_Roomtype(typeid),--  空  外键
layer VARCHAR2(20),--  空  
bednumber NUMBER,--  空  
state NUMBER default 0 check(state in (0,1,2)) not null  --0表示空闲，1表示入住，2表示预留，默认为0
);
客房基本信息管理
客房管理主要是按客房的条件不同，对客房进行分类（例如分成标准间和豪华间），每一类制定一个标价，但实际上这个标价很少按照执行，为了迎合客人心理，各个宾馆一般都会对标价进行打折，即便这样，有的客人可能还不满意，或者是打折后出现零钱的现象，宾馆一般会给接待人员一个让价的权利，但是这个让价不能是无限度的，因此还需要对每类房间定一个最低价
表名 hotel_t_Roomtype（客房类型表）
列名 描述 数据类型（精度范围） 空/非空 约束条件
typeid 类型编号 NUMBER 非空 主键（自增）
typename 类型名称 VARCHAR2(20) 非空
mardedprice 标价 NUMBER(12,2) 空
scale 折扣比率 NUMBER(5,4) 空
lowestprice 最低折扣价 NUMBER(12,2) 空




表名 hotel_t_Room（客房信息表）
列名 描述 数据类型（精度范围） 空/非空 约束条件
roomid 房号 VARCHAR2(10) 非空 主键
typeid 类型编号 NUMBER 空 外键
layer 楼层 VARCHAR2(20) 空
bednumber 床位数 NUMBER 空
state 状态 NUMBER 非空 0表示空闲，1表示入住，2表示预留，默认为0
2)  基于客房表和客房类型表创建视图
grant create any view to kaifa;


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,'shihua','18615396007',sysdate+10,sysdate+34,100,0);
insert into hotel_t_Predestine values (0000000000000001,12,sysdate,'shihua','18615396007',sysdate+10,sysdate+34,100,1);
insert into hotel_t_Predestine values (0000000000000002,13,sysdate,'shihua','18615396007',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)  创建表及其相关约束
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)  创建消费信息表及其约束
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;


第三部分  考核评价点
序号  功能列表  功能描述  分数  说明
1  数据表的建立和数据的准备  正确建立相关数据表，插入正确的数据  20  必做
2  存储过程  完成计算总分的存储过程  30  必做
3  触发器  完成插入成绩表，并算出总分的触发器  40  必做
4  符合规范  命名、书写格式规范  10  必做
5        必做