oracle实验4实验报告-pl_sql程序设计

---word.zl-学期Oracle数据库应用技术实验报告选课序号：班级：学号：姓名：指导教师：史金余成绩：2017年月日---word.zl-目录TOC\o"1-3"\h\z\uHYPERLINK\l"_Toc413332123"1.实验目的PAGEREF_Toc413332123\h1HYPERLINK\l"_Toc413332124"2.实验内容PAGEREF_Toc413332124\h1HYPERLINK\l"_Toc413332125"2.1触发器 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 PAGEREF_Toc413332125\h2HYPERLINK\l"_Toc413332126"2.2存储过程、自定义函数设计PAGEREF_Toc413332126\h2HYPERLINK\l"_Toc413332127"2.3程序包设计PAGEREF_Toc413332127\h3HYPERLINK\l"_Toc413332128"3.实验步骤PAGEREF_Toc413332128\h3HYPERLINK\l"_Toc413332129"3.1创立表空间RESTAURANT,创立用户DINERPAGEREF_Toc413332129\h3HYPERLINK\l"_Toc413332130"3.2创立餐饮系统数据库的所有表，并向各表插入演示数据PAGEREF_Toc413332130\h4HYPERLINK\l"_Toc413332131"3.3完成【实验内容】中的触发器、存储过程、函数和程序包等功能设计，将程序脚本保存到文本文件Source.sql中PAGEREF_Toc413332131\h7HYPERLINK\l"_Toc413332132"4.实验总结PAGEREF_Toc413332132\h13---word.zl-PL/SQL程序设计实验目的掌握PL/SQL程序设计根本技巧，包括根本数据类型、表类型、数组类型、匿名程序块、控制语句、PL/SQL中使用SQL语句、游标、错误处理等。熟悉和掌握PL/SQL中关于存储过程、函数、包和触发器程序设计技术。实验内容实验平台：PL/SQLDeveloper或Oracle的其它客户端管理工具。某餐饮系统数据库〔加粗字段为主键，斜体字段为外键〕，请创立如下各数据表，并实现如下存储过程、函数、包和触发器等功能设计，将程序脚本保存到文本文件Source.sql中：菜肴类别表MK〔菜肴类别编号MKid，菜肴类别名称MkName〕,菜肴类别名称：鱼类、蔬菜类、凉菜类、肉类、主食类和酒水类等。菜单信息表MList〔菜肴编号Mid，菜肴名称Mname，菜肴类别MKid，菜肴单价Mprice，菜肴本钱单价Mcost，更新日期Mdate)。餐台类别表DK〔餐台类别编号DKid，餐台类别名称DkName〕，餐台类别：包间和散台等。餐台信息表Dinfo(餐台编号Did，餐台名称Dname，餐台类别DKid，座位数Dseats，更新日期Ddate)。消费单主表C(消费单号Cid，餐台编号Did，消费开场时间StartTime，结账时间EndTime，消费金额合计Smoney，盈利金额合计SPsum)，其中，消费金额合计=消费单明细表CList中该消费单号的所有消费记录的消费金额的合计，即SUM〔消费金额〕或SUM〔菜肴单价×消费数量〕，盈利金额合计=消费单明细表CList中该消费单号的所有消费记录的盈利合计，即SUM〔(菜肴单价-菜肴本钱单价)×消费数量〕。消费单明细表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】的函数及存储过程功能，注意：先创立XXpackage，XX创立成功后，再创立包体packagebody。2.3.2设计一个匿名PL/SQL程序块，参照【2.2.3】调用【2.3.1】中程序包的函数和存储过程，输出2013年餐台类别名为“散台〞的本钱金额合计，输出2013年菜肴类别名为“蔬菜类〞的盈利金额合计。实验步骤〔备注：如果用实验室微机，请从【3.2】开场做，登录用户DINER改为stuXX〕3.1创立表空间RESTAURANT,创立用户DINER3.1.2用户SYSTEM登录Oracle3.1.3创立表空间RESTAURANT，大小10M。CREATETABLESPACERESTAURANTDATAFILE'F:\RESTAURANT.ora'SIZE10MDEFAULTSTORAGE(INITIAL10KNEXT50KMINEXTENTS1MAXEXTENTS99PCTINCREASE10)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登录Oracle3.3.2新建SQL窗口3.3.3完成【2.1触发器设计】(1)2.1.1CREATEORREPLACETRIGGERtri_CListBEFOREINSERTONCListFOREACHROWDECLAREt_SmoneyC.Smoney%type;t_SPsumC.SPsum%type;BEGIN--补全MlistSELECTMname,Mprice,Mcost,:new.Cqty*MpriceINTO:new.Mname,:new.Mprice,:new.Mcost,:new.CmoneyFROMMlistWHEREMlist.Mid=:new.Mid;--计算Mlist的增加量SELECTNVL(SUM(Cqty*Mprice),0),NVL(SUM(Cqty*(Mprice-Mcost)),0)INTOv_Smoney,v_SPsumFROMClistWHEREClist.Cid=:new.Cid;--更新CupdateCsetSmoney=t_Smoney+:new.Cmoney,SPsum=t_SPsum+(:new.Cqty*(:new.Mprice-:new.Mcost))whereC.Cid=:new.Cid;ENDtri_CList;(2)2.1.2CREATEORREPLACETRIGGERtri_upCListBEFOREUPDATEONCListFOREACHROWBEGINSELECTMname,Mprice,McostINTO:new.Mname,:new.Mprice,:new.McostFROMMlistWHEREMlist.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.3CREATEORREPLACETRIGGERtri_delCListBEFOREDELETEONCListFOREACHROWBEGINUPDATECSETC.Smoney=C.Smoney-:old.Cmoney,C.SPsum=C.SPsum-(:old.Cqty*(:old.Mprice-:old.Mcost))WHEREC.Cid=:old.Cid;ENDtri_delCList;(4)2.1.4CREATEORREPLACETRIGGERtri_allBEFOREINSERTORUPDATEORDELETEONCListFOREACHROWDECLAREt_SmoneyC.Smoney%type;t_SPsumC.SPsum%type;BEGINIFINSERTINGTHENSelectMname,Mprice,Mcost,:new.Cqty*Mpriceinto:new.Mname,:new.Mprice,:new.Mcost,:new.CmoneyfromMlistwhereMlist.Mid=:new.Mid;Selectnvl(sum(Cqty*Mprice),0),nvl(sum(Cqty*(Mprice-Mcost)),0)intot_Smoney,t_SPsumfromClistwhereClist.Cid=:new.Cid;updateCsetSmoney=t_Smoney+:new.Cmoney,SPsum=t_SPsum+(:new.Cqty*(:new.Mprice-:new.Mcost))whereC.Cid=:new.Cid;ELSIFUPDATINGTHENselectMname,Mprice,Mcostinto:new.Mname,:new.Mprice,:new.McostfromMlistwhereMlist.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;ELSEUPDATECSETC.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.1CREATEORREPLACEFUNCTIONfGetDTSum(Cyearchar,Ctabledk.dkname%type)RETURNC.spsum%TYPEASconsC.spsum%TYPE;BEGINselectnvl(sum(spsum),0)intoconsfromCwheredidin(selectdidfromdinfowheredkidin(selectdkidfromdkwheredkname=Ctable))and(to_char(endtime,'YYYY')=Cyear);RETURNcons;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thedataisinvalid!');ENDfGetDTSum;(2)2.2.2CREATEORREPLACEPROCEDUREpGetKindSum(Cyearchar,amechar,consoutclist.mcost%TYPE)ASBEGINselectnvl(sum(mprice-mcost),0)intoconsfromclistwheremidin(selectmidfrommlistwheremkidin(selectmkidfrommkwheremkname=ame))andcidin(selectcidfromCwhereto_char(endtime,'YYYY')=Cyear);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thedatadoesn’’texists!');ENDpGetKindSum;(3)2.2.3DECLAREcons1C.spsum%TYPE;cons2clist.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.1CREATEORREPLACEPACKAGEpkSUMASFUNCTIONfGetDTSum(Cyearchar,Ctabledk.dkname%type)RETURNC.spsum%TYPE;PROCEDUREpGetKindSum(Cyearchar,amechar,consoutclist.mcost%TYPE);ENDpkSUM;CREATEORREPLACEPACKAGEBODYpkSUMASCREATEORREPLACEFUNCTIONfGetDTSum(Cyearchar,Ctabledk.dkname%type)RETURNC.spsum%TYPEASconsC.spsum%TYPE;BEGINselectnvl(sum(spsum),0)intoconsfromCwheredidin(selectdidfromdinfowheredkidin(selectdkidfromdkwheredkname=Ctable))and(to_char(endtime,'YYYY')=Cyear);RETURNcons;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thedataisinvalid!');ENDfGetDTSum;CREATEORREPLACEPROCEDUREpGetKindSum(Cyearchar,amechar,consoutclist.mcost%TYPE)ASBEGINselectnvl(sum(mprice-mcost),0)intoconsfromclistwheremidin(selectmidfrommlistwheremkidin(selectmkidfrommkwheremkname=ame))andcidin(selectcidfromCwhereto_char(endtime,'YYYY')=Cyear);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thedatadoesn’’texists!');ENDpGetKindSum;ENDpkSUM;CREATEORREPLACEPACKAGEBODYpkSUMASCREATEORREPLACEFUNCTIONfGetDTSum(Cyearchar,Ctabledk.dkname%type)RETURNC.spsum%TYPEASconsC.spsum%TYPE;BEGINselectnvl(sum(spsum),0)intoconsfromCwheredidin(selectdidfromdinfowheredkidin(selectdkidfromdkwheredkname=Ctable))and(to_char(endtime,'YYYY')=Cyear);RETURNcons;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thedataisinvalid!');ENDfGetDTSum;CREATEORREPLACEPROCEDUREpGetKindSum(Cyearchar,amechar,consoutclist.mcost%TYPE)ASBEGINselectnvl(sum(mprice-mcost),0)intoconsfromclistwheremidin(selectmidfrommlistwheremkidin(selectmkidfrommkwheremkname=ame))andcidin(selectcidfromCwhereto_char(endtime,'YYYY')=Cyear);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thedatadoesn’’texists!');ENDpGetKindSum;ENDpkSUM;(2)2.3.2DECLAREcons1C.spsum%TYPE;cons2clist.mcost%TYPE;BEGINcons1:=pkSUM.fGetDTSum1('2013','散台');pkSUM.pGetKindSum1('2013','蔬菜类',cons2);DBMS_OUTPUT.PUT_LINE('cons1:'||cons1||'cons2:'||cons2||'');END;实验总结这次实验让我学会了许多东西，比方PLSQLDeveloper的使用，高级PL/SQL程序的编写，其中包括触发器，储存过程，自定义函数，匿名函数，程序包的设计与编写。另外，也让我又温习了一遍根底SQL语言。我也明白了一个道理，“纸上得来终觉浅，绝知此事要躬行〞，学好这门课最好的方法还是上级亲自实践一遍。总之，这门课令我受益匪浅。