Oracle-实验4(20210503110530)

1、2015-2016-2 学期 Oracle数据库应用技术 实验报告 班 级：13级软件工程2班 学 号：22201310 姓 名：蒋生栋 指导教师：史金余 成 绩： 2016 年 5 月 24 H PL/SQL程序设计 1. 实验目的 掌握PUSQL程序设计基本技巧，包括基本数据类型、表类型、数组类 型、匿名程序块、控制语句、PL/SQL中使用SQL语句、游标、错误处理等。 熟悉和掌握PL/SQL中关于存储过程、函数、包和触发器程序设计技术。 2. 实验内容 实验平台：PL/SQL Developer或Oracle的其它客户端管理工具。 某餐饮系统数据库（加粗字段为主键，斜体字段为外键），请创

2、建如下各 数据表，并实现如下存储过程、函数、包和触发器等功能设计，将程序脚本保 存到文本文件Source.sql中： （1）菜肴类别表MK （菜肴类别编号MKid,菜肴类别名称MkName），菜肴 类别名称：鱼类、蔬菜类、凉菜类、肉类、主食类和酒水类等。 （2）菜单信息表MList （菜肴编号Mid,菜肴名称Mname,菜肴类别MKid, 菜肴单价Mprice,菜肴成本单价Mcost,更新日期Mdate）o （3）餐台类别表DK （餐台类别编号DKid,餐台类别名称DkName）,餐台 类别：包间和散台等。 （4）餐台信息表Dinfo （餐台编号Did,餐台名称Same,餐台类别DKid,座

3、位数Dseats,更新日期Ddate）。 （5）消费单主表C （消费单号Cid,餐台编号Did,消费开始时间StartTime, 结账时间EndTime,消费金额合计Smoney,盈利金额合计SPsum）,其中， 消费金额合计二消费单明细表CList中该消费单号的所有消费记录的消费金额 的合计，即SUM （消费金额）或SUM （菜肴单价x消费数量）,盈利金额 合计二消费单明细表CList中该消费单号的所有消费记录的盈利合i|，即SUM （菜肴单价-菜肴成本单价）x消费数量）。 （6）消费单明细表CList （消费单号Cid,序号Sid,菜肴编号Mid,菜肴名称 Mname,消费数量Cqty,菜

4、肴单价Mprice,菜肴成本单价Mcost,消费金额 Cmoney），消费金额二消费数量x菜肴单价；消费数量为止数是正常点菜，消 费数量为负数是退菜，消费数量为0是赠菜。 2.1触发器设计 2.1.1为消费单明细表CList定义一个触发器，每插入（INSERT） 一条消费单明 细记录（消费单号，序号，菜肴编号、消费数量），自动根据菜肴编号从菜 单信息表MList中读取菜肴名称Mname菜肴单价Mprice.菜肴成本单价 Mcost,然后计算其消费金额（二消费数量x菜肴单价）、以及消费单主表C 的消费金额合计、盈利金额合计。编写相应的插入语句（INSERT）和查询语 句（SELECT）测试该触发

5、器效果。 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,重新编写一个 触发器实现这三个触发器的全部功

6、能。编写相应的插入语句（INSERT）、更 新语句（UPDATE）、删除语句（DELETE）和查询语句（SELECT）测试该 触发器效果。 2.2存储过程、自定义函数设计 2.2.1设计一个自定义函数fGetDTSum,实现统计某年份给定餐台类别的成本金 额合计的功能，输入参数是统讣年份和餐台类别，返回数据是成本金额合计。 成本金额二消费数量x菜肴成本单价。求年份的函数为EXTRACT（YEAR FROM 日期字段）,本题:统计年份=EXTRACT（YEAR FROM EndTime）, EndTime为结账时间字段。 2.2.2设计一个存储过程pGetKindSum,实现统计某年份给定菜肴类

7、别的盈利金 额合计的功能，输入参数是统讣年份和菜肴类别，输出参数是盈利金额合计。 盈利金额二消费数量x（菜肴单价-菜肴成本单价）。 2.2.3编写一段匿名PL/SQL程序块，调用函数fGetDTSum,输出2013年餐台类 别名为包间”的成本金额合计；调用存储过程pGetKindSum,输出2013 年菜肴类别名为“鱼类”的盈利金额合计。 23程序包设计 23.1设计一个程序包，包名为pkSUM,包括并实现2.2.1和2.2.2的函数 及存储过程功能，注意：先创建包头package,包头创建成功后，再创建包体 package body。 2.3.2设计一个匿名PL/SQL程序块,参照【223】

8、调用2.3.1中程序包的函 数和存储过程，输出2013年餐台类别名为“散台”的成本金额合计，输出 2013年菜肴类别名为“蔬菜类”的盈利金额合计。 3. 实验步骤 （备注：如果用实验室微札 请从【3.2】开始做，登录用户DINER改为stuXX） 3. 1创建表空间RESTAURANT,创建用户DINER 3.1.2 用户 SYSTEM 登录 Oracle 3.1.3创建表空间RESTAURANT,大小10M。 -Oracle的FOR EACH ROW触发器.不允许在触发器里饥 SELECT / UPDATE / DELETE自己这个 表。 一创建表空间RESTAURANT,大小10NL (用

9、户SYSTEM登录Oracle) CREATE TABLESPACE RESTAURANT DATAFILE H:RESTAUR ANT.ora* SIZE 10M不能使用相对路径 DEFAULT STORAGE (INITIAL 10K NEXT50K MINEXTENTS1 MAXEXTENTS 99 PCTINCREASE 10) ONLINE; 3.L4创建用户DINER, 口令XXX,默认表空间RESTAURANT,给该用户授予 角色权限 CONNECT、RESOURCEo CREATE USER DINER IDENTIFIED BY ml23 DEFAULT TABLESPACE

10、 RESTAURANT; GRANT CONNECT TO DINER GRANT RESOURCE TO DINER 3.2创建餐饮系统数据库的所有表，并向各表插入演示数据 3.2.1 启动 PL/SQL DeveloperC 或者启动 SQL*PLUSEnterprise Manager Console、 浏览器模式的EM （企业管理器）等工具均可以）,用户DINER登录Oracle。 3.2.2创建实验内容中的餐饮系统数据库的所有表（菜肴类别表MK、菜单信息表 MList.餐台类别表DK、餐台信息表Dinfo、消费单主表C、消费单明细表 CList）o 3.2.3依次向菜肴类别表MK、菜

11、单信息表MList、餐台类别表DK、餐台信息表 Dinfo插入足够多的演示数据。 -（1）菜肴类别表MK （菜肴类别編号MKid,菜肴类别名称MkName） 菜肴类别名称：鱼奘 蔬菜类. 凉 “ 菜类.肉类.主食类和酒水类等。 create sequence seq_MK start with 0 increment by 1 minvalue 0 maxvalue 30000 nocycle nocache; create table MK（ MKid number, MkName vaichar2（64）. constraint pk_MKid primary key （MKid） ）;

12、insert into MK values（seq_MK.nexlval/鱼类） insert into MK values（seq_MK.nexlval/蔬菜类） insert into MK values（seq_MK.nextval/凉菜类J insert into MK values（seq_MK.nextval/肉类） insert into MK values（seq_MK.nextval/主食类? insert into MK values（seq_MK.nextvaI/酒水） 一（2）菜爪信息表MList （菜肴编号Mid.菜肴名称Mname.菜肴类别MKid,菜肴収价Mpri

13、ce. 一菜肴成木爪价Mcost,更新日期Mdate）. create sequence seq_MList start with 0 increment by 1 minvalue 0 maxvalue 30000 nocycle nocache; create table MList( Mid number. Mnanie varchar2( 64). MKid number references MK(MKid), Mprice number(8.2), Mcost number(8,2), Mdate date, constraint pk_Mid primary key (Mid)

14、鱼 1,30.00,1500,sysdaie) 鱼 1.20.00000.sysda【e) 茄2.5.50.4.00.sysdate) 菜2.6.00.5.00.sysdate) insert into MList values(seq_MList.nextval.*比目鱼 1.40.()0.20.00,sysdate) insert into MList values(seq_MList.nextval/ftt insert into MList values(seq_MList.nextval.鲤 insert into MList values(seq_ML!St.nextval/番 i

15、nsert into MList values(seq_MList.nextvaL,生 insert into MList values(seq_MList.nextval/大白菜2,3.00.1.00.sysdate) -(3) 餐台类别表DK (餐台类别编号DKid.餐台类别名称DkName),餐台类别：包间和散台等。 create sequence seq_DK start with 0 increment by 1 minvalue 0 maxvalue 30000 nocycle nocache create table DK( DKid number. DkName varchar

16、2(64), constraint pk_DKid primary key (DKid) ) insert into DK values(seqJDK.nex(vaV包间) insert into DK values(seq_DK.nextval/Ui台 J -(4)餐台信息表Dinfo (餐台编号Did.餐台名称Dname,餐台类别DKid,座位数Dseats.更新日期 Ddate)o create sequence seq_Dinfo start with 0 increment by 1 minvalue 0 maxvalue 30000 nocycle nocache : create

17、 table Dinfo( Did number. Dname varchar2(64), DKid number references DK(DKid), Dseats number. Ddate dale, constraint pk_Did primary key (Did) ); insert into Dinfo values(seq_Dinfo.nextval.*l 号包间L30.sysdate) insert into Dinfo values(seq_Dinfo.nextval.2 号包间L30.sysdate) insert into Dinfo values(seq_Din

18、fo.nextval.3 号包间*,1.90.sysdate) insert into Dinfo values(seq_Dinfo.nextval/4 号包间：l,80.sysdale) insert into Dinfo values(seq_Dinfo.nextval/1 号散台2J0.sysdate) insert into Dinfo values(seq_Dinfo.nextval/2 号散台2J0.sysdate) insert into Dinfo values(seq_Dinfo.nextval/3 号散台：2,10,sysdale) insert into Dinfo va

19、lues(seq_Dinfo.nextvaI.*4 号散台；2,10,sysdale) (5) 消费单主表C (消费单号Cid,餐台编号Did.消费开始时间StartTime,结账时间EndTime.消 费金额合汁Smoney, “盈利金额合计SPsum),其中消费金额合计=消费单明细表CList中该消费瞅号的所有消费记录的消 费金额的合计， 即SUM (消费金额)或SUM (菜肴讯价X消费数虽)，盈利金额合计=消费单明细表CLisi中该消 费单号的所有消费 -记录的盈利合计即SUM (菜肴单价菜肴成本单价)X消费数虽)。 create sequence seq_C start with 0

20、increment by 1 nunvalue 0 maxvalue 30000 nocycle nocache; create table C( Cid number. Did number references Dinfo(Did), StartTime dale. EndTime date, Smoney number( create table CList( Sid number. Cid number references C(Cid). Mid number references MList(Mid), Mname varchar2(64), Cqty number. Mprice

21、 number(8.2), Mcost number(8.2), Cmoney number(8.2), constraint pk_Sid primary key (Sid) insert into CList values(seq_CList.nextval. 1J；比目鱼；4,40.00,20.00J60.00) insert into CList values(seq_CList.nextval. 1.2/ 鱼5,30.00.15.00.150.00) insert into CList values(seq_CList.nextval. 1.5；生 菜26.00.5.00J 2.00

22、) 截图: 2.1.1 SID |CID MID | M NAME CQTY | MPRICEMCOSTCMONEY 1 1 1 1 比目鱼 440.0020.00160.00 2 3 1 5 生菜 2 6.00 5.00 12.00 3 12 1 1 比目鱼 6 40.00 20.00 240.00 4 13 1 1 比目鱼- 6 40.00 20.00 240.00 5 14 2 1 比目鱼 4 40.00 20.00 160.00 6 8 1 1 比目鱼 6 40.00 20.00 240.00 7 9 1 1 比目鱼 6 40.00 20.00 240.00 8 10 1 1 比目鱼

23、6 40.00 20.00 240.00 9 11 1 1 比目鱼 6 40.00 20.00 240.00 10 15 2 2 誌鱼 5 30.00 15.00 150.00 11 16 2 5生菜 2 6.00 5.00 12.00 SID T CID MID MNAME CQTY MPRICE MCOST CMONEY 1 1 1 1 比目鱼 4 40.00 20.00 160.00 3 3 1 5 生菜 2 6.00 5.00 1乙00 7 8 1 1 比目鱼 6 40.00 20.00 240.00 2S;42 2016/5/819:25:42 - 2260.00 1130.00 2

24、 2 1 2016/5/819:25:47 2016/5/819:25:47 - 322.00 15.7.00 3 3 2 2016/5/81925:50 - 2016/5/8 19:25:50 丫 260.00 170.00 4 4 4 2016/5/819：2S：52 2016/5/8 19:25:52 - 510.00 330.00 5 5 4 2016/5/819;2S;54 2016/5/8 19:25:54 - 580.00 340.00 2.1.4 SID |CID MIDMNAME | CQTY MPRICE MCOST CMONEY 1 1 1 1 比目鱼 4 40.00 2

25、0.00 160.00 2 3 1 5 生菜 2 6.00 5.00 12.00 3 12 1 1 比目鱼 6 40.00 20.00 240.00 4 13 1 1 比目鱼- 6 40.00 20.00 240.00 5 14 2 1 比目鱼 4 40.00 20.00 160.00 6 8 1 1 比目鱼 6 40.00 20.00 240.00 7 9 1 1 比目鱼 6 40.00 20.00 240.00 8 10 1 1 比目鱼- 6 40.00 20.00 240.00 9 11 1 1 比目鱼 6 40.00 20.00 240.00 10 15 2 2 笼鱼 5 30.00

26、15.00 150.00 11 16 2 5 生菜- 2 6.00 5.00 12.00 SID工 CID MID MNAME CQTY MPRICE MCOST CMONEY 1 1 1 1 比目鱼 4 40.00 20.00 160.00 3 3 1 5 生菜 2 6.00 5.00 1乙00 7 8 1 1 比目鱼 6 40.00 20.00 240.00 g 9 1 1 比目鱼 6 40.00 20.00 240.00 9 10 1 1 比目鱼 比目鱼 6 40.00 20.00 240.00 10 11 1 1 6 40.00 20.00 240.00 4 12 1 1 比目鱼 6 40.00 20.00 240.00 5 B 1 1 比目鱼 6 40.00 20.00