oracle实验4实验报告plsql程序设计文档格式.docx
- 文档编号:18120989
- 上传时间:2022-12-13
- 格式:DOCX
- 页数:15
- 大小:139.55KB
oracle实验4实验报告plsql程序设计文档格式.docx
《oracle实验4实验报告plsql程序设计文档格式.docx》由会员分享,可在线阅读,更多相关《oracle实验4实验报告plsql程序设计文档格式.docx(15页珍藏版)》请在冰豆网上搜索。
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】的函数及存储过程功能,注意:
先创立XXpackage,XX创立成功后,再创立包体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),
Cqtynumber,
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号散台'
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=:
ENDtri_CList;
(2)2.1.2
CREATEORREPLACETRIGGERtri_upCListBEFOREUPDATEONCListFOREACHROW
SELECTMname,Mprice,McostINTO:
new.Mcost
:
new.Cmoney:
=NVL(:
new.Cqty,:
old.Cqty)*NVL(:
old.Mprice);
UPDATECSETSmoney=Smoney-:
old.Cmoney+:
new.Cmoney,
SPsum=SPsum-:
old.Cqty*(:
old.Mprice-:
old.Mcost)+:
new.Mcost)
WHEREC.Cid=:
old.Cid;
ENDtri_upCList;
(3)2.1.3
CREATEORREPLACETRIGGERtri_delCList
BEFOREDELETEONCListFOREACHROW
UPDATECSETC.Smoney=C.Smoney-:
old.Cmoney,
C.SPsum=C.SPsum-(:
old.Mcost))
ENDtri_delCList;
(4)2.1.4
CREATEORREPLACETRIGGERtri_all
BEFOREINSERTORUPDATEORDELETE
ONCList
FOREACHROW
t_SmoneyC.Smoney%type;
t_SPsumC.SPsum%type;
IFINSERTINGTHEN
SelectMname,Mprice,Mcost,:
new.Cqty*Mprice
into:
fromMlist
whereMlist.Mid=:
Selectnvl(sum(Cqty*Mprice),0),nvl(sum(Cqty*(Mprice-Mcost)),0)
intot_Smoney,t_SPsum
fromClist
whereClist.Cid=:
updateC
setSmoney=t_Smoney+:
ELSIFUPDATINGTHEN
selectMname,Mprice,Mcost
=nvl(:
old.Cqty)*nvl(:
UpdateCsetSmoney=Smoney-:
ELSE
old.Cmoney,C.SPsum=C.SPsum-(:
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;
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,amechar,
consoutclist.mcost%TYPE)
selectnvl(sum(mprice-mcost),0)intoconsfromclist
wheremidin
(selectmidfrommlistwheremkidin
(selectmkidfrommkwheremkname=ame))
and
cidin
(selectcidfromCwhereto_char(endtime,'
Thedatadoesn’’texists!
ENDpGetKindSum;
(3)2.2.3
cons1C.spsum%TYPE;
cons2clist.mcost%TYPE;
cons1:
=fGetDTSum('
2013'
'
pGetKindSum('
cons2);
cons1'
||cons1||'
cons2'
||cons2||'
END;
3.3.5完成【2.3程序包设计】
(1)2.3.1
CREATEORREPLACEPACKAGEpkSUM
FUNCTIONfGetDTSum
RETURNC.spsum%TYPE;
PROCEDUREpGetKindSum(
consoutclist.mcost%TYPE);
ENDpkSUM;
CREATEORREPLACEPACKAGEBODYpkSUM
Cyearchar,amechar,
consoutclist.mcost%TYPE)
Thedatadoesn’’texi
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 实验 报告 plsql 程序设计