Oracle实验4实验报告PLSQL程序设计.docx
《Oracle实验4实验报告PLSQL程序设计.docx》由会员分享，可在线阅读，更多相关《Oracle实验4实验报告PLSQL程序设计.docx（16页珍藏版）》请在冰豆网上搜索。
Oracle实验4实验报告PLSQL程序设计
学期
Oracle数据库应用技术
实验报告
选课序号：
班级：
学号：
姓名：
指导教师：
史金余
成绩：
2017年月日
PL/SQL程序设计
1.实验目的
◆掌握PL/SQL程序设计基本技巧，包括基本数据类型、表类型、数组类型、匿名程序块、控制语句、PL/SQL中使用SQL语句、游标、错误处理等。
◆熟悉和掌握PL/SQL中关于存储过程、函数、包和触发器程序设计技术。
2.实验内容
实验平台：
PL/SQLDeveloper或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（YEARFROM日期字段），本题：
统计年份=EXTRACT（YEARFROMEndTime），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，包头创建成功后，再创建包体packagebody。
2.3.2设计一个匿名PL/SQL程序块，参照【2.2.3】调用【2.3.1】中程序包的函数和存储过程，输出2013年餐台类别名为“散台”的成本金额合计，输出2013年菜肴类别名为“蔬菜类”的盈利金额合计。
3.实验步骤
（备注：
如果用实验室微机，请从【3.2】开始做，登录用户DINER改为stuXX）
3.1创建表空间RESTAURANT,创建用户DINER
3.1.2用户SYSTEM登录Oracle
3.1.3创建表空间RESTAURANT，大小10M。
CREATETABLESPACERESTAURANT
DATAFILE'F:
\RESTAURANT.ora'SIZE10M
DEFAULTSTORAGE
（INITIAL10K
NEXT50K
MINEXTENTS1
MAXEXTENTS99
PCTINCREASE10）
ONLINE;
3.1.4创建用户DINER，口令XXX，默认表空间RESTAURANT，给该用户授予角色权限CONNECT、RESOURCE。
CREATEUSERDINERIDENTIFIEDBY"wzl123"DEFAULTTABLESPACERESTAURANT;
GRANTCONNECTTODINER;
GRANTRESOURCETODINER;
3.2创建餐饮系统数据库的所有表，并向各表插入演示数据
3.2.1启动PL/SQLDeveloper（或者启动SQL*PLUS、EnterpriseManagerConsole、浏览器模式的EM（企业管理器）等工具均可以），用户DINER登录Oracle。
3.2.2创建实验内容中的餐饮系统数据库的所有表（菜肴类别表MK、菜单信息表MList、餐台类别表DK、餐台信息表Dinfo、消费单主表C、消费单明细表CList）。
createtableMK（
MKidnumber,
MkNamevarchar2（64）,
constraintpk_MKidprimarykey（MKid）
）;
createtableMList（
Midnumber,
Mnamevarchar2（64）,
MKidnumberreferencesMK（MKid）,
Mpricenumber（8,2）,
Mcostnumber（8,2）,
Mdatedate,
constraintpk_Midprimarykey（Mid）
）;
createtableDK（
DKidnumber,
DkNamevarchar2（64）,
constraintpk_DKidprimarykey（DKid）
）;
createtableDinfo（
Didnumber,
Dnamevarchar2（64）,
DKidnumberreferencesDK（DKid）,
Dseatsnumber,
Ddatedate,
constraintpk_Didprimarykey（Did）
）;
createtableC（
Cidnumber,
DidnumberreferencesDinfo（Did）,
StartTimedate,
EndTimedate,
Smoneynumber（8,2）,
SPsumnumber（8,2）,
constraintpk_Cidprimarykey（Cid）
）;
createtableCList（
Sidnumber,
CidnumberreferencesC（Cid）,
MidnumberreferencesMList（Mid）,
Mnamevarchar2（64）,
Cqtynumber,
Mpricenumber（8,2）,
Mcostnumber（8,2）,
Cmoneynumber（8,2）,
constraintpk_Sidprimarykey（Sid）
）;
3.2.3依次向菜肴类别表MK、菜单信息表MList、餐台类别表DK、餐台信息表Dinfo插入足够多的演示数据。
insertintoMKvalues（1,'鱼类'）;
insertintoMKvalues（2,'蔬菜类'）;
insertintoMKvalues（3,'凉菜类'）;
insertintoMKvalues（4,'肉类'）;
insertintoMKvalues（5,'主食类'）;
insertintoMKvalues（6,'酒水'）;
insertintoMListvalues（1,'鲤鱼',1,50.00,30.00,sysdate）;
insertintoMListvalues（2,'三文鱼',1,120.00,80.00,sysdate）;
insertintoMListvalues（3,'白菜',2,15.00,5.00,sysdate）;
insertintoMListvalues（4,'土豆',2,12.00,4.00,sysdate）;
insertintoMListvalues（5,'油麦菜',2,12.00,5.00,sysdate）;
insertintoMListvalues（6,'凉拌黄瓜',3,5.00,3.00,sysdate）;
insertintoMListvalues（7,'鸡肉',4,30.00,10.00,sysdate）;
insertintoMListvalues（8,'米饭',5,1.50,0.50,sysdate）;
insertintoMListvalues（9,'二锅头',6,50.00,30.00,sysdate）;
insertintoDKvalues（1,'包间'）;
insertintoDKvalues（2,'散台'）;
insertintoDinfovalues（1,'1号包间',1,20,sysdate）;
insertintoDinfovalues（2,'2号包间',1,30,sysdate）;
insertintoDinfovalues（3,'3号包间',1,50,sysdate）;
insertintoDinfovalues（4,'1号散台',2,8,sysdate）;
insertintoDinfovalues（5,'2号散台',2,8,sysdate）;
insertintoDinfovalues（6,'3号散台',2,15,sysdate）;
insertintoCvalues（1,2,sysdate,sysdate,275,150）;
insertintoCvalues（2,2,sysdate,sysdate,155,80）;
insertintoCvalues（3,1,sysdate,sysdate,566,302）;
insertintoCvalues（4,2,sysdate,sysdate,89,53）;
insertintoCvalues（5,1,sysdate,sysdate,798,435）;
insertintoCListvalues（1,1,2,'三文鱼',1,120.00,80.00,120.00）;
insertintoCListvalues（2,1,5,'油麦菜',1,12.00,5.00,12.00）;
insertintoCListvalues（3,1,9,'二锅头',2,50.00,30.00,100.00）;
3.3完成【实验内容】中的触发器、存储过程、函数和程序包等功能设计，将程序脚本保存到文本文件Source.sql中
3.3.1在PL/SQLDeveloper环境下，用户DINER登录Oracle
3.3.2新建SQL窗口
3.3.3完成【2.1触发器设计】
（1）2.1.1
CREATEORREPLACETRIGGERtri_CList
BEFOREINSERTONCListFOREACHROW
DECLARE
t_SmoneyC.Smoney%type;
t_SPsumC.SPsum%type;
BEGIN
--补全Mlist
SELECTMname,Mprice,Mcost,:
new.Cqty*MpriceINTO:
new.Mname,:
new.Mprice,:
new.Mcost,:
new.Cmoney
FROMMlistWHEREMlist.Mid=:
new.Mid;
--计算Mlist的增加量
SELECTNVL（SUM（Cqty*Mprice）,0）,NVL（SUM（Cqty*（Mprice-Mcost））,0）
INTOv_Smoney,v_SPsumFROMClistWHEREClist.Cid=:
new.Cid;
--更新C
updateCsetSmoney=t_Smoney+:
new.Cmoney,SPsum=t_SPsum+（:
new.Cqty*（:
new.Mprice-:
new.Mcost））
whereC.Cid=:
new.Cid;
ENDtri_CList;
（2）2.1.2
CREATEORREPLACETRIGGERtri_upCListBEFOREUPDATEONCListFOREACHROW
BEGIN
SELECTMname,Mprice,McostINTO:
new.Mname,:
new.Mprice,:
new.Mcost
FROMMlistWHEREMlist.Mid=:
new.Mid;
:
new.Cmoney:
=NVL（:
new.Cqty,:
old.Cqty）*NVL（:
new.Mprice,:
old.Mprice）;
UPDATECSETSmoney=Smoney-:
old.Cmoney+:
new.Cmoney,
SPsum=SPsum-:
old.Cqty*（:
old.Mprice-:
old.Mcost）+:
new.Cqty*（:
new.Mprice-:
new.Mcost）
WHEREC.Cid=:
old.Cid;
ENDtri_upCList;
（3）2.1.3
CREATEORREPLACETRIGGERtri_delCList
BEFOREDELETEONCListFOREACHROW
BEGIN
UPDATECSETC.Smoney=C.Smoney-:
old.Cmoney,
C.SPsum=C.SPsum-（:
old.Cqty*（:
old.Mprice-:
old.Mcost））
WHEREC.Cid=:
old.Cid;
ENDtri_delCList;
（4）2.1.4
CREATEORREPLACETRIGGERtri_all
BEFOREINSERTORUPDATEORDELETE
ONCList
FOREACHROW
DECLARE
t_SmoneyC.Smoney%type;
t_SPsumC.SPsum%type;
BEGIN
IFINSERTINGTHEN
SelectMname,Mprice,Mcost,:
new.Cqty*Mprice
into:
new.Mname,:
new.Mprice,:
new.Mcost,:
new.Cmoney
fromMlist
whereMlist.Mid=:
new.Mid;
Selectnvl（sum（Cqty*Mprice）,0）,nvl（sum（Cqty*（Mprice-Mcost））,0）
intot_Smoney,t_SPsum
fromClist
whereClist.Cid=:
new.Cid;
updateC
setSmoney=t_Smoney+:
new.Cmoney,SPsum=t_SPsum+（:
new.Cqty*（:
new.Mprice-:
new.Mcost））
whereC.Cid=:
new.Cid;
ELSIFUPDATINGTHEN
selectMname,Mprice,Mcost
into:
new.Mname,:
new.Mprice,:
new.Mcost
fromMlist
whereMlist.Mid=:
new.Mid;
:
new.Cmoney:
=nvl（:
new.Cqty,:
old.Cqty）*nvl（:
new.Mprice,:
old.Mprice）;
UpdateCsetSmoney=Smoney-:
old.Cmoney+:
new.Cmoney,
SPsum=SPsum-:
old.Cqty*（:
old.Mprice-:
old.Mcost）+:
new.Cqty*（:
new.Mprice-:
new.Mcost）
whereC.Cid=:
old.Cid;
ELSE
UPDATECSETC.Smoney=C.Smoney-:
old.Cmoney,C.SPsum=C.SPsum-（:
old.Cqty*（:
old.Mprice-:
old.Mcost））;
ENDIF;
ENDtri_all;
3.3.4完成【2.2存储过程、自定义函数设计】
（1）2.2.1
CREATEORREPLACEFUNCTIONfGetDTSum
（Cyearchar,Ctabledk.dkname%type）
RETURNC.spsum%TYPE
AS
consC.spsum%TYPE;
BEGIN
selectnvl（sum（spsum）,0）intoconsfromC
wheredidin（
selectdidfromdinfowheredkidin（
selectdkidfromdkwheredkname=Ctable））
and（to_char（endtime,'YYYY'）=Cyear）;
RETURNcons;
EXCEPTION
WHENNO_DATA_FOUNDTHEN
DBMS_OUTPUT.PUT_LINE（'Thedataisinvalid!
'）;
ENDfGetDTSum;
（2）2.2.2
CREATEORREPLACEPROCEDUREpGetKindSum（
Cyearchar,Cnamechar,
consoutclist.mcost%TYPE）
AS
BEGIN
selectnvl（sum（mprice-mcost）,0）intoconsfromclist
wheremidin
（selectmidfrommlistwheremkidin
（selectmkidfrommkwheremkname=Cname））
and
cidin
（selectcidfromCwhereto_char（endtime,'YYYY'）=Cyear）;
EXCEPTION
WHENNO_DATA_FOUNDTHEN
DBMS_OUTPUT.PUT_LINE（'Thedatadoesn’’texists!
'）;
ENDpGetKindSum;
（3）2.2.3
DECLARE
cons1C.spsum%TYPE;
cons2clist.mcost%TYPE;
BEGIN
cons1:
=fGetDTSum（'2013','包间'）;
pGetKindSum（'2013','鱼类',cons2）;
DBMS_OUTPUT.PUT_LINE（'cons1'||cons1||'cons2'||cons2||''）;
END;
3.3.5完成【2.3程序包设计】
（1）2.3.1
CREATEORREPLACEPACKAGEpkSUM
AS
FUNCTIONfGetDTSum
（Cyearchar,Ctabledk.dkname%type）
RETURNC.spsum%TYPE;
PROCEDUREpGetKindSum（
Cyearchar,Cnamechar,
consoutclist.mcost%TYPE）;
ENDpkSUM;
CREATEORREPLACEPACKAGEBODYpkSUM
AS
CREATEORREPLACEFUNCTIONfGetDTSum
（Cyearchar,Ctabledk.dkname%type）
RETURNC.spsum%TYPE
AS
consC.spsum%TYPE;
BEGIN
selectnvl（sum（spsum）,0）intoconsfromC
wheredidin（
selectdidfromdinfowheredkidin（
selectdkidfromdkwheredkname=Ctable））
and（to_char（endtime,'YYYY'）=Cyear）;
RETURNcons;
EXCEPTION
WHENNO_DATA_FOUNDTHEN
DBMS_OUTPUT.PUT_LINE（'Thedataisinvalid!
'）;
ENDfGetDTSum;
CREATEORREPLACEPROCEDUREpGetKindSum
升级会员 