【正文】 end。C39。M39。MB39。||CtmNo,OrderTime+1/24)。||MoWNo,OrderTime,39。 CREATE OR REPLACE PROCEDURE PROC_INSERT_MoW( MoWNo String, OrderTime date, CtmNo String) is begin insert into MealsOnWheels(MoWNo,MoWOrderTime,CtmNo,ExpectTime) values(39。||CtmNo)。||OrderNo,OrderTime+1/24,0,39。||OrderNo,39。 insert into LodgerBill(LodgerBillNo,OrderNo,LodgerBillTime,LodgerBillTotal,CtmNo) values(39。C39。T39。O39。 END。 begin select RPhone,RoomNo,RoomType,RoomPrice into phone,room,rtype,price from Reservation where RName=DName。 rtype varchar2(20)。 CREATE OR REPLACE PROCEDURE PROC_INSERT_Unsubscribe( DNo in String, DName in String ) is phone varchar2(40)。 CREATE OR REPLACE PROCEDURE PROC_INSERT_Reservation( RNo in String, RName in String, RPhone in String, RoomNo in String, RoomType in String, RoomPrice in number ) AS begin insert into Reservation(RNo,RName,RPhone,RoomNo,RoomType,RoomPrice) Values(RNo,RName,RPhone,RoomNo,RoomType,RoomPrice)。 mit。C39。CO39。 mit。C39。 create table OrderDetail (OrderNo varchar2(20) not null, FoodNo varchar2(20) not null, FoodNum number)。 create table LodgerBill (LodgerBillNo varchar2(20) not null, OrderNo varchar2(20) not null, LodgerBillTime date, LodgerBillTotal number, CtmNo varchar2(20) not null, FOREIGN KEY (CtmNo) REFERENCES Lodger(CtmNo))。 create table MoWBill (MoWBillNo varchar2(20) not null primary key, MoWNo varchar2(20) not null, MoWBillTime date, MoWBillTotal number, CtmNo varchar2(20) not null, 13 級軟工專業(yè) 3 班數(shù)據(jù)庫應(yīng)用系統(tǒng)課程設(shè)計(jì)課程論文 31 FOREIGN KEY (CtmNo) REFERENCES Lodger(CtmNo))。 create table CleanOrder (CleanOrderNo varchar2(20) not null, RoomNo varchar2(20) not null, FOREIGN KEY (RoomNo) REFERENCES Room(RoomNo))。 create table Unsubscribe (DNo varchar2(20) not null primary key, DName varchar2(40) not null, DPhone varchar2(40), RoomNo varchar2(20) not null, RoomType varchar2(20), RoomPrice number, FOREIGN KEY (RoomNo) REFERENCES Room(RoomNo))。 create table Lodger (CtmNo varchar2(20) not null primary key, Name varchar2(40) not null, IDNo varchar2(40) not null, Age number, DiningCost number, MoWCost number, Days number, RoomNo varchar2(20), FOREIGN KEY (IDNo) REFERENCES Customer(IDNo), FOREIGN KEY (RoomNo) REFERENCES Room(RoomNo), check (Age=18))。+CtmNo) GO CREATE PROCEDURE PROC_INSERT_Reservation RNo varchar(10), RName varchar(20), RPhone varchar(20), 13 級軟工專業(yè) 3 班數(shù)據(jù)庫應(yīng)用系統(tǒng)課程設(shè)計(jì)課程論文 25 RoomNo varchar(10), RoomType varchar(10), RoomPrice float AS insert Reservation(RNo,RName,RPhone,RoomNo,RoomType,RoomPrice) Values(RNo,RName,RPhone,RoomNo,RoomType,RoomPrice) GO CREATE PROCEDURE PROC_INSERT_Unsubscribe DNo varchar(10), DName varchar(20) as declare RPhone varchar(20), RoomNo varchar(10), RoomType varchar(10), RoomPrice float select RPhone=RPhone,RoomNo=RoomNo,RoomType=RoomType,RoomPrice=RoomPrice from Reservation where RName=DName insert Unsubscribe(DNo,DName,DPhone,RoomNo,RoomType,RoomPrice) Values(DNo,DName,RPhone,RoomNo,RoomType,RoomPrice) GO CREATE PROCEDURE PROC_INSERT_OrderDetail orderno varchar(10), foodno varchar(10), foodnum int AS insert OrderDetail(OrderNo,FoodNo,FoodNum) values (orderno,foodno,foodnum) GO 附錄 4 create trigger OrderDetailInsert on OrderDetail 13 級軟工專業(yè) 3 班數(shù)據(jù)庫應(yīng)用系統(tǒng)課程設(shè)計(jì)課程論文 26 for insert,update,delete as declare orderno varchar(10), foodno varchar(10), foodnum int declare foodprice float, total1 float, temp1 float declare c1 cursor for select OrderNo,FoodNo,FoodNum from OrderDetail open c1 update LodgerBill set LodgerBillTotal=0 fetch next from c1 into orderno,foodno,foodnum while(FETCH_STATUS=0) begin select foodprice=FoodPrice from Food where FoodNo=foodno select total1=LodgerBillTotal from LodgerBill where OrderNo=orderno set temp1=foodprice*foodnum set total1=total1+temp1 update LodgerBill set LodgerBillTotal=total1 where OrderNo=orderno fetch next from c1 into orderno,foodno,foodnum end close c1 deallocate c1 go create trigger OrderDetailInsert_2 on OrderDetail for insert,update,delete as declare mowno varchar(10), foodno varchar(10), foodnum int declare foodprice float, total2 float, temp2 float declare c2 cursor for select OrderNo,FoodNo,FoodNum from OrderDetail open c2 13 級軟工專業(yè) 3 班數(shù)據(jù)庫應(yīng)用系統(tǒng)課程設(shè)計(jì)課程論文 27 update MoWBill set MoWBillTotal=0 fetch next from c2 into mowno,foodno,foodnum while(FETCH_STATUS=0) begin select foodprice=FoodPrice from Food where FoodNo=foodno select total2=MoWBillTotal from MoWBill where MoWNo=mowno set temp2=foodprice*foodnum set total2=total2+temp2 update MoWBill set MoWBillTotal=total2 where MoWNo=mowno fetch next from c2 into mowno,foodno,foodnum end close c2 deallocate c2 go create trigger CheckBill1 on LodgerBill for update as declare total float, ctmno varchar(10) declare c3 cursor for select LodgerBillTotal,CtmNo from LodgerBill open c3 fetch next from c3 into total,ctmno while(FETCH_STATUS=0) begin update Lodger set DiningCost=total where CtmNo=ctmno fetch next from c3 into total,ctmno end 13 級軟工專業(yè) 3 班數(shù)據(jù)庫應(yīng)用系統(tǒng)課程設(shè)計(jì)課程論文 28 close c3 deallocate c3 go create trigger CheckBill2 on MoWBill for update as declare total float, ctmno varchar(10) declare c4 cursor for select MoWBillTotal,CtmNo from MoWBill open c4 fetch next from c4 into total,ctmno while(FETCH_STATUS=0) begin update Lodger set MoWCost=total where CtmNo=ctmno fetch next from c4 into total,ctmno end close c4 deallocate c4 go create trigger LodgerCheckOut on Lodg