【安博培训笔记】Oracle宾馆管理系统(六)
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;