餐飲管理數據庫課程設計-資料下載頁

　　

【正文】 (10) not null, Customersex varchar2(4) check(Customersex = 39。男39。 or Customersex = 39。女39。)not null, Custoemrphoneno varchar2(20) unique, VIP_level number, totalexpend number)drop table Foodtablecreate table Foodtable桌臺信息(Foodtableno varchar2(10)primary key, seatingno number not null, Foodtablestate varchar2(6) check(Foodtablestate = 39。空39。 or Foodtablestate = 39。占39。))drop table Odercreate table Oder訂單(Oderno varchar2(10) primary key, Customerno varchar2(10) not null, Odertime date not null, Foodtableno varchar2(10) not null, foreign key (Customerno) references Customer(Customerno), foreign key (Foodtableno) references Foodtable(Foodtableno))create table Menus 菜單(Menuno varchar(10)primary key, Foodtableno varchar(10) not null, Customerno varchar(10)not null, Oderno varchar(10), Workerno varchar(10)not null, consumetime date, foreign key(Foodtableno) references Foodtable(Foodtableno), foreign key(Customerno) references Customer(Customerno))drop table Menus_Dishcreate table Menus_Dish 菜單_菜品(Menuno varchar2(10) , Dishno varchar2(10) , Dishname varchar2(20) not null, Amount number check(Amount0) not null, primary key(Menuno,Dishno), foreign key (Dishno) references Dish(Dishno))create table Discount_rules折扣規則(VIP_level int primary key, Discount number not null)create table worker員工(Workerno varchar2(10) primary key, Workername varchar2(10) not null, Workersex varchar(4) check(Workersex = 39。男39。 or Workersex = 39。女39。), Workerage number, Workersalary number not null)drop table Sales_bill create table Sales_bill銷售賬單(Sales_billno varchar2(10) primary key, Billno varchar2(10), discounted_cost number, Billtime date, foreign key (Billno) references Bill(Billno))附錄2 存儲過程（1） 預定create or replace procedure 預定(in_oderno in %type,in_customerno in %type,in_odertime in %type,in_Foodtableno in %type) isbegin insert into oder values(in_oderno,in_customerno,in_odertime,in_Foodtableno)。end 預定。（2） 預定查詢create or replace procedure 查詢預訂 (in_Customerno in %type)issum1 number。out_oderno %type。begin select count(Customerno) into sum1 from oder where Customerno=in_Customerno。 if (sum10) then ( 39。已預訂39。)。 select oderno into out_oderno from oder where customerno=in_Customerno。 ( 39。預訂號:39。||out_oderno)。 else ( 39。未預定39。) 。 end if。end 查詢預訂。（3） 開臺create or replace procedure 修改菜單 (in_Menuno in %type, in_Dishname in %type) isbegin delete from menus_dish where Menuno=in_Menuno and Dishname= in_Dishname。end開臺。（4）修改菜單create or replace procedure 修改菜單 (in_Menuno in %type, in_Dishname in %type) isbegin delete from menus_dish where Menuno=in_Menuno and Dishname= in_Dishname。end 。（5）消費總額create or replace procedure 消費總額 (in_customerno in %type, out_Saleroom out number ,out_costomername out %type)isbegin select SUM(Dishprice*Amount) into out_Saleroom from menus_dish,Dish,Menus where Customerno=in_customerno and = and = 。 select Customername into out_costomername from Customer where Customerno =in_customerno。end 。（6）計算營業額create or replace procedure 計算營業額 (begintime in date, endtime in date) issum_of_expend number。begin select sum(saleroom) into sum_of_expend from sales_bill where billtime=begintime and billtime=endtime。 (39。從時間39。||begintime||39。到時間39。||endtime||39。的消費總額為39。||sum_of_expend)。end 。（7）發票信息沒寫完觸發器VIP自動升級create or replace trigger trig_Upgrade_of_VIP_level after update on Customer for each rowdeclare in_customerno varchar2(20)。 expend number。 level0 number 。begin in_customerno:=:。 expend :=:。 level0:=:。 if(expend 500) then level0 := 0。 elsif(expend = 500 and expend 1000)then level0 := 1。 elsif(expend = 1000 and expend 1800)then level0 := 2。 elsif(expend = 1800 and expend 2500)then level0 := 3。 elsif(expend = 2500 and expend 3500)then level0 := 4。 else level0 := 5。 end if。 update Customer set VIP_level = level0 where Customerno = in_customerno。end 。31 / 3