收藏
下载资源下载资源 加入VIP,免费下载

Oracle 实验4实验报告PLSQL程序设计.docx

上传人：b****8 文档编号：9210214 上传时间：2023-02-03 格式：DOCX 页数：18 大小：140.34KB
下载 相关 举报
Oracle 实验4实验报告PLSQL程序设计.docx_第1页
第1页 / 共18页
Oracle 实验4实验报告PLSQL程序设计.docx_第2页
第2页 / 共18页
Oracle 实验4实验报告PLSQL程序设计.docx_第3页
第3页 / 共18页
Oracle 实验4实验报告PLSQL程序设计.docx_第4页
第4页 / 共18页
Oracle 实验4实验报告PLSQL程序设计.docx_第5页
第5页 / 共18页
点击查看更多>>
下载资源
资源描述

Oracle 实验4实验报告PLSQL程序设计.docx

《Oracle 实验4实验报告PLSQL程序设计.docx》由会员分享，可在线阅读，更多相关《Oracle 实验4实验报告PLSQL程序设计.docx（18页珍藏版）》请在冰豆网上搜索。

Oracle 实验4实验报告PLSQL程序设计.docx

Oracle实验4实验报告PLSQL程序设计

 

学期

Oracle数据库应用技术

实验报告

 

选课序号：

班级：

学号：

姓名：

指导教师：

史金余

 

成绩：

 

2017年月日

 

PL/SQL程序设计

实验目的

掌握PL/SQL程序设计基本技巧，包括基本数据类型、表类型、数组类型、匿名程序块、控制语句、PL/SQL中使用SQL语句、游标、错误处理等。

熟悉和掌握PL/SQL中关于存储过程、函数、包和触发器程序设计技术。

实验内容

实验平台：

PL/SQLDeveloper或Oracle的其它客户端管理工具。

某餐饮系统数据库（加粗字段为主键，斜体字段为外键），请创建如下各数据表，并实现如下存储过程、函数、包和触发器等功能设计，将程序脚本保存到文本文件中：

菜肴类别表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.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将【】、【】、【】三个触发器禁用disable，重新编写一个触发器实现这三个触发器的全部功能。

编写相应的插入语句（INSERT）、更新语句（UPDATE）、删除语句（DELETE）和查询语句（SELECT）测试该触发器效果。

存储过程、自定义函数设计

2.2.1设计一个自定义函数fGetDTSum，实现统计某年份给定餐台类别的成本金额合计的功能，输入参数是统计年份和餐台类别，返回数据是成本金额合计。

成本金额=消费数量×菜肴成本单价。

求年份的函数为EXTRACT（YEARFROM日期字段），本题：

统计年份=EXTRACT（YEARFROMEndTime），EndTime为结账时间字段。

2.2.2设计一个存储过程pGetKindSum，实现统计某年份给定菜肴类别的盈利金额合计的功能，输入参数是统计年份和菜肴类别，输出参数是盈利金额合计。

盈利金额=消费数量×（菜肴单价-菜肴成本单价）。

2.2.3编写一段匿名PL/SQL程序块，调用函数fGetDTSum，输出2013年餐台类别名为“包间”的成本金额合计；调用存储过程pGetKindSum，输出2013年菜肴类别名为“鱼类”的盈利金额合计。

程序包设计

2.3.1设计一个程序包，包名为pkSUM，包括并实现【】和【】的函数及存储过程功能，注意：

先创建包头package，包头创建成功后，再创建包体packagebody。

2.3.2设计一个匿名PL/SQL程序块，参照【】调用【】中程序包的函数和存储过程，输出2013年餐台类别名为“散台”的成本金额合计，输出2013年菜肴类别名为“蔬菜类”的盈利金额合计。

实验步骤

（备注：

如果用实验室微机，请从【】开始做，登录用户DINER改为stuXX）

创建表空间RESTAURANT,创建用户DINER

用户SYSTEM登录Oracle

3.1.3创建表空间RESTAURANT，大小10M。

CREATETABLESPACERESTAURANT

DATAFILE'F:

\'SIZE10M

DEFAULTSTORAGE

（INITIAL10K

NEXT50K

MINEXTENTS1

MAXEXTENTS99

PCTINCREASE10）

ONLINE;

创建用户DINER，口令XXX，默认表空间RESTAURANT，给该用户授予角色权限CONNECT、RESOURCE。

CREATEUSERDINERIDENTIFIEDBY"wzl123"DEFAULTTABLESPACERESTAURANT;

GRANTCONNECTTODINER;

GRANTRESOURCETODINER;

创建餐饮系统数据库的所有表，并向各表插入演示数据

启动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,,,sysdate）;

insertintoMListvalues（2,'三文鱼',1,,,sysdate）;

insertintoMListvalues（3,'白菜',2,,,sysdate）;

insertintoMListvalues（4,'土豆',2,,,sysdate）;

insertintoMListvalues（5,'油麦菜',2,,,sysdate）;

insertintoMListvalues（6,'凉拌黄瓜',3,,,sysdate）;

insertintoMListvalues（7,'鸡肉',4,,,sysdate）;

insertintoMListvalues（8,'米饭',5,,,sysdate）;

insertintoMListvalues（9,'二锅头',6,,,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,,,;

insertintoCListvalues（2,1,5,'油麦菜',1,,,;

insertintoCListvalues（3,1,9,'二锅头',2,,,;

完成【实验内容】中的触发器、存储过程、函数和程序包等功能设计，将程序脚本保存到文本文件中

3.3.1在PL/SQLDeveloper环境下，用户DINER登录Oracle

3.3.2新建SQL窗口

3.3.3完成【触发器设计】

（1）ORREPLACETRIGGERtri_CList

BEFOREINSERTONCListFOREACHROW

DECLARE

t_Smoney%type;

t_SPsum%type;

BEGIN

--补全Mlist

SELECTMname,Mprice,Mcost,:

*MpriceINTO:

:

:

:

FROMMlistWHERE=:

;

--计算Mlist的增加量

SELECTNVL（SUM（Cqty*Mprice）,0）,NVL（SUM（Cqty*（Mprice-Mcost））,0）

INTOv_Smoney,v_SPsumFROMClistWHERE=:

;

--更新C

updateCsetSmoney=t_Smoney+:

SPsum=t_SPsum+（:

*（:

:

）

where=:

;

ENDtri_CList;

（2）ORREPLACETRIGGERtri_upCListBEFOREUPDATEONCListFOREACHROW

BEGIN

SELECTMname,Mprice,McostINTO:

:

:

FROMMlistWHERE=:

;

:

:

=NVL（:

:

*NVL（:

:

;

UPDATECSETSmoney=Smoney-:

+:

SPsum=SPsum-:

*（:

:

+:

*（:

:

WHERE=:

;

ENDtri_upCList;

（3）ORREPLACETRIGGERtri_delCList

BEFOREDELETEONCListFOREACHROW

BEGIN

UPDATECSET=:

=（:

*（:

:

）

WHERE=:

;

ENDtri_delCList;

（4）ORREPLACETRIGGERtri_all

BEFOREINSERTORUPDATEORDELETE

ONCList

FOREACHROW

DECLARE

t_Smoney%type;

t_SPsum%type;

BEGIN

IFINSERTINGTHEN

SelectMname,Mprice,Mcost,:

*Mprice

into:

:

:

:

fromMlist

where=:

;

Selectnvl（sum（Cqty*Mprice）,0）,nvl（sum（Cqty*（Mprice-Mcost））,0）

intot_Smoney,t_SPsum

fromClist

where=:

;

updateC

setSmoney=t_Smoney+:

SPsum=t_SPsum+（:

*（:

:

）

where=:

;

ELSIFUPDATINGTHEN

selectMname,Mprice,Mcost

into:

:

:

fromMlist

where=:

;

:

:

=nvl（:

:

*nvl（:

:

;

UpdateCsetSmoney=Smoney-:

+:

SPsum=SPsum-:

*（:

:

+:

*（:

:

where=:

;

ELSE

UPDATECSET=:

=（:

*（:

:

）;

ENDIF;

ENDtri_all;

3.3.4完成【存储过程、自定义函数设计】

（1）ORREPLACEFUNCTIONfGetDTSum

（Cyearchar,Ctable%type）

RETURN%TYPE

AS

cons%TYPE;

BEGIN

selectnvl（sum（spsum）,0）intoconsfromC

wheredidin（

selectdidfromdinfowheredkidin（

selectdkidfromdkwheredkname=Ctable））

and（to_char（endtime,'YYYY'）=Cyear）;

RETURNcons;

EXCEPTION

WHENNO_DATA_FOUNDTHEN

（'Thedataisinvalid!

'）;

ENDfGetDTSum;

（2）ORREPLACEPROCEDUREpGetKindSum（

Cyearchar,Cnamechar,

consout%TYPE）

AS

BEGIN

selectnvl（sum（mprice-mcost）,0）intoconsfromclist

wheremidin

（selectmidfrommlistwheremkidin

（selectmkidfrommkwheremkname=Cname））

and

cidin

（selectcidfromCwhereto_char（endtime,'YYYY'）=Cyear）;

EXCEPTION

WHENNO_DATA_FOUNDTHEN

（'Thedatadoesn’’texists!

'）;

ENDpGetKindSum;

（3）cons1%TYPE;

cons2%TYPE;

BEGIN

cons1:

=fGetDTSum（'2013','包间'）;

pGetKindSum（'2013','鱼类',cons2）;

（'cons1'||cons1||'cons2'||cons2||''）;

END;

3.3.5完成【程序包设计】

（1）ORREPLACEPACKAGEpkSUM

AS

FUNCTIONfGetDTSum

（Cyearchar,Ctable%type）

RETURN%TYPE;

PROCEDUREpGetKindSum（

Cyearchar,Cnamechar,

consout%TYPE）;

ENDpkSUM;

CREATEORREPLACEPACKAGEBODYpkSUM

AS

CREATEORREPLACEFUNCTIONfGetDTSum

（Cyearchar,Ctable%type）

RETURN%TYPE

AS

cons%TYPE;

BEGIN

selectnvl（sum（spsum）,0）intoconsfromC

wheredidin（

selectdidfromdinfowheredkidin（

selectdkidfromdkwheredkname=Ctable））

and（to_char（endtime,'YYYY'）=Cyear）;

RETURNcons;

EXCEPTION

WHENNO_DATA_FOUNDTHEN

（'Thedataisinvalid!

'）;

ENDfGetDTSum;

CREATEORREPLACEPROCEDUREpGetKindSum（

Cyearchar,Cnamechar,

consout%TYPE）

AS

BEGIN

selectnvl（sum（mprice-mcost）,0）intoconsfromclist

wheremidin

（selectmidfrommlistwheremkidin

（selectmkidfrommkwheremkname=Cname））

and

cidin

（selectcidfromCwhereto_char（endtime,'YYYY'）=Cyear）;

EXCEPTION

WHENNO_DATA_FOUNDTHEN

（'Thedatadoesn’’texists!

'）;

ENDpGetKindSum;

ENDpkSUM;

CREATEORREPLACEPACKAGEBODYpkSUM

AS

CREATEORREPLACEFUNCTIONfGetDTSum

（Cyearchar,Ctable%type）

RETURN%TYPE

AS

cons%TYPE;

BEGIN

selectnvl（sum（spsum）,0）intoconsfromC

wheredidin（

selectdidfromdinfowheredkidin（

selectdkidfromdkwheredkname=Ctable））

and（to_char（endtime,'YYYY'）=Cyear）;

RETURNcons;

EXCEPTION

WHENNO_DATA_FOUNDTHEN

（'Thedataisinvalid!

'）;

ENDfGetDTSum;

CREATEORREPLACEPROCEDUREpGetKindSum（

Cyearchar,Cnamechar,

consout%TYPE）

AS

BEGIN

selectnvl（sum（mprice-mcost）,0）intoconsfromclist

wheremidin

（selectmidfrommlistwheremkidin

（selectmkidfrommkwheremkname=Cname））

and

cidin

（selectcidfromCwhereto_char（endtime,'YYYY'）=Cyear）;

EXCEPTION

WHENNO_DATA_FOUNDTHEN

（'Thedatadoesn’’texists!

'）;

ENDpGetKindSum;

ENDpkSUM;

（2）cons1%TYPE;

cons2%TYPE;

BEGIN

cons1:

=（'2013','散台'）;

（'2013','蔬菜类',cons2）;

（'cons1:

'||cons1||'cons2:

'|

展开阅读全文
相关资源
猜你喜欢
相关搜索

当前位置：首页 > 工程科技 > 电力水利

copyright@ 2008-2022 冰豆网网站版权所有

经营许可证编号:鄂ICP备2022015515号-1