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