oracle实验4实验报告-pl_sql程序设计
《oracle实验4实验报告-pl_sql程序设计》由会员分享,可在线阅读,更多相关《oracle实验4实验报告-pl_sql程序设计(12页珍藏版)》请在装配图网上搜索。
1、-学期Oracle数据库应用技术实验报告选课序号:班 级:学 号:姓 名:指导教师: 史金余成 绩:2017年月日. z.-目 录1.实验目的12.实验内容12.1 触发器设计22.2 存储过程、自定义函数设计22.3 程序包设计33.实验步骤33.1 创立表空间RESTAURANT,创立用户DINER33.2 创立餐饮系统数据库的所有表,并向各表插入演示数据43.3 完成【实验内容】中的触发器、存储过程、函数和程序包等功能设计,将程序脚本保存到文本文件Source.sql中74.实验总结13. z.-PL/SQL程序设计1. 实验目的u 掌握PL/SQL程序设计根本技巧,包括根本数据类型、表
2、类型、数组类型、匿名程序块、控制语句、PL/SQL中使用SQL语句、游标、错误处理等。u 熟悉和掌握PL/SQL中关于存储过程、函数、包和触发器程序设计技术。2. 实验内容实验平台:PL/SQL Developer或Oracle的其它客户端管理工具。*餐饮系统数据库加粗字段为主键,斜体字段为外键,请创立如下各数据表,并实现如下存储过程、函数、包和触发器等功能设计,将程序脚本保存到文本文件Source.sql中:(1) 菜肴类别表MK菜肴类别编号MKid,菜肴类别名称MkName,菜肴类别名称:鱼类、蔬菜类、凉菜类、肉类、主食类和酒水类等。(2) 菜单信息表MList菜肴编号Mid,菜肴名称Mn
3、ame,菜肴类别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菜肴单价
4、消费数量,盈利金额合计=消费单明细表CList中该消费单号的所有消费记录的盈利合计,即SUM(菜肴单价 - 菜肴本钱单价) 消费数量。(6) 消费单明细表CList (消费单号Cid,序号Sid,菜肴编号Mid,菜肴名称Mname,消费数量Cqty,菜肴单价Mprice,菜肴本钱单价Mcost,消费金额Cmoney) ,消费金额=消费数量菜肴单价;消费数量为正数是正常点菜,消费数量为负数是退菜,消费数量为0是赠菜。2.1 触发器设计为消费单明细表CList定义一个触发器,每插入INSERT一条消费单明细记录消费单号,序号,菜肴编号,消费数量,自动根据菜肴编号从菜单信息表MList中读取菜肴名称
5、Mname、菜肴单价Mprice、菜肴本钱单价Mcost,然后计算其消费金额=消费数量菜肴单价、以及消费单主表C的消费金额合计、盈利金额合计。编写相应的插入语句INSERT和查询语句SELECT测试该触发器效果。为消费单明细表CList定义一个触发器,每更新UPDATE一条消费单明细表记录,自动修改其消费金额、以及消费单主表C的消费金额合计、盈利金额合计。编写相应的更新语句UPDATE和查询语句SELECT测试该触发器效果。为消费单明细表CList定义一个触发器,每删除DELETE一条消费单明细表记录自动修改其消费单主表C的消费金额合计、盈利金额合计。编写相应的删除语句DELETE和查询语句S
6、ELECT测试该触发器效果。将【2.1.1】、【2.1.2】、【2.1.3】三个触发器禁用disable,重新编写一个触发器实现这三个触发器的全部功能。编写相应的插入语句INSERT、更新语句UPDATE、删除语句DELETE和查询语句SELECT测试该触发器效果。2.2 存储过程、自定义函数设计设计一个自定义函数fGetDTSum,实现统计*年份给定餐台类别的本钱金额合计的功能,输入参数是统计年份和餐台类别,返回数据是本钱金额合计。本钱金额=消费数量菜肴本钱单价。求年份的函数为E*TRACT(YEAR FROM 日期字段),此题:统计年份= E*TRACT(YEAR FROM EndTime
7、),EndTime为结账时间字段。设计一个存储过程pGetKindSum,实现统计*年份给定菜肴类别的盈利金额合计的功能,输入参数是统计年份和菜肴类别,输出参数是盈利金额合计。盈利金额=消费数量(菜肴单价-菜肴本钱单价)。编写一段匿名PL/SQL程序块,调用函数fGetDTSum,输出2013年餐台类别名为“包间的本钱金额合计;调用存储过程pGetKindSum,输出2013年菜肴类别名为“鱼类的盈利金额合计。2.3 程序包设计设计一个程序包,包名为pkSUM,包括并实现【2.2.1】和【2.2.2】的函数及存储过程功能,注意:先创立*package,*创立成功后,再创立包体package b
8、ody。设计一个匿名PL/SQL程序块,参照【2.2.3】调用【2.3.1】中程序包的函数和存储过程,输出2013年餐台类别名为“散台的本钱金额合计,输出2013年菜肴类别名为“蔬菜类的盈利金额合计。3. 实验步骤备注:如果用实验室微机,请从【3.2】开场做,登录用户DINER改为stu*3.1 创立表空间RESTAURANT,创立用户DINER用户SYSTEM登录Oracle创立表空间RESTAURANT,大小10M。CREATETABLESPACE RESTAURANT DATAFILEF:RESTAURANT.oraSIZE10MDEFAULTSTORAGE(INITIAL10KNE*T
9、50KMINE*TENTS1MA*E*TENTS99PCTINCREASE10)ONLINE;3.1.4 创立用户DINER,口令*,默认表空间RESTAURANT,给该用户授予角色权限CONNECT、RESOURCE。CREATEUSER DINER IDENTIFIEDBY wzl123 DEFAULTTABLESPACE RESTAURANT;GRANTCONNECTTO DINER;GRANTRESOURCETO DINER;3.2创立餐饮系统数据库的所有表,并向各表插入演示数据启动PL/SQL Developer或者启动SQL*PLUS、Enterprise Manager Cons
10、ole、浏览器模式的EM企业管理器等工具均可以,用户DINER登录Oracle。创立实验内容中的餐饮系统数据库的所有表(菜肴类别表MK、菜单信息表MList、餐台类别表DK、餐台信息表Dinfo、消费单主表C、消费单明细表CList)。createtable MK( MKid number,MkName varchar2(64),constraint pk_MKid primarykey(MKid);createtable MList( Mid number, Mname varchar2(64), MKid numberreferences MK(MKid), Mprice number(8
11、,2), Mcost number(8,2), Mdate date,constraint pk_Mid primarykey(Mid);createtable DK( DKid number, DkName varchar2(64),constraint pk_DKid primarykey(DKid);createtable Dinfo( Did number, Dname varchar2(64), DKid numberreferences DK(DKid), Dseats number, Ddate date,constraint pk_Did primarykey(Did);cre
12、atetable C( Cid number, Did numberreferences Dinfo(Did), StartTime date, EndTime date, Smoney number(8,2), SPsum number(8,2),constraint pk_Cid primarykey(Cid);createtable CList(Sidnumber, Cid numberreferences C(Cid), Mid numberreferences MList(Mid), Mname varchar2(64), Cqty number, Mprice number(8,2
13、), Mcost number(8,2), Cmoney number(8,2),constraint pk_Sid primarykey(Sid);依次向菜肴类别表MK、菜单信息表MList、餐台类别表DK、餐台信息表Dinfo插入足够多的演示数据。 insertinto MK values(1,鱼类);insertinto MK values(2,蔬菜类);insertinto MK values(3,凉菜类);insertinto MK values(4,肉类);insertinto MK values(5,主食类);insertinto MK values(6,酒水);insertin
14、to MList values(1,鲤鱼,1,50.00,30.00,sysdate);insertinto MList values(2,三文鱼,1,120.00,80.00,sysdate);insertinto MList values(3,白菜,2,15.00,5.00,sysdate);insertinto MList values(4,土豆,2,12.00,4.00,sysdate);insertinto MList values(5,油麦菜,2,12.00,5.00,sysdate);insertinto MList values(6,凉拌黄瓜,3,5.00,3.00,sysda
15、te);insertinto MList values(7,鸡肉,4,30.00,10.00,sysdate);insertinto MList values(8,米饭,5,1.50,0.50,sysdate);insertinto MList values(9,二锅头,6,50.00,30.00,sysdate);insertinto DK values(1,包间);insertinto DK values(2,散台);insertinto Dinfo values(1,1号包间,1,20,sysdate);insertinto Dinfo values(2,2号包间,1,30,sysdat
16、e);insertinto Dinfo values(3,3号包间,1,50,sysdate);insertinto Dinfo values(4,1号散台,2,8,sysdate);insertinto Dinfo values(5,2号散台,2,8,sysdate);insertinto Dinfo values(6,3号散台,2,15,sysdate);insertinto C values(1,2,sysdate,sysdate,275,150);insertinto C values(2,2,sysdate,sysdate,155,80);insertinto C values(3,
17、1,sysdate,sysdate,566,302);insertinto C values(4,2,sysdate,sysdate,89,53);insertinto C values(5,1,sysdate,sysdate,798,435);insertinto CList values(1,1,2,三文鱼,1,120.00,80.00,120.00);insertinto CList values(2,1,5,油麦菜,1,12.00,5.00,12.00);insertinto CList values(3,1,9,二锅头,2,50.00,30.00,100.00);3.3完成【实验内容
18、】中的触发器、存储过程、函数和程序包等功能设计,将程序脚本保存到文本文件Source.sql中在PL/SQL Developer环境下,用户DINER登录Oracle 新建SQL窗口3.3.3完成【2.1 触发器设计】(1)CREATEORREPLACETRIGGER tri_CListBEFOREINSERTON CList FOREACHROWDECLAREt_Smoney C.Smoney%type;t_SPsum C.SPsum%type;BEGIN-补全MlistSELECT Mname,Mprice,Mcost,:new.Cqty*Mprice INTO:new.Mname,:ne
19、w.Mprice,:new.Mcost,:new.Cmoney FROM Mlist WHERE Mlist.Mid=:new.Mid;-计算Mlist的增加量SELECTNVL(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.
20、Cid=:new.Cid;END tri_CList;CREATEORREPLACETRIGGER tri_upCList BEFOREUPDATEON CList FOREACHROWBEGINSELECT 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.Cmo
21、ney+:new.Cmoney, SPsum=SPsum-:old.Cqty*(:old.Mprice-:old.Mcost)+:new.Cqty*(:new.Mprice-:new.Mcost)WHERE C.Cid=:old.Cid;END tri_upCList;CREATEORREPLACETRIGGER tri_delCListBEFOREDELETEON CList FOREACHROWBEGINUPDATE C SET C.Smoney=C.Smoney-:old.Cmoney, C.SPsum=C.SPsum-(:old.Cqty*(:old.Mprice-:old.Mcost
22、)WHERE C.Cid=:old.Cid;END tri_delCList;CREATEORREPLACETRIGGER tri_allBEFOREINSERTORUPDATEORDELETEON CListFOREACHROWDECLARE t_Smoney C.Smoney%type; t_SPsum C.SPsum%type;BEGINIF INSERTING THENSelect Mname,Mprice,Mcost,:new.Cqty*Mprice into:new.Mname,:new.Mprice,:new.Mcost,:new.Cmoney from Mlist where
23、Mlist.Mid=:new.Mid;Selectnvl(sum(Cqty*Mprice),0),nvl(sum(Cqty*(Mprice-Mcost),0)into t_Smoney,t_SPsum from Clist where Clist.Cid=:new.Cid;update C set Smoney=t_Smoney+:new.Cmoney,SPsum=t_SPsum+(:new.Cqty*(:new.Mprice-:new.Mcost)where C.Cid=:new.Cid;ELSIF UPDATING THENselect Mname,Mprice,Mcost into:ne
24、w.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;ELSEUPDATE C
25、 SET C.Smoney=C.Smoney-:old.Cmoney,C.SPsum=C.SPsum-(:old.Cqty*(:old.Mprice-:old.Mcost);ENDIF;END tri_all;完成【2.2 存储过程、自定义函数设计】CREATEORREPLACEFUNCTION fGetDTSum(Cyear char,Ctable dk.dkname%type)RETURN C.spsum%TYPEAS cons C.spsum%TYPE;BEGINselectnvl(sum(spsum),0)into cons from C where did in(select did
26、 from dinfo where dkid in(select dkid from dk where dkname=Ctable)and(to_char(endtime,YYYY)=Cyear);RETURN cons;E*CEPTIONWHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(The data is invalid!);END fGetDTSum;CREATEORREPLACEPROCEDURE pGetKindSum(Cyear char,ame char,cons out clist.mcost%TYPE)ASBEGINselectnvl
27、(sum(mprice-mcost),0)into cons from clistwhere mid in(select mid from mlist where mkid in(select mkid from mk where mkname=ame)and cid in(select cid from C where to_char(endtime,YYYY)=Cyear);E*CEPTIONWHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(The data doesnt e*ists!);END pGetKindSum;DECLARE cons1
28、C.spsum%TYPE; cons2 clist.mcost%TYPE;BEGIN cons1:=fGetDTSum(2013,包间); pGetKindSum(2013,鱼类,cons2); DBMS_OUTPUT.PUT_LINE(cons1 |cons1|cons2 |cons2|);END; 完成【2.3 程序包设计】CREATEORREPLACEPACKAGE pkSUMASFUNCTION fGetDTSum(Cyear char,Ctable dk.dkname%type)RETURN C.spsum%TYPE;PROCEDURE pGetKindSum(Cyear char,
29、ame char,cons out clist.mcost%TYPE);END pkSUM;CREATEORREPLACEPACKAGEBODY pkSUMASCREATEORREPLACEFUNCTION fGetDTSum(Cyear char,Ctable dk.dkname%type)RETURN C.spsum%TYPEAS cons C.spsum%TYPE;BEGINselectnvl(sum(spsum),0)into cons from C where did in(select did from dinfo where dkid in(select dkid from dk
30、 where dkname=Ctable)and(to_char(endtime,YYYY)=Cyear);RETURN cons;E*CEPTIONWHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(The data is invalid!);END fGetDTSum;CREATEORREPLACEPROCEDURE pGetKindSum( Cyear char,ame char, cons out clist.mcost%TYPE)ASBEGINselectnvl(sum(mprice-mcost),0)into cons from clistwh
31、ere mid in(select mid from mlist where mkid in(select mkid from mk where mkname=ame)and cid in(select cid from C where to_char(endtime,YYYY)=Cyear);E*CEPTIONWHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(The data doesnt e*ists!);END pGetKindSum;END pkSUM;CREATEORREPLACEPACKAGEBODY pkSUMASCREATEORREPLA
32、CEFUNCTION fGetDTSum(Cyear char,Ctable dk.dkname%type)RETURN C.spsum%TYPEAS cons C.spsum%TYPE;BEGINselectnvl(sum(spsum),0)into cons from C where did in(select did from dinfo where dkid in(select dkid from dk where dkname=Ctable)and(to_char(endtime,YYYY)=Cyear);RETURN cons;E*CEPTIONWHEN NO_DATA_FOUND
33、 THEN DBMS_OUTPUT.PUT_LINE(The data is invalid!);END fGetDTSum;CREATEORREPLACEPROCEDURE pGetKindSum( Cyear char,ame char, cons out clist.mcost%TYPE)ASBEGINselectnvl(sum(mprice-mcost),0)into cons from clistwhere mid in(select mid from mlist where mkid in(select mkid from mk where mkname=ame)and cid i
34、n(select cid from C where to_char(endtime,YYYY)=Cyear);E*CEPTIONWHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(The data doesnt e*ists!);END pGetKindSum;END pkSUM;DECLARE cons1 C.spsum%TYPE; cons2 clist.mcost%TYPE;BEGIN cons1:=pkSUM.fGetDTSum1(2013,散台); pkSUM.pGetKindSum1(2013,蔬菜类,cons2); DBMS_OUTPUT.PUT_LINE(cons1:|cons1| cons2:|cons2|);END;4. 实验总结 这次实验让我学会了许多东西,比方PLSQL Developer的使用,高级PL/SQL程序的编写,其中包括触发器,储存过程,自定义函数,匿名函数,程序包的设计与编写。另外,也让我又温习了一遍根底SQL语言。我也明白了一个道理,“纸上得来终觉浅,绝知此事要躬行,学好这门课最好的方法还是上级亲自实践一遍。总之,这门课令我受益匪浅。. z.
- 温馨提示:
1: 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
2: 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
3.本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
5. 装配图网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。