学期Oracle数据库应用技术实验报告选课序号：班级：学号：姓名：指导教师：***成绩：2017年月日目录1.实验目的 (1)2.实验内容 (1)2.1 触发器设计 (2)2.2 存储过程、自定义函数设计 (2)2.3 程序包设计 (3)3.实验步骤 (3)3.1 创建表空间RESTAURANT,创建用户DINER (3)3.2 创建餐饮系统数据库的所有表，并向各表插入演示数据 (4)3.3 完成【实验内容】中的触发器、存储过程、函数和程序包等功能设计，将程序脚本保存到文本文件Source.sql中 (7)4.实验总结 (13)PL/SQL程序设计1.实验目的◆掌握PL/SQL程序设计基本技巧，包括基本数据类型、表类型、数组类型、匿名程序块、控制语句、PL/SQL中使用SQL语句、游标、错误处理等。

◆熟悉和掌握PL/SQL中关于存储过程、函数、包和触发器程序设计技术。

2.实验内容实验平台：PL/SQL Developer或Oracle的其它客户端管理工具。

某餐饮系统数据库（加粗字段为主键，斜体字段为外键），请创建如下各数据表，并实现如下存储过程、函数、包和触发器等功能设计，将程序脚本保存到文本文件Source.sql中：（1）菜肴类别表MK（菜肴类别编号MKid，菜肴类别名称MkName）,菜肴类别名称：鱼类、蔬菜类、凉菜类、肉类、主食类和酒水类等。

（2）菜单信息表MList（菜肴编号Mid，菜肴名称Mname，菜肴类别MKid，菜肴单价Mprice，菜肴成本单价Mcost，更新日期Mdate)。

（3）餐台类别表DK（餐台类别编号DKid，餐台类别名称DkName），餐台类别：包间和散台等。

（4）餐台信息表Dinfo (餐台编号Did，餐台名称Dname，餐台类别DKid，座位数Dseats，更新日期Ddate)。

（5）消费单主表C (消费单号Cid，餐台编号Did，消费开始时间StartTime，结账时间EndTime，消费金额合计Smoney，盈利金额合计SPsum)，其中，消费金额合计=消费单明细表CList中该消费单号的所有消费记录的消费金额的合计，即SUM（消费金额）或SUM（菜肴单价×消费数量），盈利金额合计=消费单明细表CList中该消费单号的所有消费记录的盈利合计，即SUM（(菜肴单价- 菜肴成本单价)×消费数量）。

（6）消费单明细表CList (消费单号Cid，序号Sid，菜肴编号Mid，菜肴名称Mname，消费数量Cqty，菜肴单价Mprice，菜肴成本单价Mcost，消费金额Cmoney) ，消费金额=消费数量×菜肴单价；消费数量为正数是正常点菜，消费数量为负数是退菜，消费数量为0是赠菜。

2.1 触发器设计2.1.1 为消费单明细表CList定义一个触发器，每插入（INSERT）一条消费单明细记录（消费单号，序号，菜肴编号，消费数量），自动根据菜肴编号从菜单信息表MList中读取菜肴名称Mname、菜肴单价Mprice、菜肴成本单价Mcost，然后计算其消费金额（=消费数量×菜肴单价）、以及消费单主表C的消费金额合计、盈利金额合计。

编写相应的插入语句（INSERT）和查询语句（SELECT）测试该触发器效果。

2.1.2 为消费单明细表CList定义一个触发器，每更新UPDATE一条消费单明细表记录，自动修改其消费金额、以及消费单主表C的消费金额合计、盈利金额合计。

编写相应的更新语句（UPDATE）和查询语句（SELECT）测试该触发器效果。

2.1.3 为消费单明细表CList定义一个触发器，每删除DELETE一条消费单明细表记录自动修改其消费单主表C的消费金额合计、盈利金额合计。

编写相应的删除语句（DELETE）和查询语句（SELECT）测试该触发器效果。

2.1.4 将【2.1.1】、【2.1.2】、【2.1.3】三个触发器禁用disable，重新编写一个触发器实现这三个触发器的全部功能。

编写相应的插入语句（INSERT）、更新语句（UPDATE）、删除语句（DELETE）和查询语句（SELECT）测试该触发器效果。

2.2 存储过程、自定义函数设计2.2.1设计一个自定义函数fGetDTSum，实现统计某年份给定餐台类别的成本金额合计的功能，输入参数是统计年份和餐台类别，返回数据是成本金额合计。

成本金额=消费数量×菜肴成本单价。

求年份的函数为EXTRACT(YEAR FROM 日期字段)，本题：统计年份= EXTRACT(YEAR FROM EndTime)，EndTime为结账时间字段。

2.2.2设计一个存储过程pGetKindSum，实现统计某年份给定菜肴类别的盈利金额合计的功能，输入参数是统计年份和菜肴类别，输出参数是盈利金额合计。

盈利金额=消费数量× (菜肴单价- 菜肴成本单价)。

2.2.3编写一段匿名PL/SQL程序块，调用函数fGetDTSum，输出2013年餐台类别名为“包间”的成本金额合计；调用存储过程pGetKindSum，输出2013年菜肴类别名为“鱼类”的盈利金额合计。

2.3 程序包设计2.3.1设计一个程序包，包名为pkSUM，包括并实现【2.2.1】和【2.2.2】的函数及存储过程功能，注意：先创建包头package，包头创建成功后，再创建包体package body。

2.3.2 设计一个匿名PL/SQL程序块，参照【2.2.3】调用【2.3.1】中程序包的函数和存储过程，输出2013年餐台类别名为“散台”的成本金额合计，输出2013年菜肴类别名为“蔬菜类”的盈利金额合计。

3.实验步骤（备注：如果用实验室微机，请从【3.2】开始做，登录用户DINER改为stu XX）3.1 创建表空间RESTAURANT,创建用户DINER3.1.2 用户SYSTEM登录Oracle3.1.3创建表空间RESTAURANT，大小10M。

CREATE TABLESPACE RESTAURANTDATAFILE'F:\RESTAURANT.ora'SIZE10MDEFAULT STORAGE(INITIAL10KNEXT50KMINEXTENTS1MAXEXTENTS99PCTINCREASE10)ONLINE;3.1.4 创建用户DINER，口令XXX，默认表空间RESTAURANT，给该用户授予角色权限CONNECT、RESOURCE。

CREATE USER DINER IDENTIFIED BY "wzl123" 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));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,'酒水');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);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登录Oracle3.3.2新建SQL窗口3.3.3 完成【2.1 触发器设计】(1)2.1.1CREATE OR REPLACE TRIGGER tri_CListBEFORE INSERT ON CList FOR EACH ROWDECLAREt_Smoney C.Smoney%type;t_SPsum C.SPsum%type;BEGIN--补全MlistSELECT Mname,Mprice,Mcost,:new.Cqty*MpriceINTO:new.Mname,:new.Mprice,:new.Mcost,:new.CmoneyFROM Mlist WHERE Mlist.Mid=:new.Mid;--计算Mlist的增加量SELECT NVL(SUM(Cqty*Mprice),0),NVL(SUM(Cqty*(Mprice-Mcost)),0)INTO v_Smoney,v_SPsum FROM Clist WHERE Clist.Cid=:new.Cid;--更新Cupdate 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.2CREATE OR REPLACE TRIGGER tri_upCList BEFORE UPDATE ON CList FOR EACH ROWBEGINSELECT Mname,Mprice,Mcost INTO:new.Mname,:new.Mprice,:new.McostFROM 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.3CREATE OR REPLACE TRIGGER tri_delCListBEFORE DELETE ON CList FOR EACH ROWBEGINUPDATE C SET C.Smoney=C.Smoney-:old.Cmoney,C.SPsum=C.SPsum-(:old.Cqty*(:old.Mprice-:old.Mcost))WHERE C.Cid=:old.Cid;END tri_delCList;(4)2.1.4CREATE OR REPLACE TRIGGER tri_allBEFORE INSERT OR UPDATE OR DELETEON CListFOR EACH ROWDECLAREt_Smoney C.Smoney%type;t_SPsum C.SPsum%type;BEGINIF INSERTING THENSelect Mname,Mprice,Mcost,:new.Cqty*Mpriceinto:new.Mname,:new.Mprice,:new.Mcost,:new.Cmoneyfrom Mlistwhere Mlist.Mid=:new.Mid;Select nvl(sum(Cqty*Mprice),0),nvl(sum(Cqty*(Mprice-Mcost)),0)into t_Smoney,t_SPsumfrom Clistwhere Clist.Cid=:new.Cid;update Cset Smoney=t_Smoney+:new.Cmoney,SPsum=t_SPsum+(:new.Cqty*(:new.Mprice-:new.Mcost)) where C.Cid=:new.Cid;ELSIF UPDATING THENselect Mname,Mprice,Mcostinto:new.Mname,:new.Mprice,:new.Mcostfrom Mlistwhere 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;ELSEUPDATE C SETC.Smoney=C.Smoney-:old.Cmoney,C.SPsum=C.SPsum-(:old.Cqty*(:old.Mprice-:old.Mcost));END IF;END tri_all;3.3.4 完成【2.2 存储过程、自定义函数设计】(1)2.2.1CREATE OR REPLACE FUNCTION fGetDTSum(Cyear char,Ctable dk.dkname%type)RETURN C.spsum%TYPEAScons C.spsum%TYPE;BEGINselect nvl(sum(spsum),0)into cons from Cwhere did in(select did from dinfo where dkid in(select dkid from dk where dkname=Ctable))and(to_char(endtime,'YYYY')=Cyear);RETURN cons;EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('The data is invalid!');END fGetDTSum;(2)2.2.2CREATE OR REPLACE PROCEDURE pGetKindSum(Cyear char,Cname char,cons out clist.mcost%TYPE)ASBEGINselect nvl(sum(mprice-mcost),0)into cons from clist where mid in(select mid from mlist where mkid in(select mkid from mk where mkname=Cname))andcid in(select cid from C where to_char(endtime,'YYYY')=Cyear); EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('The data doesn’’t exists!'); END pGetKindSum;(3)2.2.3DECLAREcons1 C.spsum%TYPE;cons2 clist.mcost%TYPE;BEGINcons1:=fGetDTSum('2013','包间');pGetKindSum('2013','鱼类',cons2);DBMS_OUTPUT.PUT_LINE('cons1 '||cons1||'cons2 '||cons2||''); END;3.3.5完成【2.3 程序包设计】(1)2.3.1CREATE OR REPLACE PACKAGE pkSUMASFUNCTION fGetDTSum(Cyear char,Ctable dk.dkname%type)RETURN C.spsum%TYPE;PROCEDURE pGetKindSum(Cyear char,Cname char,cons out clist.mcost%TYPE);END pkSUM;CREATE OR REPLACE PACKAGE BODY pkSUMASCREATE OR REPLACE FUNCTION fGetDTSum(Cyear char,Ctable dk.dkname%type)RETURN C.spsum%TYPEAScons C.spsum%TYPE;BEGINselect nvl(sum(spsum),0)into cons from Cwhere did in(select did from dinfo where dkid in(select dkid from dk where dkname=Ctable))and(to_char(endtime,'YYYY')=Cyear);RETURN cons;EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('The data is invalid!');END fGetDTSum;CREATE OR REPLACE PROCEDURE pGetKindSum(Cyear char,Cname char,cons out clist.mcost%TYPE)ASBEGINselect nvl(sum(mprice-mcost),0)into cons from clistwhere mid in(select mid from mlist where mkid in(select mkid from mk where mkname=Cname))andcid in(select cid from C where to_char(endtime,'YYYY')=Cyear);EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('The data doesn’’t exists!');END pGetKindSum;END pkSUM;CREATE OR REPLACE PACKAGE BODY pkSUMASCREATE OR REPLACE FUNCTION fGetDTSum(Cyear char,Ctable dk.dkname%type)RETURN C.spsum%TYPEAScons C.spsum%TYPE;BEGINselect nvl(sum(spsum),0)into cons from Cwhere did in(select did from dinfo where dkid in(select dkid from dk where dkname=Ctable))and(to_char(endtime,'YYYY')=Cyear);RETURN cons;EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('The data is invalid!');END fGetDTSum;CREATE OR REPLACE PROCEDURE pGetKindSum(Cyear char,Cname char,cons out clist.mcost%TYPE)ASBEGINselect nvl(sum(mprice-mcost),0)into cons from clistwhere mid in(select mid from mlist where mkid in(select mkid from mk where mkname=Cname))andcid in(select cid from C where to_char(endtime,'YYYY')=Cyear);EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('The data doesn’’t exists!');END pGetKindSum;END pkSUM;(2)2.3.2DECLAREcons1 C.spsum%TYPE;cons2 clist.mcost%TYPE;BEGINcons1:=pkSUM.fGetDTSum1('2013','散台');pkSUM.pGetKindSum1('2013','蔬菜类',cons2);DBMS_OUTPUT.PUT_LINE('cons1:'||cons1||' cons2:'||cons2||'');END;4.实验总结这次实验让我学会了许多东西，比如PLSQL Developer的使用，高级PL/SQL 程序的编写，其中包括触发器，储存过程，自定义函数，匿名函数，程序包的设计与编写。