第一部分 案例描述
案例目的
学习并巩固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) |
空 |
|
| 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,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
FROMhotel_t_Roomtype,hotel_t_Room
WHEREhotel_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_cardtype,
r_birthday,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,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,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,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,'wa1','18111111111',sysdate+10,sysdate+34,100,0); insert into hotel_t_Predestine values (0000000000000001,12,'wa2','15555555555',1); insert into hotel_t_Predestine values (0000000000000002,13,'16666666666',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,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,'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,-- 非空 “男”或“女” cardtype VARCHAR2(20),-- 空 cardnumber VARCHAR2(30),-- 空 birthday DATE,-- 空 guestaddress VARCHAR2(50),-- 空 phone VARCHAR2(20),-- 空 arrivetime DATE not null,-- 非空 leavetime DATE,-- 非空 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,payinadvance number(12,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,-- 非空 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,arrivetime,serverman into r_lodgeid,r_sercerman from hotel_t_lodge htl; insert into HOTEL_T_LODGEHISTORY values(r_lodgeid,r_sercerman); delete from hotel_t_Consume where lodgeid = r_lodgeid; delete from hotel_t_lodge where lodgeid = r_lodgeid; commit; end;
原文链接：https://www.f2er.com/oracle/208138.html