数据库大作业实训项目-民宿管理
Oracle数据库实训项目-民宿管理，纯sql数据库操作相关，没有前端后端。触发器等相关功能实现纯粹为了使用触发器而使用的触发器，实际上无需触发器也能实现。
-
Mysql数据库实验及练习题相关
MySQL 数据库和表的管理-数据库实验一
MySQL连接查询、索引、视图-数据库实验二、实验三
MySQL约束、触发器-数据库实验四
MYSQL存储过程和存储函数-数据库实验五
MySQL批量随机生成name、TEL、idNumber
MYSQL数据库的安全管理-数据库实验六
MYSQL数据库安全性练习题
MYSQL数据库的备份与恢复-数据库实验七
MYSQL数据库设计题-窗帘店
目录
Mysql数据库实验及练习题相关
①建表空间、建表、插入数据相关sql如下：
②各触发器实现的相关功能及测试sql如下：
一、项目概述
（一）项目简介与背景
（二）项目功能
2.1房间管理
2.2入住管理
2.3餐饮
2.4活动管理
2.5评价模块
2.6留言板块
二、需求分析
（一）业务流程图
（二）数据流程
2.1数据流图
（1）顶层数据流图
（2）二级数据流图
三、数据库概念模型
四、数据库物理模型
（一）物理模型
（二）数据库表
（三）表空间创建
3.1 永久表空间 HomeStay1_data
3.2 临时表空间 HomeStay_temp
（四）用户创建
4.1 创建管理员用户
4.2 创建前台用户
（五）关键应用编程实现
5.1 预定功能
5.2 入住办理功能
5.3 活动报名功能
5.4 新建房间类型
5.5 费用结算功能
5.6 退房办理
5.7 序列与触发器
（六）测试
6.1 权限管理
6.1.2 授予前台用户proscenium resource和connect权限，允许登录和创建实体及对住客表、菜品表、订单表、入住表、用餐表、费用表的insert、update、select、delete权限；对房间表、房间类型表的select权限。
6.1.3 增
6.1.4 删
6.1.5 改
6.1.6 查
6.2 数据完整性
6.3 存储过程结构测试
6.3.1 录入房间信息测试
6.3.2 查询房间信息测试
6.3.3 住客注册功能测试
6.3.4 餐饮信息录入测试
6.3.5 住客预定功能测试
6.3.6入住办理功能测试
6.3.7 用餐信息录入测试
6.3.8 费用结算测试
6.3.9 活动报名测试
6.3.10 留言测试
6.3.11 评价测试
6.3.12 退房办理
五、总结
①建表空间、建表、插入数据相关sql如下：
--永久表空间 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
免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们，邮箱：ciyunidc@ciyunshuju.com。本站只作为美观性配图使用,无任何非法侵犯第三方意图,一切解释权归图片著作权方,本站不承担任何责任。如有恶意碰瓷者,必当奉陪到底严惩不贷!
