Oracle 实验4（实验报告）-PL - SQL程序设计(2)

CREATE USER DINER IDENTIFIED BY \DEFAULT TABLESPACE RESTAURANT; GRANT CONNECT TO DINER; GRANT RESOURCE TO DINER;

3.2 创建餐饮系统数据库的所有表，并向各表插入演示数据

3.2.1启动PL/SQL Developer（或者启动SQL*PLUS、Enterprise Manager Console、浏览器模式的EM（企业管理器）等工具均可以），用户DINER登录Oracle。

3.2.2 创建实验内容中的餐饮系统数据库的所有表(菜肴类别表MK、菜单信息表MList、餐台类别表DK、餐台信息表Dinfo、消费单主表C、消费单明细表CList)。

create table MK(

MKid number,

MkName varchar2(64),

constraint pk_MKid primary key (MKid) );

create table MList( Mid number, Mname varchar2(64),

MKid number references MK(MKid), Mprice number(8,2), Mcost number(8,2), Mdate date,

constraint pk_Mid primary key (Mid) );

create table DK( DKid number,

DkName varchar2(64),

constraint pk_DKid primary key (DKid) );

4 / 13

create table Dinfo( Did number,

Dname varchar2(64),

DKid number references DK(DKid), Dseats number, Ddate date,

constraint pk_Did primary key (Did) );

create table C( Cid number,

Did number references Dinfo(Did), StartTime date, EndTime date, Smoney number(8,2), SPsum number(8,2),

constraint pk_Cid primary key (Cid) );

create table CList( Sid number,

Cid number references C(Cid), Mid number references MList(Mid), Mname varchar2(64), Cqty number, Mprice number(8,2), Mcost number(8,2), Cmoney number(8,2),

constraint pk_Sid primary key (Sid) );

3.2.3依次向菜肴类别表MK、菜单信息表MList、餐台类别表DK、餐台信息表Dinfo插入足够多的演示数据。

insert into MK values(1,'鱼类');

insert into MK values(2,'蔬菜类'); insert into MK values(3,'凉菜类'); insert into MK values(4,'肉类'); insert into MK values(5,'主食类'); insert into MK values(6,'酒水');

5 / 13

insert into MList values(1,'鲤鱼',1,50.00,30.00,sysdate); insert into MList values(2,'三文鱼',1,120.00,80.00,sysdate); insert into MList values(3,'白菜',2,15.00,5.00,sysdate); insert into MList values(4,'土豆',2,12.00,4.00,sysdate); insert into MList values(5,'油麦菜',2,12.00,5.00,sysdate); insert into MList values(6,'凉拌黄瓜',3,5.00,3.00,sysdate); insert into MList values(7,'鸡肉',4,30.00,10.00,sysdate); insert into MList values(8,'米饭',5,1.50,0.50,sysdate); insert into MList values(9,'二锅头',6,50.00,30.00,sysdate);

insert into DK values(1,'包间'); insert into DK values(2,'散台');

insert into Dinfo values(1,'1号包间',1,20,sysdate); insert into Dinfo values(2,'2号包间',1,30,sysdate); insert into Dinfo values(3,'3号包间',1,50,sysdate); insert into Dinfo values(4,'1号散台',2,8,sysdate); insert into Dinfo values(5,'2号散台',2,8,sysdate); insert into Dinfo values(6,'3号散台',2,15,sysdate);

6 / 13

insert into C values(1,2,sysdate,sysdate,275,150); insert into C values(2,2,sysdate,sysdate,155,80); insert into C values(3,1,sysdate,sysdate,566,302); insert into C values(4,2,sysdate,sysdate,89,53); insert into C values(5,1,sysdate,sysdate,798,435);

insert into CList values(1,1,2,'三文鱼',1,120.00,80.00,120.00); insert into CList values(2,1,5,'油麦菜',1,12.00,5.00,12.00); insert into CList values(3,1,9,'二锅头',2,50.00,30.00,100.00);

3.3 完成【实验内容】中的触发器、存储过程、函数和程序包等功能设计，将程序脚本保存到文本文件Source.sql中

3.3.1 在PL/SQL Developer环境下，用户DINER登录Oracle

3.3.2 新建SQL窗口

3.3.3 完成【2.1 触发器设计】 (1)2.1.1

CREATE OR REPLACE TRIGGER tri_CList BEFORE INSERT ON CList FOR EACH ROW DECLARE

t_Smoney C.Smoney%type; t_SPsum C.SPsum%type; BEGIN --补全Mlist

SELECT Mname,Mprice,Mcost,:new.Cqty*Mprice INTO :new.Mname,:new.Mprice,:new.Mcost,:new.Cmoney FROM Mlist WHERE Mlist.Mid=:new.Mid; --计算Mlist的增加量

SELECT NVL(SUM(Cqty*Mprice),0),NVL(SUM(Cqty*(Mprice-Mcost)),0)

7 / 13

INTO v_Smoney,v_SPsum FROM Clist WHERE Clist.Cid=:new.Cid; --更新C

update C set Smoney=t_Smoney+:new.Cmoney,SPsum=t_SPsum+(:new.Cqty*(:new.Mprice-:new.Mcost)) where C.Cid=:new.Cid; END tri_CList;

(2)2.1.2

CREATE OR REPLACE TRIGGER tri_upCList BEFORE UPDATE ON CList FOR EACH ROW BEGIN

SELECT Mname,Mprice,Mcost INTO :new.Mname,:new.Mprice,:new.Mcost FROM Mlist WHERE Mlist.Mid=:new.Mid;

:new.Cmoney:=NVL(:new.Cqty,:old.Cqty)*NVL(:new.Mprice,:old.Mprice); UPDATE C SET Smoney=Smoney-:old.Cmoney+:new.Cmoney,

SPsum=SPsum-:old.Cqty*(:old.Mprice-:old.Mcost)+:new.Cqty*(:new.Mprice-:new.Mcost) WHERE C.Cid=:old.Cid; END tri_upCList;

(3)2.1.3

CREATE OR REPLACE TRIGGER tri_delCList BEFORE DELETE ON CList FOR EACH ROW BEGIN

UPDATE C SET C.Smoney=C.Smoney-:old.Cmoney,

C.SPsum=C.SPsum-(:old.Cqty*(:old.Mprice-:old.Mcost)) WHERE C.Cid=:old.Cid;

8 / 13