Oracle-实验4

2015-2016-2学期Oracle数据库应用技术实验报告班级：13级软件工程2班学号：2220131011姓名：蒋生栋指导教师：史金余成绩：2016年5月24日选课序号：姓名：学号：班级：目录1.实验目的................................................................................................12.实验内容................................................................................................12.1触发器设计...................................................................................22.2存储过程、自定义函数设计.......................................................22.3程序包设计...................................................................................33.实验步骤................................................................................................33.1创建表空间RESTAURANT,创建用户DINER................................33.2创建餐饮系统数据库的所有表，并向各表插入演示数据......33.3完成【实验内容】中的触发器、存储过程、函数和程序包等功能设计，将程序脚本保存到文本文件Source.sql中错误!未定义书签。4.实验总结..............................................................................................11选课序号：姓名：学号：班级：1/11PL/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/112.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，实现统计某年份给定菜肴类别的盈利金额合计的功能，输入参数是统计年份和菜肴类别，输出参数是盈利金额合计。盈利金额=消费数量×(菜肴单价-菜肴成本单价)。选课序号：姓名：学号：班级：3/112.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,创建用户DINER3.1.2用户SYSTEM登录Oracle3.1.3创建表空间RESTAURANT，大小10M。--Oracle的FOREACHROW触发器,不允许在触发器里面,SELECT/UPDATE/DELETE自己这个表。--创建表空间RESTAURANT，大小10M。（用户SYSTEM登录Oracle）CREATETABLESPACERESTAURANTDATAFILE'H:\RESTAURANT.ora'SIZE10M--不能使用相对路径DEFAULTSTORAGE(INITIAL10KNEXT50KMINEXTENTS1MAXEXTENTS99PCTINCREASE10)ONLINE;3.1.4创建用户DINER，口令XXX，默认表空间RESTAURANT，给该用户授予角色权限CONNECT、RESOURCE。CREATEUSERDINERIDENTIFIEDBYm123DEFAULTTABLESPACERESTAURANT;GRANTCONNECTTODINERGRANTRESOURCETODINER3.2创建餐饮系统数据库的所有表，并向各表插入演示数据选课序号：姓名：学号：班级：4/113.2.1启动PL/SQLDeveloper（或者启动SQL*PLUS、EnterpriseManagerConsole、浏览器模式的EM（企业管理器）等工具均可以），用户DINER登录Oracle。3.2.2创建实验内容中的餐饮系统数据库的所有表(菜肴类别表MK、菜单信息表MList、餐台类别表DK、餐台信息表Dinfo、消费单主表C、消费单明细表CList)。3.2.3依次向菜肴类别表MK、菜单信息表MList、餐台类别表DK、餐台信息表Dinfo插入足够多的演示数据。--（1）菜肴类别表MK（菜肴类别编号MKid，菜肴类别名称MkName）,菜肴类别名称：鱼类、蔬菜类、凉--菜类、肉类、主食类和酒水类等。createsequenceseq_MKstartwith0incrementby1minvalue0maxvalue30000nocyclenocache;createtableMK(MKidnumber,MkNamevarchar2(64),constraintpk_MKidprimarykey(MKid));insertintoMKvalues(seq_MK.nextval,'鱼类')insertintoMKvalues(seq_MK.nextval,'蔬菜类')insertintoMKvalues(seq_MK.nextval,'凉菜类')insertintoMKvalues(seq_MK.nextval,'肉类')insertintoMKvalues(seq_MK.nextval,'主食类')insertintoMKvalues(seq_MK.nextval,'酒水')--（2）菜单信息表MList（菜肴编号Mid，菜肴名称Mname，菜肴类别MKid，菜肴单价Mprice，--菜肴成本单价Mcost，更新日期Mdate)。createsequenceseq_MListstartwith0incrementby1选课序号：姓名：学号：班级：5/11minvalue0maxvalue30000nocyclenocache;createtableMList(Midnumber,Mnamevarchar2(64),MKidnumberreferencesMK(MKid),Mpricenumber(8,2),Mcostnumber(8,2),Mdatedate,constraintpk_Midprimarykey(Mid));insertintoMListvalues(seq_MList.nextval,'比目鱼',1,40.00,20.00,sysdate)insertintoMListvalues(seq_MList.nextval,'鲶鱼',1,30.00,15.00,sysdate)insertintoMListvalues(seq_MList.nextval,'鲤鱼',1,20.00,10.00,sysdate)insertintoMListvalues(seq_MList.nextval,'番茄',2,5.50,4.00,sysdate)insertintoMListvalues(seq_MList.nextval,'生菜',2,6.00,5.00,sysdate)insertintoMListvalues(seq_MList.nextval,'大白菜',2,3.00,1.00,sysda