
--永久表空间 HomeStay_data
CREATE TABLESPACE HomeStay1_data DATAFILE 'D:\Data\local\sql\HomeStay1\HomeStay1_data.dbf' 
SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED ONLINE;
--drop tablespace HomeStay1_data;
--alter tablespace HomeStay1_data datafile 'D:\Data\local\sql\HomeStay1\HomeStay1_data.dbf' online;
 
--临时表空间 HomeStay_temp
CREATE TEMPORARY TABLESPACE HomeStay1_temp TEMPFILE 
'D:\Data\local\sql\HomeStay1\HomeStay1_temp.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 500M;
 
--索引表空间 HomeStay_idx
CREATE TABLESPACE HomeStay1_idx DATAFILE 'D:\Data\local\sql\HomeStay1\HomeStay1_idx.dbf' SIZE 500M AUTOEXTEND OFF;
 
--用户创建
--CREATE USER customer IDENTIFIED BY customer123 TEMPORARY TABLESPACE HomeStay_temp1;
--GRANT CONNECT TO customer; 
 
-- 管理员user创建
CREATE USER manager1 IDENTIFIED BY 123 DEFAULT TABLESPACE HomeStay1_data  QUOTA UNLIMITED ON  HomeStay1_data QUOTA UNLIMITED ON HomeStay1_idx TEMPORARY TABLESPACE HomeStay1_temp;
GRANT dba TO manager1; 
 
--create user proscenium identified by proscenium123 TEMPORARY TABLESPACE HomeStay_temp;
--GRANT CONNECT TO proscenium; 
 
 
--创建表
 /*==============================================================*/
/* DBMS name:      ORACLE Version 11g                           */
/* Created on:     2022/9/6 10:55:37                            */
/*==============================================================*/
 
 
 
 
/*==============================================================*/
/* Table: activity                                              */
/*==============================================================*/
create table activity 
(
   activity_code        VARCHAR2(16)         not null,
   activity_name        VARCHAR2(32),
   activity_type        VARCHAR2(32),
   activity_start_time  DATE,
   activity_end_time    DATE,
   activity_position    VARCHAR2(64),
   activity_content     VARCHAR2(240),
   activity_price       FLOAT(16),
   constraint PK_ACTIVITY primary key (activity_code)
);
 
comment on table activity is
'民宿中各种活动的相关信息。';
 
/*==============================================================*/
/* Table: activity_registration                                 */
/*==============================================================*/
create table activity_registration 
(
   customer_code        VARCHAR2(16)         not null,
   activity_code        VARCHAR2(16)         not null,
   activity_recode      VARCHAR2(16)         not null,
   constraint PK_ACTIVITY_REGISTRATION primary key (customer_code, activity_code)
);
 
comment on table activity_registration is
'住户报名参加活动。';
 
/*==============================================================*/
/* Index: activity_registration_FK                              */
/*==============================================================*/
create index activity_registration_FK on activity_registration (
   customer_code ASC
);
 
/*==============================================================*/
/* Index: activity_registration2_FK                             */
/*==============================================================*/
create index activity_registration2_FK on activity_registration (
   activity_code ASC
);
 
/*==============================================================*/
/* Table: book                                                  */
alter table book drop column order_code;
alter table book add order_code varchar2(16) not null after room_type_code;
select * from book;
/*==============================================================*/
create table book 
(
   reservation_code     VARCHAR2(16)         not null,
   customer_code        VARCHAR2(16)         not null,
   room_type_code       VARCHAR2(16)         not null,
   order_code           VARCHAR2(16)         not null,
   lease_way            VARCHAR2(1),
   reservation_check_in_time DATE,
   reservation_check_out_time DATE,
   constraint PK_BOOK primary key (reservation_code)
);
 
comment on table book is
'客户进行民宿预定。';
 
/*==============================================================*/
/* Index: 住客预定_FK                                               */
/*==============================================================*/
create index 住客预定_FK on book (
   customer_code ASC
);
 
/*==============================================================*/
/* Index: 房间预定_FK                                               */
/*==============================================================*/
create index 房间预定_FK on book (
   room_type_code ASC
);
 
/*==============================================================*/
/* Table: check_in                                              */
/*==============================================================*/
create table check_in 
(
   check_in_code        VARCHAR2(16)         not null,
   room_code            VARCHAR2(16)         not null,
   order_code           VARCHAR2(16)         not null,
   cost_code            VARCHAR2(16),
   check_in_person_name VARCHAR2(32),
   check_in_person_id_card varchar2(18),
   check_in_person_tel  NUMBER(15),
   check_in_time        DATE,
   check_out_tim        DATE,
   check_in_lease_way   VARCHAR2(1),
   constraint PK_CHECK_IN primary key (check_in_code)
);
 
comment on table check_in is
'住户租房基本信息。';
 
/*==============================================================*/
/* Index: 订单入住_FK                                               */
/*==============================================================*/
create index 订单入住_FK on check_in (
   order_code ASC
);
 
/*==============================================================*/
/* Index: 入住费用_FK                                               */
/*==============================================================*/
create index 入住费用_FK on check_in (
   cost_code ASC
);
 
/*==============================================================*/
/* Index: 入住房间_FK                                               */
/*==============================================================*/
create index 入住房间_FK on check_in (
   room_code ASC
);
 
/*==============================================================*/
/* Table: cost                                                  */
/*==============================================================*/
create table cost 
(
   cost_code            VARCHAR2(16)         not null,
   check_in_code        VARCHAR2(16),
   dinning_cost         FLOAT(16),
   room_cost            FLOAT(16),
   total_cost           FLOAT(16),
   constraint PK_COST primary key (cost_code)
);
 
comment on table cost is
'客户入住对应订单相关费用。';
 
/*==============================================================*/
/* Index: 入住费用2_FK                                              */
/*==============================================================*/
create index 入住费用2_FK on cost (
   check_in_code ASC
);
 
/*==============================================================*/
/* Table: customer                                              */
/*==============================================================*/
create table customer 
(
   customer_code        VARCHAR2(16)         not null,
   customer_name        VARCHAR2(32),
   gender               VARCHAR2(1),
   id                   varchar2(18),
   tel                  VARCHAR2(15),
   constraint PK_CUSTOMER primary key (customer_code)
);
 
comment on table customer is
'进入民宿的租户。';
 
/*==============================================================*/
/* Table: customer_comment                                      */
/*==============================================================*/
create table customer_comment 
(
   comment_code         VARCHAR2(16)         not null,
   customer_code        VARCHAR2(16)         not null,
   comment_score        NUMBER(1),
   comment_content      VARCHAR2(240),
   constraint PK_CUSTOMER_COMMENT primary key (comment_code)
);
 
comment on table customer_comment is
'住户对与民宿的相关评价，可以给出自己的评分与评价内容。';
 
/*==============================================================*/
/* Index: 客户评价_FK                                               */
/*==============================================================*/
create index 客户评价_FK on customer_comment (
   customer_code ASC
);
 
/*==============================================================*/
/* Table: customer_order                                        */
/*==============================================================*/
create table customer_order 
(
   order_code           VARCHAR2(16)         not null,
   customer_code        VARCHAR2(16)         not null,
   reservation_code     VARCHAR2(16)         not null,
   order_time           DATE,
   constraint PK_CUSTOMER_ORDER primary key (order_code)
);
 
comment on table customer_order is
'客户入住相关订单。';
 
/*==============================================================*/
/* Index: 订单预定_FK                                               */
/*==============================================================*/
create index 订单预定_FK on customer_order (
   reservation_code ASC
);
 
/*==============================================================*/
/* Index: 客户订单_FK                                               */
/*==============================================================*/
create index 客户订单_FK on customer_order (
   customer_code ASC
);
 
/*==============================================================*/
/* Table: dinning                                               */
/*==============================================================*/
create table dinning 
(
   dinning_code         VARCHAR2(16)         not null,
   check_in_code        VARCHAR2(16),
   food_code            VARCHAR2(16)         not null,
   dinning_time         DATE,
   constraint PK_DINNING primary key (dinning_code)
);
 
comment on table dinning is
'入住的客户在民宿中进行用餐的信息。';
 
/*==============================================================*/
/* Index: 用餐_FK                                                 */
/*==============================================================*/
create index 用餐_FK on dinning (
   check_in_code ASC
);
 
/*==============================================================*/
/* Index: 用餐合计_FK                                               */
/*==============================================================*/
create index 用餐合计_FK on dinning (
   food_code ASC
);
 
/*==============================================================*/
/* Table: dishes                                                */
/*==============================================================*/
create table dishes 
(
   food_code            VARCHAR2(16)         not null,
   food_name            VARCHAR2(32),
   food_price           FLOAT(32),
   food_content         VARCHAR2(240),
   constraint PK_DISHES primary key (food_code)
);
 
comment on table dishes is
'入住的客户在民宿中可以消费的具体餐品。';
 
/*==============================================================*/
/* Table: leave_message                                         */
/*==============================================================*/
create table leave_message 
(
   message_code         VARCHAR2(16)         not null,
   customer_code        VARCHAR2(16)         not null,
   message_tinfo        VARCHAR2(240),
   message_time         DATE,
   constraint PK_LEAVE_MESSAGE primary key (message_code)
);
 
comment on table leave_message is
'客户可以对民宿进行留言。';
 
/*==============================================================*/
/* Index: 客户留言_FK                                               */
/*==============================================================*/
create index 客户留言_FK on leave_message (
   customer_code ASC
);
 
/*==============================================================*/
/* Table: room                                                  */
/*==============================================================*/
create table room 
(
   room_code            VARCHAR2(16)         not null,
   room_type_code       VARCHAR2(16)         not null,
   room_number          VARCHAR2(32),
   check_out_confirm    VARCHAR2(1),
   room_name            VARCHAR2(10),
   constraint PK_ROOM primary key (room_code)
);
 
comment on table room is
'住户已经入住的房间信息。';
 
/*==============================================================*/
/* Index: 房间属别_FK                                               */
/*==============================================================*/
create index 房间属别_FK on room (
   room_type_code ASC
);
 
/*==============================================================*/
/* Table: room_type                                             */
/*==============================================================*/
create table room_type 
(
   room_type_code       VARCHAR2(16)         not null,
   room_type_name       VARCHAR2(32),
   room_day_price       FLOAT(32),
   room_short_price     FLOAT(32),
   room_long_price      FLOAT(32),
   room_type_info       VARCHAR2(240),
   room_type_sums       NUMBER(5),
   constraint PK_ROOM_TYPE primary key (room_type_code)
);
 
comment on table room_type is
'民宿的各种房间信息。';
 
alter table activity_registration
   add constraint FK_ACTIVITY_ACTIVITY__CUSTOMER foreign key (customer_code)
      references customer (customer_code);
 
alter table activity_registration
   add constraint FK_ACTIVITY_ACTIVITY__ACTIVITY foreign key (activity_code)
      references activity (activity_code);
 
alter table book
   add constraint FK_BOOK_住客预定_CUSTOMER foreign key (customer_code)
      references customer (customer_code);
 
alter table book
   add constraint FK_BOOK_房间预定_ROOM_TYP foreign key (room_type_code)
      references room_type (room_type_code);
 
alter table check_in
   add constraint FK_CHECK_IN_入住房间_ROOM foreign key (room_code)
      references room (room_code);
 
alter table check_in
   add constraint FK_CHECK_IN_入住费用_COST foreign key (cost_code)
      references cost (cost_code);
 
alter table check_in
   add constraint FK_CHECK_IN_订单入住_CUSTOMER foreign key (order_code)
      references customer_order (order_code);
 
alter table cost
   add constraint FK_COST_入住费用2_CHECK_IN foreign key (check_in_code)
      references check_in (check_in_code);
 
alter table customer_comment
   add constraint FK_CUSTOMER_客户评价_CUSTOMER foreign key (customer_code)
      references customer (customer_code);
 
alter table customer_order
   add constraint FK_CUSTOMER_客户订单_CUSTOMER foreign key (customer_code)
      references customer (customer_code);
 
alter table customer_order
   add constraint FK_CUSTOMER_订单预定_BOOK foreign key (reservation_code)
      references book (reservation_code);
 
alter table dinning
   add constraint FK_DINNING_用餐_CHECK_IN foreign key (check_in_code)
      references check_in (check_in_code);
 
alter table dinning
   add constraint FK_DINNING_用餐合计_DISHES foreign key (food_code)
      references dishes (food_code);
 
alter table leave_message
   add constraint FK_LEAVE_ME_客户留言_CUSTOMER foreign key (customer_code)
      references customer (customer_code);
 
alter table room
   add constraint FK_ROOM_房间属别_ROOM_TYP foreign key (room_type_code)
      references room_type (room_type_code);
 
alter table activity_registration
   drop constraint FK_ACTIVITY_ACTIVITY__CUSTOMER;
 
alter table activity_registration
   drop constraint FK_ACTIVITY_ACTIVITY__ACTIVITY;
 
alter table book
   drop constraint FK_BOOK_住客预定_CUSTOMER;
 
alter table book
   drop constraint FK_BOOK_房间预定_ROOM_TYP;
 
alter table check_in
   drop constraint FK_CHECK_IN_入住房间_ROOM;
 
alter table check_in
   drop constraint FK_CHECK_IN_入住费用_COST;
 
alter table check_in
   drop constraint FK_CHECK_IN_订单入住_CUSTOMER;
 
alter table cost
   drop constraint FK_COST_入住费用2_CHECK_IN;
 
alter table customer_comment
   drop constraint FK_CUSTOMER_客户评价_CUSTOMER;
 
alter table customer_order
   drop constraint FK_CUSTOMER_客户订单_CUSTOMER;
 
alter table customer_order
   drop constraint FK_CUSTOMER_订单预定_BOOK;
 
alter table dinning
   drop constraint FK_DINNING_用餐_CHECK_IN;
 
alter table dinning
   drop constraint FK_DINNING_用餐合计_DISHES;
 
alter table leave_message
   drop constraint FK_LEAVE_ME_客户留言_CUSTOMER;
 
alter table room
   drop constraint FK_ROOM_房间属别_ROOM_TYP;
 
--为顾客电话创建索引
create index index_tel on customer('tel') TABLESPACE HomeStay_idx;
 
 
-- drop table activity cascade constraints;
-- 
-- drop index activity_registration2_FK;
-- 
-- drop index activity_registration_FK;
-- 
-- drop table activity_registration cascade constraints;
-- 
-- drop index 房间预定_FK;
-- 
-- drop index 住客预定_FK;
-- 
-- drop table book cascade constraints;
-- 
-- drop index 入住房间_FK;
-- 
-- drop index 入住费用_FK;
-- 
-- drop index 订单入住_FK;
-- 
-- drop table check_in cascade constraints;
-- 
-- drop index 入住费用2_FK;
-- 
-- drop table cost cascade constraints;
-- 
-- drop table customer cascade constraints;
-- 
-- drop index 客户评价_FK;
-- 
-- drop table customer_comment cascade constraints;
-- 
-- drop index 客户订单_FK;
-- 
-- drop index 订单预定_FK;
-- 
-- drop table customer_order cascade constraints;
-- 
-- drop index 用餐合计_FK;
-- 
-- drop index 用餐_FK;
-- 
-- drop table dinning cascade constraints;
-- 
-- drop table dishes cascade constraints;
-- 
-- drop index 客户留言_FK;
-- 
-- drop table leave_message cascade constraints;
-- 
-- drop index 房间属别_FK;
-- 
-- drop table room cascade constraints;
-- 
-- drop table room_type cascade constraints;
 
--序列与触发器
create sequence AUTOID1
increment by 1
start with 100001
nomaxvalue
nocycle
cache 10;
 
/*触发器用户编号生成*/
CREATE or replace TRIGGER MS_AutoID
BEFORE INSERT ON customer
FOR EACH ROW begin
SELECT ('ms'||trim(to_char(AUTOID1.NEXTVAL,'000000')))
INTO :NEW.CUSTOMER_CODE
FROM DUAL;
dbms_output.put_line('客户编号为'||:NEW.CUSTOMER_CODE);
End;
/
 
create sequence AUTOID2
increment by 1
start with 10001
nomaxvalue
nocycle
cache 10;
 
/*住客预定编号生成*/
CREATE or replace TRIGGER YD_AutoID
BEFORE INSERT ON book
FOR EACH ROW begin
SELECT ('yd'||trim(to_char(AUTOID2.NEXTVAL,'000000')))
INTO :NEW.RESERVATION_CODE
FROM DUAL;
dbms_output.put_line('预订编号为'||:NEW.RESERVATION_CODE);
End;
/
 
 
create sequence AUTOID3
increment by 1
start with 10001
nomaxvalue
nocycle
cache 10;
 
/*订单编号生成*/
CREATE or replace TRIGGER DD_AutoID
BEFORE INSERT ON customer_order
FOR EACH ROW begin
SELECT ('dd'||trim(to_char(AUTOID3.NEXTVAL,'000000')))
INTO :NEW.ORDER_CODE
FROM DUAL;
dbms_output.put_line('订单编号为'||:NEW.ORDER_CODE);
End;
/
 
create sequence AUTOID4
increment by 1
start with 10001
nomaxvalue
nocycle
cache 10;
 
/*入住编号生成*/
CREATE or replace TRIGGER RZ_AutoID
BEFORE INSERT ON CHECK_IN
FOR EACH ROW begin
SELECT ('rz'||trim(to_char(AUTOID4.NEXTVAL,'000000')))
INTO :NEW.CHECK_IN_CODE
FROM DUAL;
dbms_output.put_line('入住编号为'||:NEW.CHECK_IN_CODE);
End;
/
 
create sequence AUTOID5
increment by 1
start with 10001
nomaxvalue
nocycle
cache 10;
 
/*用餐编号生成*/
CREATE or replace TRIGGER DIN_AutoID
BEFORE INSERT ON dinning
FOR EACH ROW begin
SELECT ('yc'||trim(to_char(AUTOID5.NEXTVAL,'000000')))
INTO :NEW.DINNING_CODE
FROM DUAL;
dbms_output.put_line('订单编号为'||:NEW.DINNING_CODE);
End;
/
 
create sequence AUTOID6
increment by 1
start with 10001
nomaxvalue
nocycle
cache 10;
 
/*活动报名编号生成*/
CREATE or replace TRIGGER BM_AutoID
BEFORE INSERT ON activity_registration
FOR EACH ROW begin
SELECT ('bm'||trim(to_char(AUTOID6.NEXTVAL,'00000')))
INTO :NEW.ACTIVITY_RECODE
FROM DUAL;
End;
/
 
 
create sequence AUTOID7
increment by 1
start with 10001
nomaxvalue
nocycle
cache 10;
 
/*留言编号生成*/
CREATE or replace TRIGGER LY_AutoID
BEFORE INSERT ON leave_message
FOR EACH ROW begin
SELECT ('ly'||trim(to_char(AUTOID7.NEXTVAL,'000000')))
INTO :NEW.MESSAGE_CODE
FROM DUAL;
End;
/
 
 
create sequence AUTOID8
increment by 1
start with 10001
nomaxvalue
nocycle
cache 10;
 
/*评价编号生成*/
CREATE or replace TRIGGER PJ_AutoID
BEFORE INSERT ON customer_comment
FOR EACH ROW begin
SELECT ('pj'||trim(to_char(AUTOID8.NEXTVAL,'000000')))
INTO :NEW.COMMENT_CODE
FROM DUAL;
End;
/
 
create sequence AUTOID9
increment by 1
start with 10001
nomaxvalue
nocycle
cache 10;
 
/*房间类型编号生成*/
CREATE or replace TRIGGER LX_AutoID
BEFORE INSERT ON room_type
FOR EACH ROW begin
SELECT ('lx'||trim(to_char(AUTOID9.NEXTVAL,'000000')))
INTO :NEW.ROOM_TYPE_CODE
FROM DUAL;
End;
/
 
 
create sequence AUTOID10
increment by 1
start with 10001
nomaxvalue
nocycle
cache 10;
 
/*活动编号生成*/
CREATE or replace TRIGGER HD_AutoID
BEFORE INSERT ON activity
FOR EACH ROW begin
SELECT ('hd'||trim(to_char(AUTOID10.NEXTVAL,'000000')))
INTO :NEW.ACTIVITY_CODE
FROM DUAL;
End;
/
 
 
select * from customer;
delete from customer;
INSERT INTO customer(customer_name, gender, id, tel) VALUES ('翁乾岚', 'M', '321001199512012472', '13554532227');
INSERT INTO customer(customer_name, gender, id, tel) VALUES ('胡雪擎', 'F', '320112199512033375', '13644089109');
INSERT INTO customer(customer_name, gender, id, tel) VALUES ('殷苑', 'F', '320112199911119251', '13509455854');
INSERT INTO customer(customer_name, gender, id, tel) VALUES ('贝海', 'F', '321001199511110342', '13718678110');
INSERT INTO customer(customer_name, gender, id, tel) VALUES ('楮瑜冷', 'M', '321102199511119990', '13505506023');
INSERT INTO customer(customer_name, gender, id, tel) VALUES ('经经磊', 'F', '321001199912011919', '13602902300');
INSERT INTO customer(customer_name, gender, id, tel) VALUES ('章柏', 'F', '320112199312018246', '13759350230');
INSERT INTO customer(customer_name, gender, id, tel) VALUES ('冯彤祥', 'M', '320021199509036207', '13663741513');
INSERT INTO customer(customer_name, gender, id, tel) VALUES ('何沛', 'F', '321102199911117494', '13660206154');
INSERT INTO customer(customer_name, gender, id, tel) VALUES ('危熠', 'M', '321102199311112316', '13637685244');
 
-- 餐品
select * from dishes;
insert into dishes values (1, '中式精选套餐A', 119, '山药排骨汤，江山鱼，萝卜炖牛腩，红烧豆腐，上汤菠菜，香煎野菜包，热带水果盘');
insert into dishes values (2, '中式精选套餐B', 129, '槟榔花猪肚汤，话梅小排，芹菜炒鱿鱼，外婆豆腐，蒜蓉炒鸡叶菜，香煎葱油饼，热带水果盘');
insert into dishes values (3, '经济套餐',99, '鱼子酱，牛尾汤，红烧鱼片，羔羊腿，蔬菜，布丁，咖啡');
insert into dishes values (4, '情侣套餐', 199, '熏鲑鱼，奶油茄子汤，牛肉蔬菜汤，酒酿鱼，烤牛肉，冷肉拼香肠，酿青椒，酸黄瓜，冰淇淋，咖啡');
 
 
-- 房间类型
delete from room_type;
insert into room_type(room_type_name, room_day_price, room_short_price, room_long_price, room_type_info, room_type_sums) 
values( '单人间', 59, 49, 45, '单人床，一个卫生间，无阳台，有电视电脑，24h热水', 5); -- lx001
insert into room_type(room_type_name, room_day_price, room_short_price, room_long_price, room_type_info, room_type_sums) 
values( '标准间', 99, 89, 79, '单人床两张， 一个卫生间，有阳台，有电视电脑，24h热水', 3);
insert into room_type(room_type_name, room_day_price, room_short_price, room_long_price, room_type_info, room_type_sums)
values( '豪华情侣房', 119, 109, 99, '大床房，一个卫生间，有阳台，有电视电脑，24h热水', 2);
insert into room_type(room_type_name, room_day_price, room_short_price, room_long_price, room_type_info, room_type_sums)
values( '家庭复式', 349, 319, 299, '两室两床，最多可住4人，有客厅，有沙发，有电视电脑，有阳台，24h热水', 1);
select * from room_type;
 
-- 入住房间
delete from room;
insert into room values('1001', 'lx001', '101', 'N', '凌霄斋');
insert into room values('1002', 'lx001', '102', 'N', '云梦阁');
insert into room values('1003', 'lx001', '103', 'N', '碧水间');
insert into room values('1004', 'lx001', '201', 'N', '晴天阁');
insert into room values('1005', 'lx001', '202', 'N', '凌霄斋');
insert into room values('1006', 'lx002', '105', 'N', '月亮湾');
insert into room values('1007', 'lx002', '106', 'N', '天涯间');
insert into room values('1008', 'lx002', '203', 'N', '别云间');
insert into room values('1009', 'lx003', '301', 'N', '郁宁间');
insert into room values('1010', 'lx003', '302', 'N', '赏心阁');
insert into room values('1011', 'lx004', '108', 'N', '披星斋');
select * from room;
 
-- 活动
delete from activity;
insert into activity values('hd1', '山里一日游','出游',date'2022-10-1',date'2022-10-6','庐山','欣赏风景','36.5');
insert into activity(activity_name, activity_type, activity_start_time, activity_end_time, activity_position, activity_content, activity_price)
values('山里一日游','出游',date'2022-10-1',date'2022-10-6','庐山','欣赏风景','36.5');
insert into activity(activity_name, activity_type, activity_start_time, activity_end_time, activity_position, activity_content, activity_price)
values('丛林探险','出游',date'2022-11-1',date'2022-11-7','国家森林公园','游玩','66.7');
select * from activity;

②各触发器实现的相关功能及测试sql如下：


-- 用户注册
CREATE OR REPLACE PROCEDURE customers_reg(
 customer_name customer.customer_name%type,
 gender customer.gender%type,
 id1 customer.id%type,
 tel1 customer.tel%type)
 as
 cnm number;
BEGIN
 select count(*) into cnm from customer where id1=customer.id;
 if cnm>0 then
   dbms_output.put_line('该用户已注册，请勿重复注册!');
   return;
 end if;
 INSERT INTO customer(customer_name, gender, id, tel) VALUES (customer_name,gender,id1,tel1);
 dbms_output.put_line('注册成功');
END customers_reg;
--  测试
call customers_reg('丽江好', 'M', '123456', '110');
select * from customer;
 
 
-- 预定
CREATE OR REPLACE PROCEDURE Customer_book(
c_code varchar2, rt_code varchar2,  --顾客编号 房间类型编号 订单编号
l_way varchar2,re_citime DATE, re_cotime DATE)  -- 租聘方式 预定入住时间 预定退房时间
is  
count1 number;  -- 
r_code varchar(16);  
r_name varchar(32);  
 
begin  
   select count(*) into count1 from customer where customer_code = c_code;  
   if count1=0 then  
   dbms_output.put_line('该住客不存在！');  
    else   
       select count(*) into count1 from room where room_type_code = rt_code and check_out_confirm = 'n';  
      if count1=0 then   
           dbms_output.put_line('该房型预定已满不存在！');   
      else  
         INSERT INTO book(customer_code,room_type_code,lease_way,reservation_check_in_time,reservation_check_out_time)  
         values(c_code, rt_code,l_way,re_citime,re_cotime); 
         
         select room_code into r_code from (
                select *from room where room_type_code = rt_code and check_out_confirm = 'n'
                ) where rownum <= 1;  
        update room set check_out_confirm = 'y' where room_code = r_code;  
        select room_name into r_name from room where room_code = r_code;  
         dbms_output.put_line('预定成功！,房间编号code为；'||r_code || '房间名：' || r_name);  
     end if;  
  end if;  
 end;
 --   测试
select * from customer_order;
alter table book drop column order_code;
 
call customer_book('ms100011', 'lx001', 's', date'2022-9-7', date'2022-9-10');
select * from book;
 
select * from room;
update room set check_out_confirm = 'n';
 
select reservation_code into sel_reservation_code from book 
         where customer_code = c_code;
  sel_reservation_code book.reservation_code%type;  
       
  
-- 订单加入
insert into customer_order values('dd001', 'ms100011', 'yd010001', sysdate);
select * from customer_order;
 
 
-- 入住办理
CREATE OR REPLACE PROCEDURE do_checkin(
	room_code varchar2, o_code varchar2, lease_way varchar, check_itime date, check_otime date,
	check_name varchar2, check_id varchar2, check_tel number)
	is
	begin
	    INSERT INTO  check_in(room_code,order_code,check_in_lease_way,check_in_time,
	    check_out_time,check_in_person_name,check_in_person_id_card,check_in_person_tel)
	    values(room_code, o_code, lease_way, check_itime,check_otime, check_name, check_id, check_tel);
	end;
 
--    测试
call do_checkin('1001', 'dd001', 's', date'2022-9-7', date'2022-9-10', '丽江好', '123456', '110');
select * from check_in; 
 
 
-- 就餐
select * from dishes;
select * from dinning;
insert into dinning(check_in_code, food_code, dinning_time) values('rz010001', '1', sysdate);
select * from dinning;
 
--参加活动
select * from activity;
call activity_registration_proc('ms100011', 'hd010001');
select * from activity_registration;
 
 
-- 费用结算
CREATE OR REPLACE PROCEDURE get_cost(
  o_code customer_order.order_code%type,c_code customer.customer_code%type)
  is
  day_begin check_in.check_in_time%type;
  day_end date;
  roomcode varchar2(16);
  roomtype varchar2(16);
  checkway varchar2(1);
  checkcode varchar2(16);
  day_diff number;
  dishestime number;
  onedayroom_cost float;
  onedishescost float;
  roomcost float;
  foodtype varchar2(16);
  dishescost float;
  actcode varchar2(16);
  actprice float;
  actcost float;
  res float;
  begin
     select check_in_time into day_begin from check_in where order_code = o_code;
     select check_out_time into day_end from check_in where order_code = o_code;
     select check_in_lease_way into checkway from check_in where order_code = o_code;
     day_diff := day_end - day_begin;
 
     select room_code into roomcode from check_in where order_code = o_code;
     select room_type_code into roomtype from room where room_code = roomcode;
     
     if checkway = 'd' then
     select room_day_price into onedayroom_cost from room_type where room_type_code = roomtype;
     roomcost := onedayroom_cost * day_diff;
     end if;
     
     if checkway = 's' then
     select room_short_price into onedayroom_cost from room_type where room_type_code = roomtype;
     roomcost := onedayroom_cost * day_diff;
     end if;
     
     if checkway = 'l' then
     select room_long_price into onedayroom_cost from room_type where room_type_code = roomtype;
     roomcost := onedayroom_cost * day_diff;
     end if;
     dbms_output.put_line('房间费用为：'||roomcost||'元');
 
     select check_in_code into checkcode from check_in where order_code = o_code;
     select food_code into foodtype from (select * from dinning where check_in_code = checkcode)
     where rownum <= 1;
     select food_price into onedishescost from dishes where food_code = foodtype;
     select count(*) into dishestime from dinning where check_in_code = checkcode;
     dishescost := onedishescost * dishestime;
     dbms_output.put_line('用餐费用为：'||dishescost||'元');
 
     select activity_code into actcode from activity_registration
     where customer_code = c_code;
     select activity_price into actprice from activity
     where activity_code = actcode;
     actcost := actprice;
     dbms_output.put_line('活动费用为：'||actcost||'元');
     res :=roomcost + dishescost + actcost;
     dbms_output.put_line('最终费用为：'||res||'元');
 end;
 
--   测试
select * from customer;
select * from customer_order;
call get_cost('dd001', 'ms100011');
 
-- 退房
  CREATE OR REPLACE PROCEDURE do_checkout(  
  room_code1 varchar2)  
  is  
  count1 number;  
  count2 number;  
  begin  
      select count(*) into count1 from room where room_code = room_code1;  
      if count1 = 0 then  
      dbms_output.put_line('该房间不存在');  
     else  
         select count(*) into count2 from room where room_code = room_code1 and check_out_confirm = 'y';  
         if count2 = 0 then  
         dbms_output.put_line('该房间暂无住客');  
         else  
         update room set check_out_confirm = 'n' where room_code = room_code1 and check_out_confirm = 'y';  
         dbms_output.put_line('退房成功');  
         end if;  
     end if;  
 end;
 
--   测试
select * from room;
call do_checkout('1002');
select * from room;
 
 -- 新建房间类型
 CREATE OR REPLACE PROCEDURE insert_roomtype(
lxcode varchar2,
lxname varchar2,
dcost float,
scost float,
lcost float,
rinfo varchar2
)
 is
 count1 number;
  begin
      select count(*) into count1 from room_type where room_type_code=lxcode;
      if count1 = 1 then
      dbms_output.put_line('该房间类型号已存在');
     else
      insert into room_type(ROOM_TYPE_CODE,ROOM_TYPE_NAME,ROOM_DAY_PRICE,ROOM_SHORT_PRICE,ROOM_LONG_PRICE,ROOM_TYPE_INFO)
      values (lxcode,lxname,dcost,scost,lcost,rinfo);
      end if;
 end;
 
 --查询入住房间及情况
 CREATE OR REPLACE PROCEDURE select_rm(rm_code room.room_code%type) AS
--声明异常的变量
no_result EXCEPTION;
rm_number room.room_number%type;
check_out room.check_out_confirm%type ;
BEGIN
 --根据传入的id删除用户
 select room_number,check_out_confirm into rm_number,check_out from room
 where room_code=rm_code;
 --使用游标判断是否删除成功
 IF rm_number is null THEN
   --没有删除成功抛出异常
   RAISE no_result;
 END IF;
 dbms_output.put_line('房间编号：'||rm_code||'，房间名：'||rm_number||',入住情况：'||check_out);
EXCEPTION
  WHEN no_result THEN
    dbms_output.put_line('房间编号不存在！');
  WHEN OTHERS  THEN
    dbms_output.put_line('房间编号不存在！');
END select_rm;
 
 
 
-- 活动创建
CREATE OR REPLACE PROCEDURE activity_add(
 code activity.activity_code%type,
 name activity.activity_name%type,
 act_type activity.activity_type%type,
 start_time activity.activity_start_time%type,
 end_time activity.activity_end_time%type,
 position activity.activity_position%type,
 content activity.activity_content%type,
 price activity.activity_price%type)
 is
BEGIN
 INSERT INTO activity VALUES (code,name,act_type,start_time,
 end_time,position,content,price);
 dbms_output.put_line('活动创建成功！');
END activity_add;
 
--留言
create or replace procedure do_leave_message(
customer_code1 customer.customer_code%type,
l_message_tinfo leave_message.message_tinfo%type)
is
begin
  insert into leave_message(customer_code, message_tinfo,message_time)
  values(customer_code1, l_message_tinfo, sysdate);
 
  dbms_output.put_line('留言成功！');
  dbms_output.put_line('调用完成！');
end;
 
-- 留言调用测试
call do_leave_message('ms100001', '民宿不错！');
select * from leave_message;
 
 
--评价
create or replace procedure do_customer_comment(
   p_customer_code customer.customer_code%type,
   p_comment_score customer_comment.comment_score%type,
   p_comment_content customer_comment.comment_content%type
  ) 
is
begin
  insert into customer_comment(customer_code,comment_score,comment_content)
  values(p_customer_code,p_comment_score,p_comment_content );
  dbms_output.put_line('评价成功！');
  dbms_output.put_line('调用完成！');
end;
--评价调用测试
call do_customer_comment('ms100013', 3, '还不错'); 
select * from customer_comment;

一、项目概述

（一）项目简介与背景

民宿通常指的是自用住宅空闲房间，结合当地人文、自然景观、生态、环境资源等方面资源，以家庭副业方式经营，提供旅客居住生活的方式。近年来，民宿凭借其接地气、特色化、具有家庭氛围等优势，逐步发展成为一种住店潮流,深受国内外旅行者的喜爱。而民宿的管理水平的高低将直接影响到民宿的形象和声誉。伴随社会信息化脚步的发展，民宿服务只有走上网络化、智能化的发展之路，才能更好的满足人们对高标准、高质量和个性化服务的追求，因此，民宿管理系统的开发拥有着广泛的应用前景。

本项目旨在借助数据库相关知识打造符合实际需求，结构完整的民宿管理系统，采用Oracle数据库实现各项需求。分为房间管理、入住、餐饮、活动、评价、留言六大功能模块。

（二）项目功能

2.1房间管理

①房间信息录入：管理员可以向系统中录入房间的编号、房间号、房间类型、房间价格（日租、短租、长租）、是否已入住的信息。

②房间信息查询：工作人员可以通过房间信息表查询房间的信息，需要精确，模糊查询，如房间号查询，已入住房间查询，未入住房间查询，已入住客户信息查询等方式。

③房间信息管理：管理员可以根据需要，对房间的编号，房间号、房间类型、房间价格等信息进行添加，修改和删除等功能。

2.2入住管理

①预定功能：客户可以通过网络查询到各类的房间信息，包括基本图片、类型、价格、是否已经被预定、餐饮等信息。住客需要录入预定信息，预定信息包括住客的姓名、性别、身份证、联系方式基本信息和租赁方式、预定入住时间、预定退房时间。根据房间信息和人员信息判断能否预定成功。当系统接收到该房间被预定后，需将房间信息中是否预定的信息更变为已预定。

②入住办理：当客户办理入住手续时，如已预定，在登记住客身份证后将该用户预定信息一并记录。如未预定，在客户查询客房各类信息之后，登记其姓名、性别、身份证号、联系方式、入住房间、入住开始结束时间、租赁方式，房间已有入住人员则不允许办理入住。

③住客信息查询：工作人员可以通过住客登记的姓名、身份证，入住时间，房间号等查询到住客的住房信息以及其住房的历史记录等信息。

④退房办理：在客户需要办理退房手续且上交房卡后，工作人员进行退房，先计算顾客总共消费，付钱后完成退房流程，在房间信息表中将该房间标记为未入住，同时更新入住记录和预定信息住客状态。

2.3餐饮

①餐饮信息录入：工作人员需向系统录入餐饮时间、价格、内容等信息。

②餐饮信息查询。

③餐饮信息修改。

④用餐人员信息录入：在接收到网上预定或入住办理时用户用餐的信息，工作人员需要将用餐人员的时间录入系统。

⑤用餐信息查询：工作人员，管理员可以通过日期，客户姓名，房间号、身份证号查询其用餐信息。

⑥用餐信息修改和删除。

2.4活动管理

①活动创建：工作人员可以定期组织租客举办交友，游戏等活动。此时工作人员需要将活动时间，内容，地点，收费等信息录入系统中。

②活动信息修改：工作人员可以修改活动信息。

③活动信息查询：管理员可以通过时间、名称查询活动信息，查询今后一周的活动信息。

④活动信息展示：客户可以通过民宿大屏幕查看活动信息。

⑤活动报名：在客户查询到未来的活动后，可以通过工作人员报名此活动，将自己的姓名、房间号或身份证等基本信息录入系统。

2.5评价模块

①评价：客户在办理退房手续后，可以对入住期间的环境，服务进行综合评价，打分。工作人员将评价信息录入系统。

②评价查询：客户、工作人员、管理员可以查询历史评价信息，包括好评，差评、综合查询方式。

2.6留言板块

①留言信息录入：租客可以通过工作人员留下自己在该地旅行居住的各类感受或者写简单的游记。

②留言信息展示：租客可以通过大屏幕赏阅历史的留言，游记等。

图1 功能模块图

二、需求分析

系统开发之前，先要充分了解和熟悉有关民宿相关的信息，并区分其与一般旅馆的区别和其存在的特点，充分了解民宿管理系统中的所需功能项，从而使开发设计的系统可以全面便捷的概括所需的操作，以达到满足使用者的需求。

（一）业务流程图

一个简化的业务流程如图2所示：

图2 业务流程图

（二）数据流程

2.1数据流图

（1）顶层数据流图

一级数据流图包括主要的外部实体、数据存储对象以及流程，首先确定住客、前台人员、管理人员三个主要的外部实体，随后根据需求分析列出了入住预定与退房、餐饮信息管理、房间信息管理等六个流程。对于入住预定与退房以及活动管理还扩展了二级数据流图。最后的是存储表单的确定，根据系统信息需要我们共写出了客户信息、入住信息等十二个存储表。

（2）二级数据流图

二级数据流图是对一级部分流程的细化。入住预定与退房流程包含预定办理、入住办理以及退房办理三个业务流程；活动信息管理流程包括活动信息管理以及活动报名两个流程。

三、数据库概念模型

本民宿管理数据库设计主要实体对象包括客户、房间、活动、餐饮、评价、留言等，共12个实体模型。

图3 概念模型

四、数据库物理模型

（一）物理模型

物理模型是由概念模型通过PowerDesigner自动生成得到的，将概念模型中的联系转为外键引入到实体中，更能体现实体间的联系。

图4 物理模型

（二）数据库表

数据库的主要表格包括：

客户表
customer (customer_code, customer_name, gender, id, tel)
餐品表
dishes (food_code, food_name, food_price, food_content)
房间类型
room_type (room_type_code, room_type_name, room_day_price,
room_short_price, room_long_price, room_type_info, room_type_sums)
入住房间
room (room_code, room_name, check_out_confirm, room_type_code)
活动表 activity
(activity_code, activity_name, activity_type, activity_start_time, activity_end_time, activity_position, activity_content, activity_price)
报名表
activity_registration (customer_code, activity_code, activity_recode)
留言表 leave_message (message_code, customer_code, message_time, message_info)
评价表 customer_comment (comment_code, customer_code, comment_score,
comment_content)
订单表
customer_order (order_code, customer_code, order_time)
预定表
book (reservation_code, customer_code, room_type_code, order_code, lease_way, reservation_check_in_time, reservation_check_out_time, room_code_res)
入住表
check_in (check_in_code, room_code, order_code, check_in_lease_way, check_in_time, check_out_time, check_in_person_name, check_in_person_id_card, check_in_person_tel)
用餐表
dinning (dinning_code, food_code, check_in_code, dinning_time)
费用表 cost (cost_code, check_in_code, dinning_cost, room_cost, total_cost)

（三）表空间创建

表空间是Oracle数据库最大的逻辑单位，Oracle数据库通过表空间来组织数据库中的数据。一个数据库逻辑上由一个或多个表空间组成，一个表空间物理上是由一个或多个数据文件组成。在任何一个时刻，一个数据文件只能属于一个表空间，一个表空间只能属于一个数据库，反之则不成立。

永久表空间：专门针对用户的具体应用而创建的数据表空间，用户方案对象（如表、索引等）中的数据就存放在此。

临时表空间：保存SQL语句在执行过程中所产生的临时数据（如查询操作中的记录排序、分组汇总等）。此类表空间通常不能存放用户数据，由系统自动管理，其中的数据不需要永久保存，属于“临时”性质。

本项目创建表空间如下：

3.1 永久表空间 HomeStay1_data


CREATE TABLESPACE HomeStay1_data DATAFILE 'D: \Data\local\sql\HomeStay1\HomeStay1_data. dbf' 
SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED ONLINE;

3.2 临时表空间 HomeStay_temp


CREATE TEMPORARY TABLESPACE HomeStay1_temp TEMPFILE 
'D:\Data\local\sql\HomeStay1\HomeStay1_temp.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 500M;

（四）用户创建

Oracle数据库对安全性的管理，使用用户以及授权权限来达到安全性。下面是对住客、老板、前台分别创建的用户，并为其授予权限。创建授权如下：

4.1 创建管理员用户


CREATE USER manager1 IDENTIFIED BY 123 DEFAULT TABLESPACE HomeStay1_data  QUOTA UNLIMITED ON  HomeStay1_data TEMPORARY TABLESPACE HomeStay1_temp;
GRANT dba TO manager1;

4.2 创建前台用户


create user proscenium identified by proscenium123 TEMPORARY TABLESPACE HomeStay_temp;
GRANT CONNECT TO proscenium;

（五）关键应用编程实现

顾客注册账号、预定房间、顾客入住、顾客入住办理、顾客退房办理、费用结算、用餐、活动报名、在表中插入相关的新信息、留言和评价。下面展示用存储过程实现的一些主要功能。

5.1 预定功能

通过传入顾客编号、预定房间的类型编号、租聘方式、预计入住时间、预计退房时间，先进性判断各信息是否正确，房间是否有空缺，最后预定成功插入相关表中.


CREATE OR REPLACE PROCEDURE Customer_book(
c_code varchar2, rt_code varchar2,  --顾客编号 房间类型编号
l_way varchar2,re_citime DATE, re_cotime DATE)  -- 租聘方式 预定入住时间 预定退房时间
is  
count1 number;  -- 
r_code varchar(16);  
r_name varchar(32);  
 
begin  
   select count(*) into count1 from customer where customer_code = c_code;  
   if count1=0 then  
   dbms_output.put_line('该住客不存在！');  
    else   
       select count(*) into count1 from room where room_type_code = rt_code and check_out_confirm = 'n';  
      if count1=0 then   
           dbms_output.put_line('该房型预定已满不存在！');   
      else  
         INSERT INTO book(customer_code,room_type_code,lease_way,reservation_check_in_time,reservation_check_out_time)  
         values(c_code, rt_code,l_way,re_citime,re_cotime); 
         
         select room_code into r_code from (
                select *from room where room_type_code = rt_code and check_out_confirm = 'n'
                ) where rownum <= 1;  
        update room set check_out_confirm = 'y' where room_code = r_code;  
        select room_name into r_name from room where room_code = r_code;  
         dbms_output.put_line('预定成功！,房间编号code为；'||r_code || '房间名：' || r_name);  
     end if;  
  end if;  
 end;

5.2 入住办理功能

通过传入房间编号、订单编号、租聘方式、入住时间、预计退房时间，进行入住办理。


CREATE OR REPLACE PROCEDURE do_checkin(
	room_code varchar2, o_code varchar2, lease_way varchar, check_itime date, check_otime date,
	check_name varchar2, check_id varchar2, check_tel number)
	is
	begin
	    INSERT INTO  check_in(room_code,order_code,check_in_lease_way,check_in_time,
	    check_out_time,check_in_person_name,check_in_person_id_card,check_in_person_tel)
	    values(room_code, o_code, lease_way, check_itime,check_otime, check_name, check_id, check_tel);
	end;

5.3 活动报名功能

通过传入顾客编号和活动编号先进性边界判断，最后报名成功，在相关表中添加报名信息。


create or replace procedure activity_registration_proc(
in_cus_code in customer.customer_code%type,
in_act_code in activity.activity_code%type) is
begin
  declare
    cus_name customer.customer_name%type;
    act_name activity.activity_name%type;
    cursor cur_cusName is select customer_name from customer where customer_code = in_cus_code;
    cursor cur_actName is select activity_name from activity where activity_code = in_act_code;
  begin
    open cur_cusName;
    open cur_actName;
    fetch cur_cusName into cus_name;
    fetch cur_actName into act_name;
 
    if cur_cusName%found then
      if cur_actName%found then
        insert into activity_registration(customer_code, activity_code)
        values(in_cus_code, in_act_code);
        commit;
        dbms_output.put_line(in_cus_code||'顾客：'||cus_name||'报名 '||in_act_code||' 活动 '||act_name||'成功！');
      else dbms_output.put_line('未查到编号'||in_act_code||'活动！');
      end if;
    else dbms_output.put_line('未查到编号'||in_cus_code||'顾客！');
    end if;
    close cur_cusName;
    close cur_actName;
  end;

5.4 新建房间类型


CREATE OR REPLACE PROCEDURE insert_roomtype(
lxcode varchar2,
lxname varchar2,
dcost float,
scost float,
lcost float,
rinfo varchar2
)
 is
 count1 number;
  begin
      select count(*) into count1 from room_type where room_type_code=lxcode;
      if count1 = 1 then
      dbms_output.put_line('该房间类型号已存在');
     else
      insert into room_type(ROOM_TYPE_CODE,ROOM_TYPE_NAME,ROOM_DAY_PRICE,ROOM_SHORT_PRICE,ROOM_LONG_PRICE,ROOM_TYPE_INFO)
      values (lxcode,lxname,dcost,scost,lcost,rinfo);
      end if;
 end;

5.5 费用结算功能

通过传入的顾客编号和订单编号，计算出相关的消费：房费、用餐费用和活动费用，并插入到消费表中。


CREATE OR REPLACE PROCEDURE get_cost(
    o_code customer_order.order_code%type,c_code customer.customer_code%type)
    is-- 订单编号                                   顾客编号
    day_begin date; -- 需查询入住时间
    day_end date;   -- 需查询退房房间
    roomcode varchar2(16);-- 房间编号
    roomtype varchar2(16);-- 房间类型
    checkway varchar2(1);--  租聘方式
    checkcode varchar2(16);--入住编号
    day_diff number; -- 天数
    dishestime number;-- 餐品次数
    onedayroom_cost float;--房间单价
    onedishescost float;-- 餐品单价
    roomcost float; -- 房间总费用
    foodtype varchar2(16); -- 餐品类型
    dishescost float; -- 餐品总费用
    actcode varchar2(16); -- 活动编号
    actprice float; -- 活动价格
    actcost float; --活动总费用
    res float; --   总费用
    begin
       select check_in_time into day_begin from check_in where order_code = o_code;
       select check_out_time into day_end from check_in where order_code = o_code;
       select check_in_lease_way into checkway from check_in where order_code = o_code;
       day_diff := day_end - day_begin;
 
       select room_code into roomcode from check_in where order_code = o_code;
       select room_type_code into roomtype from room where room_code = roomcode;
       
       if checkway = 'd' then
       select room_day_price into onedayroom_cost from room_type where room_type_code = roomtype;
       roomcost := onedayroom_cost * day_diff;
       end if;
       
       if checkway = 's' then
       select room_short_price into onedayroom_cost from room_type where room_type_code = roomtype;
       roomcost := onedayroom_cost * day_diff;
       end if;
       
       if checkway = 'l' then
       select room_long_price into onedayroom_cost from room_type where room_type_code = roomtype;
       roomcost := onedayroom_cost * day_diff;
       end if;
       dbms_output.put_line('房间费用为：'||roomcost||'元');
 
       select check_in_code into checkcode from check_in where order_code = o_code;
       select food_code into foodtype from (select * from dinning where check_in_code = checkcode)
       where rownum <= 1;
       select food_price into onedishescost from dishes where food_code = foodtype;
       select count(*) into dishestime from dinning where check_in_code = checkcode;
       dishescost := onedishescost * dishestime;
       dbms_output.put_line('用餐费用为：'||dishescost||'元');
 
       select activity_code into actcode from activity_registration
       where customer_code = c_code;
       select activity_price into actprice from activity
       where activity_code = actcode;
       actcost := actprice;
       dbms_output.put_line('活动费用为：'||actcost||'元');
       res :=roomcost + dishescost + actcost;
       dbms_output.put_line('最终费用为：'||res||'元');
       insert into cost(check_in_code, dinning_cost, room_cost, total_cost) 
       values(checkcode, dishescost, roomcost, res);
       update customer_order set payed = 'y' where o_code = order_code;
       commit;
   end;

5.6 退房办理

通过传入的房间编号进行边界判断，判断房间编号是否正确以及是否有顾客入住，完成退房修改相关表的信息。


CREATE OR REPLACE PROCEDURE do_checkout(  
  room_code1 varchar2)  
  is  
  count1 number;  
  count2 number;  
  begin  
      select count(*) into count1 from room where room_code = room_code1;  
      if count1 = 0 then  
      dbms_output.put_line('该房间不存在');  
     else  
         select count(*) into count2 from room where room_code = room_code1 and check_out_confirm = 'y';  
         if count2 = 0 then  
         dbms_output.put_line('该房间暂无住客');  
         else  
         update room set check_out_confirm = 'n' where room_code = room_code1 and check_out_confirm = 'y';  
         dbms_output.put_line('退房成功');  
         end if;  
     end if;  
 end;

5.7 序列与触发器

触发器利用序列生成相关表的编号自增。下面是其中一个，其余10个触发器功能和这个类似，都是给表进行编号自增。

--序列与触发器
create sequence AUTOID1
increment by 1
start with 100001
nomaxvalue
nocycle
cache 10;

/*触发器用户编号生成*/
CREATE or replace TRIGGER MS_AutoID
BEFORE INSERT ON customer
FOR EACH ROW begin
SELECT ('ms'||trim(to_char(AUTOID1.NEXTVAL,'000000')))
INTO :NEW.CUSTOMER_CODE
FROM DUAL;
dbms_output.put_line('客户编号为'||:NEW.CUSTOMER_CODE);
End;

（六）测试

6.1 权限管理

6.1.1 授予管理员boss resource和connect权限，允许登录和创建实体及对活动表、活动报名表、房间表、房间类型表的insert、update、select、delete权限

6.1.2 授予前台用户proscenium resource和connect权限，允许登录和创建实体及对住客表、菜品表、订单表、入住表、用餐表、费用表的insert、update、select、delete权限；对房间表、房间类型表的select权限。

6.1.3 增

INSERT INTO room_type VALUES ('lx004','豪华单人间',250,238,218,'独立房间，1室1床，可住1人，生活设备一应俱全');

6.1.4 删

DELETE from room_type where room_type_code = 'lx004';

6.1.5 改

update room_type set room_day_price=room_day_price*0.9 where room_type_name='商务标准双人房';

6.1.6 查

select room_long_price from room_type where room_type_name='商务标准双人房';

6.2 数据完整性

实体完整性、参照完整性、用户完整性

6.3 存储过程结构测试

6.3.1 录入房间信息测试

call insert_room_type('lx004','豪华单人间',250,238,218,'独立房间，1室1床，可住1人，生活设备一应俱全');

接口名称	测试用例	预期结果	实际结果	是否通过
insert_room_type	同上	插入成功	插入成功	是
insert_room_type	同上	弹出重复提示	已存在该房间类型编号，插入失败	是

6.3.2 查询房间信息测试

接口名称

测试用例

预期结果

实际结果

是否通过

select_rm

'fj100010'

输出房间信息

如下图

是

select_rm

'fj100015'

弹出错误提示

房间编号不存在

是

call select_rm('fj100010');

call select_rm('fj100015');

6.3.3 住客注册功能测试

Callcustomers_reg('ms100013','李健豪','m',123456','110');

接口名称	测试用例	预期结果	实际结果	是否通过
customers_reg	同上	注册成功	注册成功	是
customers_reg	同上	弹出重复提示	该用户已注册，请勿重复注册	是
customers_reg	如下	请输入18位身份证号	注册成功	否
customers_reg	如下	请输入11位手机号	注册成功	否

6.3.4 餐饮信息录入测试

call dishes_add('cp100004','豪华会议餐','150','京都排条套餐，猪排饭，奥尔良鸡排套餐');

接口名称	测试用例	预期结果	实际结果	是否通过
dishes_add	同上	添加成功	套餐添加成功	是
dishes_add	同上	弹出重复提示	该套餐已存在，请勿重复添加	是

6.3.5 住客预定功能测试

call Customer_book('yd100003', 'ms100013', 'lx002', 'dd100003','l',sysdate, sysdate+7);

接口名称	测试用例	预期结果	实际结果	是否通过
Customer_book	同上	预定成功	如下图	是
Customer_book	同上	弹出重复提示	该预订已注册，请勿重复预订	是
Customer_book	如下	无空房	该房型预订已满	是

call Customer_book('yd100004', 'ms100013', 'lx002', 'dd100003','l',sysdate, sysdate+7);

6.3.6入住办理功能测试

call do_checkin('1001', 'dd002', 's', date'2022-9-7', date'2022-9-10', '丽江好', '123456', '110');

6.3.7 用餐信息录入测试

insert into dinning(check_in_code, food_code, dinning_time) values('rz010002', '1', sysdate);

6.3.8 费用结算测试

call get_cost('dd002', 'ms100013');

6.3.9 活动报名测试

接口名称	测试用例	预期结果	实际结果	是否通过
activity_registration_proc	('ms100001','hd010001')	报名成功，反馈信息	如图	是
activity_registration_proc	('ms1','hd10001')	报名失败，提示信息	没有这个编号的租户	是
activity_registration_proc	('ms100001','hd1')	报名失败，提示信息	没有这个编号的活动	是

call activity_registration_proc('ms100001','hd010001');

查询activity_registration活动报名表，信息正确。

call activity_registration_proc('ms100001','hd1');

call activity_registration_proc('ms1','hd010001');

6.3.10 留言测试

call do_leave_message('ly100001','ms100013',sysdate,'我觉得这家民宿真不错啊，真不错！！！');

6.3.11 评价测试

call do_comment('pl100002','ms100013', 3, '还不错');

6.3.12 退房办理

call do_checkout('fj100008');

五、总结

本项目中，我的任务主要数据库模型的建立，概念模型图以及物理模型图，表结构和字段的具体设计，用触发器和存储过程实现活动报名功能和费用结算功能。在小组讨论好我们的所有表和联系后，我用PowerDesigner绘制了概念模型图，然后优化一下各个表的具体哪些字段、类型已经各表的主键、外键设计。概念模型需要确定系统中有哪些实体以及实体之间的联系，同时还要写上实体所有的属性名、主键等。联系不仅要写出联系名还要写出联系的类型，包括一对一、一对多等等。物理模型可以在概念模型的基础上自动生成，其会自动地将概念模型中的外键引入到实体属性中，同时箭头指向引入外键的实体。在设计费用结算时卡了很久，也不断进行优化数据和逻辑流程，因为涉及表比较多，也debug了很久，让我对于存储过程和多表之间的关联查询有了更加深刻的理解。

这个项目的背景是学校数据库实训课的大作业，给了五天的时间，要完成相关项目的数据库开发，因为时间问题所以并没有要求前后端，只需要有相关sql操作就行，触发器实现相关功能，纯粹是因为体现触发器的教学而使用的，实际上并不完全需要。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/秋刀鱼在做梦/article/detail/791503