Oracle实验4(实验报告)-PL_SQL程序的设计说明

学期

Oracle数据库应用技术

实验报告

选课序号：

班级：

学号：

姓名：

指导教师：史金余

成绩：

2017年月日

目录

1.实验目的 (1)

2.实验容 (1)

2.1 触发器设计 (2)

2.2 存储过程、自定义函数设计 (2)

2.3 程序包设计 (3)

3.实验步骤 (3)

3.1 创建表空间RESTAURANT,创建用户DINER (3)

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

3.3 完成【实验容】中的触发器、存储过程、函数和程序包等功

能设计，将程序脚本保存到文本文件Source.sql中 (7)

4.实验总结 (13)

PL/SQL程序设计

1.实验目的

◆掌握PL/SQL程序设计基本技巧，包括基本数据类型、表类型、数组类型、

匿名程序块、控制语句、PL/SQL中使用SQL语句、游标、错误处理等。

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

2.实验容

实验平台：PL/SQL Developer或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.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(YEAR FROM 日期字段)，本题：统计年份= EXTRACT(YEAR FROM EndTime)，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】的函

数及存储过程功能，注意：先创建package，创建成功后，再创建包体package body。

2.3.2 设计一个匿名PL/SQL程序块，参照【2.2.3】调用【2.3.1】中程序包的

函数和存储过程，输出2013年餐台类别名为“散台”的成本金额合计，输出2013年菜肴类别名为“蔬菜类”的盈利金额合计。

3.实验步骤

（备注：如果用实验室微机，请从【3.2】开始做，登录用户DINER改为stu XX）3.1 创建表空间RESTAURANT,创建用户DINER

3.1.2 用户SYSTEM登录Oracle

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

CREATE TABLESPACE RESTAURANT

DATAFILE'F:\RESTAURANT.ora'SIZE10M

DEFAULT STORAGE

(INITIAL10K

NEXT50K

MINEXTENTS1

MAXEXTENTS99

PCTINCREASE10)

ONLINE;

3.1.4 创建用户DINER，口令XXX，默认表空间RESTAURANT，给该用户授予角色

权限CONNECT、RESOURCE。

CREATE USER DINER IDENTIFIED BY "wzl123" DEFAULT TABLESPACE RESTAURANT;